1 package Excel::Template;
6 use Excel::Template::Base;
7 use vars qw ($VERSION @ISA);
10 @ISA = qw( Excel::Template::Base );
20 my $self = $class->SUPER::new(@_);
22 $self->parse_xml($self->{FILENAME})
23 if defined $self->{FILENAME};
25 my @renderer_classes = ( 'Spreadsheet::WriteExcel' );
26 if (exists $self->{BIG_FILE} && $self->{BIG_FILE})
28 unshift @renderer_classes, 'Spreadsheet::WriteExcel::Big';
31 $self->{RENDERER} = undef;
32 foreach my $class (@renderer_classes)
34 (my $filename = $class) =~ s!::!/!g;
36 require "$filename.pm";
40 warn "Could not find or compile '$class'\n" if $^W;
42 $self->{RENDERER} = $class;
47 defined $self->{RENDERER} ||
48 die "Could not find a renderer class. Tried:\n\t" .
49 join("\n\t", @renderer_classes) .
52 $self->{USE_UNICODE} = ~~0
62 # Allow an arbitrary number of hashrefs, so long as they're the first things # into param(). Put each one onto the end, de-referenced.
63 push @_, %{shift @_} while UNIVERSAL::isa($_[0], 'HASH');
66 && die __PACKAGE__, "->param() : Odd number of parameters to param()\n";
69 $params{uc $_} = delete $params{$_} for keys %params;
70 @{$self->{PARAM_MAP}}{keys %params} = @params{keys %params};
80 my $xls = $self->{RENDERER}->new($filename)
81 || die "Cannot create XLS in '$filename': $!\n";
83 $self->_prepare_output($xls);
95 tie *XLS, 'IO::Scalar', \$output;
97 $self->write_file(\*XLS);
107 my ($filename, $dirname) = fileparse($fname);
110 my $parser = XML::Parser->new(
118 my $node = Excel::Template::Factory->create_node($name, @_);
119 die "'$name' (@_) didn't make a node!\n" unless defined $node;
121 if ( $node->isa( 'WORKBOOK' ) )
123 push @{$self->{WORKBOOKS}}, $node;
125 elsif ( $node->is_embedded )
127 return unless @stack;
129 if (exists $stack[-1]{TXTOBJ} &&
130 $stack[-1]{TXTOBJ}->isa('TEXTOBJECT'))
132 push @{$stack[-1]{TXTOBJ}{STACK}}, $node;
138 push @{$stack[-1]{ELEMENTS}}, $node
145 return unless @stack;
147 my $parent = $stack[-1];
150 exists $parent->{TXTOBJ}
152 $parent->{TXTOBJ}->isa('TEXTOBJECT')
154 push @{$parent->{TXTOBJ}{STACK}}, @_;
159 return unless @stack;
161 pop @stack if $stack[-1]->isa(uc $_[0]);
167 open( INFILE, "<$fname" )
168 || die "Cannot open '$fname' for reading: $!\n";
170 $parser->parse(do { local $/ = undef; <INFILE> });
177 *parse = \&parse_xml;
184 my $context = Excel::Template::Factory->create(
188 PARAM_MAP => [ $self->{PARAM_MAP} ],
189 UNICODE => $self->{UNICODE},
192 $_->render($context) for @{$self->{WORKBOOKS}};
197 sub register { shift; Excel::Template::Factory::register(@_) }
204 Excel::Template - Excel::Template
208 First, make a template. This is an XML file, describing the layout of the
211 For example, test.xml:
214 <worksheet name="tester">
220 Now, create a small program to use it:
225 # Create the Excel template
226 my $template = Excel::Template->new(
227 filename => 'test.xml',
230 # Add a few parameters
236 $template->write_file('test.xls');
238 If everything worked, then you should have a spreadsheet in your work directory
239 that looks something like:
242 +----------------+----------------+----------------
243 1 | /home/me | /bin:/usr/bin |
244 +----------------+----------------+----------------
246 +----------------+----------------+----------------
251 This is a module used for templating Excel files. Its genesis came from the
252 need to use the same datastructure as HTML::Template, but provide Excel files
253 instead. The existing modules don't do the trick, as they require replication
254 of logic that's already been done within HTML::Template.
258 I do a lot of Perl/CGI for reporting purposes. In nearly every place I've been,
259 I've been asked for HTML, PDF, and Excel. HTML::Template provides the first, and
260 PDF::Template does the second pretty well. But, generating Excel was the
261 sticking point. I already had the data structure for the other templating
262 modules, but I just didn't have an easy mechanism to get that data structure
269 This creates a Excel::Template object. If passed a FILENAME parameter, it will
270 parse the template in the given file. (You can also use the parse() method,
273 new() accepts an optional BIG_FILE parameter. This will attempt to change the
274 renderer from L<Spreadsheet::WriteExcel> to L<Spreadsheet::WriteExcel::Big>. You
275 must already have L<Spreadsheet::WriteExcel::Big> installed on your system.
277 new() also accepts an optional USE_UNICODE parameter. This will use
278 L<Unicode::String> to represent strings instead of Perl's internal string
279 handling. You must already have L<Unicode::String> installed on your system.
281 The USE_UNICODE parameter will be ignored if you are using Perl 5.8 or higher as
282 Perl's internal string handling is unicode-aware.
284 NOTE: L<Spreadsheet::WriteExcel::Big> and mod_perl clash for some reason. This
285 is outside of my control.
289 This method is exactly like L<HTML::Template>'s param() method.
291 =head2 parse() / parse_xml()
293 This method actually parses the template file. It can either be called
294 separately or through the new() call. It will die() if it runs into a situation
299 Create the Excel file and write it to the specified filename, if possible. (This
300 is when the actual merging of the template and the parameters occurs.)
304 It will act just like HTML::Template's output() method, returning the resultant
305 file as a stream, usually for output to the web. (This is when the actual
306 merging of the template and the parameters occurs.)
308 =head1 SUPPORTED NODES
310 This is a partial list of nodes. See the other classes in this distro for more
311 details on specific parameters and the like.
313 Every node can set the ROW and COL parameters. These are the actual ROW/COL
314 values that the next CELL-type tag will write into.
318 =item * L<WORKBOOK|Excel::Template::Container::Workbook>
320 This is the node representing the workbook. It is the parent for all other
323 =item * L<WORKSHEET|Excel::Template::Container::Worksheet>
325 This is the node representing a given worksheet.
327 =item * L<IF|Excel::Template::Container::Conditional>
329 This node represents a conditional expression. Its children may or may not be
330 rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
332 =item * L<LOOP|Excel::Template::Container::Loop>
334 This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
336 =item * L<ROW|Excel::Template::Container::Row>
338 This node represents a row of data. This is the A in A1.
340 =item * L<FORMAT|Excel::Template::Container::Format>
342 This node varies the format for its children. All formatting options supported
343 in L<Spreadsheet::WriteExcel> are supported here. There are also a number of
344 formatting shortcuts, such as L<BOLD|Excel::Template::Container::Bold> and
345 L<ITALIC|Excel::Template::Container::Italic>.
347 =item * L<BACKREF|Excel::Template::Element::Backref>
349 This refers back to a cell previously named.
351 =item * L<CELL|Excel::Template::Element::Cell>
353 This is the actual cell in a spreadsheet.
355 =item * L<FORMULA|Excel::Template::Element::Formula>
357 This is a formula in a spreadsheet.
359 =item * L<RANGE|Excel::Template::Element::Range>
361 This is a BACKREF for a number of identically-named cells.
363 =item * L<VAR|Excel::Template::Element::Var>
365 This is a variable. It is generally used when the 'text' attribute isn't
372 None, that I know of.
376 This is production quality software, used in several production web
381 Rob Kinyon (rob.kinyon@gmail.com)
385 There is a mailing list at http://groups.google.com/group/ExcelTemplate
391 =item * Finishing formats
393 =item * Fixing several bugs in worksheet naming
399 This program is free software; you can redistribute
400 it and/or modify it under the same terms as Perl itself.
402 The full text of the license can be found in the
403 LICENSE file included with this module.
407 perl(1), HTML::Template, Spreadsheet::WriteExcel.