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 if (exists $self->{XML} && $self->{XML})
33 unshift @renderer_classes, 'Spreadsheet::WriteExcelXML';
36 $self->{RENDERER} = undef;
37 foreach my $class (@renderer_classes)
39 (my $filename = $class) =~ s!::!/!g;
41 require "$filename.pm";
45 warn "Could not find or compile '$class'\n" if $^W;
47 $self->{RENDERER} = $class;
52 defined $self->{RENDERER} ||
53 die "Could not find a renderer class. Tried:\n\t" .
54 join("\n\t", @renderer_classes) .
57 $self->{USE_UNICODE} = ~~0
67 # Allow an arbitrary number of hashrefs, so long as they're the first things # into param(). Put each one onto the end, de-referenced.
68 push @_, %{shift @_} while UNIVERSAL::isa($_[0], 'HASH');
71 && die __PACKAGE__, "->param() : Odd number of parameters to param()\n";
74 $params{uc $_} = delete $params{$_} for keys %params;
75 @{$self->{PARAM_MAP}}{keys %params} = @params{keys %params};
85 my $xls = $self->{RENDERER}->new($filename)
86 || die "Cannot create XLS in '$filename': $!\n";
88 $self->_prepare_output($xls);
100 tie *XLS, 'IO::Scalar', \$output;
102 $self->write_file(\*XLS);
112 my ($filename, $dirname) = fileparse($fname);
115 my $parser = XML::Parser->new(
123 my $node = Excel::Template::Factory->create_node($name, @_);
124 die "'$name' (@_) didn't make a node!\n" unless defined $node;
126 if ( $node->isa( 'WORKBOOK' ) )
128 push @{$self->{WORKBOOKS}}, $node;
130 elsif ( $node->is_embedded )
132 return unless @stack;
134 if (exists $stack[-1]{TXTOBJ} &&
135 $stack[-1]{TXTOBJ}->isa('TEXTOBJECT'))
137 push @{$stack[-1]{TXTOBJ}{STACK}}, $node;
143 push @{$stack[-1]{ELEMENTS}}, $node
150 return unless @stack;
152 my $parent = $stack[-1];
155 exists $parent->{TXTOBJ}
157 $parent->{TXTOBJ}->isa('TEXTOBJECT')
159 push @{$parent->{TXTOBJ}{STACK}}, @_;
164 return unless @stack;
166 pop @stack if $stack[-1]->isa(uc $_[0]);
172 open( INFILE, "<$fname" )
173 || die "Cannot open '$fname' for reading: $!\n";
175 $parser->parse(do { local $/ = undef; <INFILE> });
182 *parse = \&parse_xml;
189 my $context = Excel::Template::Factory->create(
193 PARAM_MAP => [ $self->{PARAM_MAP} ],
194 UNICODE => $self->{UNICODE},
197 $_->render($context) for @{$self->{WORKBOOKS}};
202 sub register { shift; Excel::Template::Factory::register(@_) }
209 Excel::Template - Excel::Template
213 First, make a template. This is an XML file, describing the layout of the
216 For example, test.xml:
219 <worksheet name="tester">
225 Now, create a small program to use it:
230 # Create the Excel template
231 my $template = Excel::Template->new(
232 filename => 'test.xml',
235 # Add a few parameters
241 $template->write_file('test.xls');
243 If everything worked, then you should have a spreadsheet in your work directory
244 that looks something like:
247 +----------------+----------------+----------------
248 1 | /home/me | /bin:/usr/bin |
249 +----------------+----------------+----------------
251 +----------------+----------------+----------------
256 This is a module used for templating Excel files. Its genesis came from the
257 need to use the same datastructure as HTML::Template, but provide Excel files
258 instead. The existing modules don't do the trick, as they require replication
259 of logic that's already been done within HTML::Template.
263 I do a lot of Perl/CGI for reporting purposes. In nearly every place I've been,
264 I've been asked for HTML, PDF, and Excel. HTML::Template provides the first, and
265 PDF::Template does the second pretty well. But, generating Excel was the
266 sticking point. I already had the data structure for the other templating
267 modules, but I just didn't have an easy mechanism to get that data structure
274 This creates a Excel::Template object. If passed a FILENAME parameter, it will
275 parse the template in the given file. (You can also use the parse() method,
278 new() accepts an optional BIG_FILE parameter. This will attempt to change the
279 renderer from L<Spreadsheet::WriteExcel> to L<Spreadsheet::WriteExcel::Big>. You
280 must already have L<OLE::Storage_Lite> (required by Spreadsheet::WriteExcel::Big) installed on your system.
282 new() also accepts an optional USE_UNICODE parameter. This will use
283 L<Unicode::String> to represent strings instead of Perl's internal string
284 handling. You must already have L<Unicode::String> installed on your system.
286 The USE_UNICODE parameter will be ignored if you are using Perl 5.8 or higher as
287 Perl's internal string handling is unicode-aware.
289 NOTE: Certain older versions of L<OLE::Storage_Lite> and mod_perl clash for some
290 reason. Upgrading to the latest version of L<OLE::Storage_Lite> should fix the
295 This method is exactly like L<HTML::Template>'s param() method.
297 =head2 parse() / parse_xml()
299 This method actually parses the template file. It can either be called
300 separately or through the new() call. It will die() if it runs into a situation
305 Create the Excel file and write it to the specified filename, if possible. (This
306 is when the actual merging of the template and the parameters occurs.)
310 It will act just like HTML::Template's output() method, returning the resultant
311 file as a stream, usually for output to the web. (This is when the actual
312 merging of the template and the parameters occurs.)
314 =head1 SUPPORTED NODES
316 This is a partial list of nodes. See the other classes in this distro for more
317 details on specific parameters and the like.
319 Every node can set the ROW and COL parameters. These are the actual ROW/COL
320 values that the next CELL-type tag will write into.
324 =item * L<WORKBOOK|Excel::Template::Container::Workbook>
326 This is the node representing the workbook. It is the parent for all other
329 =item * L<WORKSHEET|Excel::Template::Container::Worksheet>
331 This is the node representing a given worksheet.
333 =item * L<IF|Excel::Template::Container::Conditional>
335 This node represents a conditional expression. Its children may or may not be
336 rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
338 =item * L<LOOP|Excel::Template::Container::Loop>
340 This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
342 =item * L<ROW|Excel::Template::Container::Row>
344 This node represents a row of data. This is the A in A1.
346 =item * L<FORMAT|Excel::Template::Container::Format>
348 This node varies the format for its children. All formatting options supported
349 in L<Spreadsheet::WriteExcel> are supported here. There are also a number of
350 formatting shortcuts, such as L<BOLD|Excel::Template::Container::Bold> and
351 L<ITALIC|Excel::Template::Container::Italic>.
353 =item * L<BACKREF|Excel::Template::Element::Backref>
355 This refers back to a cell previously named.
357 =item * L<CELL|Excel::Template::Element::Cell>
359 This is the actual cell in a spreadsheet.
361 =item * L<FORMULA|Excel::Template::Element::Formula>
363 This is a formula in a spreadsheet.
365 =item * L<RANGE|Excel::Template::Element::Range>
367 This is a BACKREF for a number of identically-named cells.
369 =item * L<VAR|Excel::Template::Element::Var>
371 This is a variable. It is generally used when the 'text' attribute isn't
378 None, that I know of.
382 This is production quality software, used in several production web
387 Rob Kinyon (rob.kinyon@gmail.com)
391 There is a mailing list at http://groups.google.com/group/ExcelTemplate
397 =item * Finishing formats
399 =item * Fixing several bugs in worksheet naming
405 This program is free software; you can redistribute
406 it and/or modify it under the same terms as Perl itself.
408 The full text of the license can be found in the
409 LICENSE file included with this module.
413 perl(1), HTML::Template, Spreadsheet::WriteExcel.