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);
126 tie *XLS, 'IO::Scalar', \$output;
128 $self->write_file(\*XLS)
147 my $node = Excel::Template::Factory->_create_node($name, @_);
148 die "'$name' (@_) didn't make a node!\n" unless defined $node;
150 if ( $node->isa( 'WORKBOOK' ) )
152 $self->{WORKBOOK} = $node;
154 elsif ( $node->is_embedded )
156 return unless @stack;
158 if (exists $stack[-1]{TXTOBJ} &&
159 $stack[-1]{TXTOBJ}->isa('TEXTOBJECT'))
161 push @{$stack[-1]{TXTOBJ}{STACK}}, $node;
167 push @{$stack[-1]{ELEMENTS}}, $node
174 return unless @stack;
176 my $parent = $stack[-1];
179 exists $parent->{TXTOBJ}
181 $parent->{TXTOBJ}->isa('TEXTOBJECT')
183 push @{$parent->{TXTOBJ}{STACK}}, @_;
188 return unless @stack;
190 pop @stack if $stack[-1]->isa(uc $_[0]);
201 my ($filename, $dirname) = fileparse($file);
203 push @parms, Base => $dirname;
205 open( INFILE, "<$file" )
206 || die "Cannot open '$file' for reading: $!\n";
210 my $parser = XML::Parser->new( @parms );
211 $parser->parse(do { local $/ = undef; <INFILE> });
218 *parse = *parse = \&parse_xml;
223 return unless $self->{WORKBOOK};
227 my $context = Excel::Template::Factory->_create(
231 PARAM_MAP => [ $self->{PARAM_MAP} ],
232 UNICODE => $self->{UNICODE},
235 $self->{WORKBOOK}->render($context);
240 sub register { shift; Excel::Template::Factory->register(@_) }
247 Excel::Template - Excel::Template
251 First, make a template. This is an XML file, describing the layout of the
254 For example, test.xml:
257 <worksheet name="tester">
258 <cell text="$HOME" />
259 <cell text="$PATH" />
263 Now, create a small program to use it:
271 # Create the Excel template
272 my $template = Excel::Template->new(
273 filename => 'test.xml',
276 # Add a few parameters
282 $template->write_file('test.xls');
284 If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like:
287 +----------------+----------------+----------------
288 1 | /home/me | /bin:/usr/bin |
289 +----------------+----------------+----------------
291 +----------------+----------------+----------------
296 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>.
300 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.
306 This creates a Excel::Template object.
312 =item * FILE / FILENAME
314 Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.)
316 If you want to use the __DATA__ section, you can do so by passing
322 The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are:
326 =item * Excel::Template->RENDER_NML
328 This is the default of L<Spreadsheet::WriteExcel>.
330 =item * Excel::Template->RENDER_BIG
332 This attempts to load L<Spreadsheet::WriteExcel::Big>.
334 =item * Excel::Template->RENDER_XML
336 This attempts to load L<Spreadsheet::WriteExcelXML>.
342 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.
344 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.
346 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.
356 Instead, use RENDERER => Excel::Template->RENDER_BIG
362 This method is exactly like L<HTML::Template>'s param() method.
364 =head2 parse() / parse_xml()
366 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.
368 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.
372 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.)
376 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.)
380 This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info.
382 =head1 SUPPORTED NODES
384 This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like.
386 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.
390 =item * L<WORKBOOK|Excel::Template::Container::Workbook>
392 This is the node representing the workbook. It is the parent for all other nodes.
394 =item * L<WORKSHEET|Excel::Template::Container::Worksheet>
396 This is the node representing a given worksheet.
398 =item * L<IF|Excel::Template::Container::Conditional>
400 This node represents a conditional expression. Its children may or may not be rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
402 =item * L<LOOP|Excel::Template::Container::Loop>
404 This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
406 =item * L<ROW|Excel::Template::Container::Row>
408 This node represents a row of data. This is the 1 in A1. There is no COLUMN node, as of yet.
410 =item * L<FORMAT|Excel::Template::Container::Format>
412 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>.
414 =item * L<BACKREF|Excel::Template::Element::Backref>
416 This refers back to a cell previously named.
418 =item * L<CELL|Excel::Template::Element::Cell>
420 This is the actual cell in a spreadsheet.
422 =item * L<FORMULA|Excel::Template::Element::Formula>
424 This is a formula in a spreadsheet.
426 =item * L<RANGE|Excel::Template::Element::Range>
428 This is a BACKREF for a number of identically-named cells.
430 =item * L<VAR|Excel::Template::Element::Var>
432 This is a variable. It is generally used when the 'text' attribute isn't
439 None, that I know of.
443 This is production quality software, used in several production web applications.
447 Rob Kinyon (rob.kinyon@gmail.com)
451 There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com
457 =item * Finishing formats
459 =item * Fixing several bugs in worksheet naming
465 I used L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers.
467 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.
469 ---------------------------- ------ ------ ------ ------ ------ ------ ------
470 File stmt branch cond sub pod time total
471 ---------------------------- ------ ------ ------ ------ ------ ------ ------
472 blib/lib/Excel/Template.pm 96.0 64.3 58.8 100.0 100.0 26.3 85.8
473 ...ib/Excel/Template/Base.pm 94.4 50.0 n/a 100.0 75.0 6.6 90.0
474 ...cel/Template/Container.pm 100.0 50.0 33.3 100.0 66.7 4.5 88.1
475 ...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.2 95.0
476 .../Container/Conditional.pm 95.9 90.0 66.7 100.0 0.0 1.7 91.0
477 ...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 0.6 96.6
478 ...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.2 95.0
479 ...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.2 95.0
480 ...ainer/KeepLeadingZeros.pm 100.0 100.0 n/a 100.0 0.0 0.1 96.3
481 ...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
482 ...emplate/Container/Loop.pm 96.8 50.0 50.0 100.0 50.0 0.4 84.6
483 ...late/Container/Outline.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
484 ...Template/Container/Row.pm 100.0 75.0 n/a 100.0 50.0 0.3 93.8
485 ...mplate/Container/Scope.pm 100.0 n/a n/a 100.0 n/a 0.1 100.0
486 ...plate/Container/Shadow.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
487 ...te/Container/Strikeout.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
488 ...ate/Container/Workbook.pm 100.0 n/a n/a 100.0 n/a 1.0 100.0
489 ...te/Container/Worksheet.pm 94.7 75.0 n/a 100.0 50.0 1.0 90.3
490 ...Excel/Template/Context.pm 98.0 80.0 75.0 100.0 93.3 23.4 91.8
491 ...Excel/Template/Element.pm 100.0 n/a n/a 100.0 n/a 0.3 100.0
492 ...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.2 87.1
493 .../Template/Element/Cell.pm 97.9 75.0 80.0 100.0 66.7 3.9 91.1
494 ...mplate/Element/Formula.pm 100.0 n/a n/a 100.0 0.0 0.2 94.1
495 ...Template/Element/Range.pm 100.0 66.7 n/a 100.0 66.7 0.2 93.3
496 ...l/Template/Element/Var.pm 100.0 n/a n/a 100.0 0.0 0.2 94.1
497 ...Excel/Template/Factory.pm 100.0 75.0 n/a 100.0 100.0 15.5 92.4
498 .../Excel/Template/Format.pm 98.4 75.0 33.3 100.0 66.7 8.1 91.2
499 ...xcel/Template/Iterator.pm 98.6 80.0 70.6 100.0 83.3 1.4 90.3
500 ...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 50.0 3.2 83.0
501 Total 98.0 75.2 63.4 100.0 98.5 100.0 92.2
502 ---------------------------- ------ ------ ------ ------ ------ ------ ------
506 This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
508 The full text of the license can be found in the LICENSE file included with this module.
512 perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel>