1 package Excel::Template;
6 use Excel::Template::Base;
7 use vars qw ($VERSION @ISA);
10 @ISA = qw( Excel::Template::Base );
17 use constant RENDER_NML => 'normal';
18 use constant RENDER_BIG => 'big';
19 use constant RENDER_XML => 'xml';
22 RENDER_NML, 'Spreadsheet::WriteExcel',
23 RENDER_BIG, 'Spreadsheet::WriteExcel::Big',
24 RENDER_XML, 'Spreadsheet::WriteExcelXML',
30 my $self = $class->SUPER::new(@_);
32 $self->{FILE} = $self->{FILENAME}
33 if !defined $self->{FILE} && defined $self->{FILENAME};
35 $self->parse_xml($self->{FILE})
36 if defined $self->{FILE};
38 my @renderer_classes = ( 'Spreadsheet::WriteExcel' );
40 if (exists $self->{RENDERER} && $self->{RENDERER})
42 if (exists $renderers{ lc $self->{RENDERER} })
44 unshift @renderer_classes, $renderers{ lc $self->{RENDERER} };
48 warn "'$self->{RENDERER}' is not recognized\n";
51 elsif (exists $self->{BIG_FILE} && $self->{BIG_FILE})
53 warn "Use of BIG_FILE is deprecated.\n";
54 unshift @renderer_classes, 'Spreadsheet::WriteExcel::Big';
57 $self->{RENDERER} = undef;
58 foreach my $class (@renderer_classes)
60 (my $filename = $class) =~ s!::!/!g;
62 require "$filename.pm";
66 warn "Could not find or compile '$class'\n" if $^W;
68 $self->{RENDERER} = $class;
73 defined $self->{RENDERER} ||
74 die "Could not find a renderer class. Tried:\n\t" .
75 join("\n\t", @renderer_classes) .
78 $self->{USE_UNICODE} = ~~0
88 # Allow an arbitrary number of hashrefs, so long as they're the first things # into param(). Put each one onto the end, de-referenced.
89 push @_, %{shift @_} while ref $_[0] eq 'HASH';
92 && die __PACKAGE__, "->param() : Odd number of parameters to param()\n";
95 $params{uc $_} = delete $params{$_} for keys %params;
96 @{$self->{PARAM_MAP}}{keys %params} = @params{keys %params};
106 my $xls = $self->{RENDERER}->new($filename)
107 || die "Cannot create XLS in '$filename': $!\n";
110 $self->_prepare_output($xls);
125 tie *XLS, 'IO::Scalar', \$output;
127 $self->write_file(\*XLS)
146 my $node = Excel::Template::Factory->_create_node($name, @_);
147 die "'$name' (@_) didn't make a node!\n" unless defined $node;
149 if ( $node->isa( 'WORKBOOK' ) )
151 $self->{WORKBOOK} = $node;
153 elsif ( $node->is_embedded )
155 return unless @stack;
157 if (exists $stack[-1]{TXTOBJ} &&
158 $stack[-1]{TXTOBJ}->isa('TEXTOBJECT'))
160 push @{$stack[-1]{TXTOBJ}{STACK}}, $node;
166 push @{$stack[-1]{ELEMENTS}}, $node
173 return unless @stack;
175 my $parent = $stack[-1];
178 exists $parent->{TXTOBJ}
180 $parent->{TXTOBJ}->isa('TEXTOBJECT')
182 push @{$parent->{TXTOBJ}{STACK}}, @_;
187 return unless @stack;
189 pop @stack if $stack[-1]->isa(uc $_[0]);
200 my ($filename, $dirname) = fileparse($file);
202 push @parms, Base => $dirname;
204 open( INFILE, "<$file" )
205 || die "Cannot open '$file' for reading: $!\n";
209 my $parser = XML::Parser->new( @parms );
210 $parser->parse(do { local $/ = undef; <INFILE> });
217 *parse = *parse = \&parse_xml;
222 return unless $self->{WORKBOOK};
226 my $context = Excel::Template::Factory->_create(
230 PARAM_MAP => [ $self->{PARAM_MAP} ],
231 UNICODE => $self->{UNICODE},
234 $self->{WORKBOOK}->render($context);
239 sub register { shift; Excel::Template::Factory->register(@_) }
246 Excel::Template - Excel::Template
250 First, make a template. This is an XML file, describing the layout of the
253 For example, test.xml:
256 <worksheet name="tester">
257 <cell text="$HOME" />
258 <cell text="$PATH" />
262 Now, create a small program to use it:
270 # Create the Excel template
271 my $template = Excel::Template->new(
272 filename => 'test.xml',
275 # Add a few parameters
281 $template->write_file('test.xls');
283 If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like:
286 +----------------+----------------+----------------
287 1 | /home/me | /bin:/usr/bin |
288 +----------------+----------------+----------------
290 +----------------+----------------+----------------
295 This is a module used for templating Excel files. Its genesis came from the need to use the same datastructure as L<HTML::Template>, but provide Excel files instead. The existing modules don't do the trick, as they require replication of logic that's already been done within L<HTML::Template>.
299 I do a lot of Perl/CGI for reporting purposes. In nearly every place I've been, I've been asked for HTML, PDF, and Excel. L<HTML::Template> provides the first, and L<PDF::Template> does the second pretty well. But, generating Excel was the sticking point. I already had the data structure for the other templating modules, but I just didn't have an easy mechanism to get that data structure into an XLS file.
305 This creates a Excel::Template object.
311 =item * FILE / FILENAME
313 Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.)
315 If you want to use the __DATA__ section, you can do so by passing
321 The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are:
325 =item * Excel::Template->RENDER_NML
327 This is the default of L<Spreadsheet::WriteExcel>.
329 =item * Excel::Template->RENDER_BIG
331 This attempts to load L<Spreadsheet::WriteExcel::Big>.
333 =item * Excel::Template->RENDER_XML
335 This attempts to load L<Spreadsheet::WriteExcelXML>.
341 This will use L<Unicode::String> to represent strings instead of Perl's internal string handling. You must already have L<Unicode::String> installed on your system.
343 The USE_UNICODE parameter will be ignored if you are using Perl 5.8 or higher as Perl's internal string handling is unicode-aware.
345 NOTE: Certain older versions of L<OLE::Storage_Lite> and mod_perl clash for some reason. Upgrading to the latest version of L<OLE::Storage_Lite> should fix the problem.
355 Instead, use RENDERER => Excel::Template->RENDER_BIG
361 This method is exactly like L<HTML::Template>'s param() method.
363 =head2 parse() / parse_xml()
365 This method actually parses the template file. It can either be called separately or through the new() call. It will die() if it runs into a situation it cannot handle.
367 If a filename is passed in (vs. a filehandle), the directory name will be passed in to L<XML::Parser> as the I<Base> parameter. This will allow for XML directives to work as expected.
371 Create the Excel file and write it to the specified filename, if possible. (This is when the actual merging of the template and the parameters occurs.)
375 It will act just like L<HTML::Template>'s output() method, returning the resultant file as a stream, usually for output to the web. (This is when the actual merging of the template and the parameters occurs.)
379 This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info.
381 =head1 SUPPORTED NODES
383 This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like.
385 Every node can set the ROW and COL parameters. These are the actual ROW/COL values that the next CELL-type tag will write into.
389 =item * L<WORKBOOK|Excel::Template::Container::Workbook>
391 This is the node representing the workbook. It is the parent for all other nodes.
393 =item * L<WORKSHEET|Excel::Template::Container::Worksheet>
395 This is the node representing a given worksheet.
397 =item * L<IF|Excel::Template::Container::Conditional>
399 This node represents a conditional expression. Its children may or may not be rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
401 =item * L<LOOP|Excel::Template::Container::Loop>
403 This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
405 =item * L<ROW|Excel::Template::Container::Row>
407 This node represents a row of data. This is the A in A1.
409 =item * L<FORMAT|Excel::Template::Container::Format>
411 This node varies the format for its children. All formatting options supported in L<Spreadsheet::WriteExcel> are supported here. There are also a number of formatting shortcuts, such as L<BOLD|Excel::Template::Container::Bold> and L<ITALIC|Excel::Template::Container::Italic>.
413 =item * L<BACKREF|Excel::Template::Element::Backref>
415 This refers back to a cell previously named.
417 =item * L<CELL|Excel::Template::Element::Cell>
419 This is the actual cell in a spreadsheet.
421 =item * L<FORMULA|Excel::Template::Element::Formula>
423 This is a formula in a spreadsheet.
425 =item * L<RANGE|Excel::Template::Element::Range>
427 This is a BACKREF for a number of identically-named cells.
429 =item * L<VAR|Excel::Template::Element::Var>
431 This is a variable. It is generally used when the 'text' attribute isn't
438 None, that I know of.
442 This is production quality software, used in several production web applications.
446 Rob Kinyon (rob.kinyon@gmail.com)
450 There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com
456 =item * Finishing formats
458 =item * Fixing several bugs in worksheet naming
464 I used L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers.
466 Excel::Template is also part of the CPAN Kwalitee initiative, being one of the top 100 non-core modules downloaded from CPAN. If you wish to help out, please feel free to contribute tests, patches, and/or suggestions.
468 -----------------------------------------------------------------------
469 File stmt brnch cond sub pod time total
470 -----------------------------------------------------------------------
471 blib/lib/Excel/Template.pm 96.0 62.5 58.8 100.0 100.0 25.2 86.2
472 ...ib/Excel/Template/Base.pm 87.0 50.0 66.7 81.8 87.5 8.7 83.0
473 ...cel/Template/Container.pm 71.4 50.0 33.3 70.0 80.0 4.9 68.4
474 ...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.7 95.0
475 .../Container/Conditional.pm 64.9 57.5 66.7 100.0 0.0 0.7 63.9
476 ...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 0.7 96.6
477 ...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
478 ...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
479 ...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
480 ...emplate/Container/Loop.pm 90.9 50.0 50.0 100.0 50.0 0.5 80.4
481 ...late/Container/Outline.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
482 ...Template/Container/Row.pm 100.0 75.0 n/a 100.0 50.0 0.3 93.8
483 ...mplate/Container/Scope.pm 100.0 n/a n/a 100.0 n/a 0.1 100.0
484 ...plate/Container/Shadow.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
485 ...te/Container/Strikeout.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
486 ...ate/Container/Workbook.pm 100.0 n/a n/a 100.0 n/a 0.9 100.0
487 ...te/Container/Worksheet.pm 94.1 50.0 n/a 100.0 0.0 0.9 88.0
488 ...Excel/Template/Context.pm 84.3 53.4 54.2 100.0 92.3 19.5 76.0
489 ...Excel/Template/Element.pm 100.0 n/a n/a 100.0 n/a 0.5 100.0
490 ...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.1 87.1
491 .../Template/Element/Cell.pm 95.8 65.0 80.0 100.0 66.7 3.8 86.9
492 ...mplate/Element/Formula.pm 100.0 n/a n/a 100.0 0.0 0.3 94.1
493 ...Template/Element/Range.pm 100.0 66.7 n/a 100.0 66.7 0.2 93.3
494 ...l/Template/Element/Var.pm 100.0 n/a n/a 100.0 0.0 0.1 94.1
495 ...Excel/Template/Factory.pm 100.0 73.1 n/a 100.0 100.0 16.3 92.6
496 .../Excel/Template/Format.pm 98.3 81.2 33.3 100.0 100.0 10.0 93.2
497 ...xcel/Template/Iterator.pm 98.6 80.0 70.6 100.0 83.3 1.9 90.3
498 ...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 50.0 3.1 83.0
499 Total 92.0 63.5 58.3 97.5 98.5 100.0 86.0
500 -----------------------------------------------------------------------
504 This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
506 The full text of the license can be found in the LICENSE file included with this module.
510 perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel>