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;
206 open( INFILE, '<', $file )
207 || die "Cannot open '$file' for reading: $!\n";
209 if ( $@ =~ /Too many arguments for open/ ) {
210 open( INFILE, "< $file" )
211 || die "Cannot open '$file' for reading: $!\n";
218 my $parser = XML::Parser->new( @parms );
219 $parser->parse(do { local $/ = undef; <INFILE> });
226 *parse = *parse = \&parse_xml;
231 return unless $self->{WORKBOOK};
235 my $context = Excel::Template::Factory->_create(
239 PARAM_MAP => [ $self->{PARAM_MAP} ],
240 UNICODE => $self->{UNICODE},
243 $self->{WORKBOOK}->render($context);
248 sub register { shift; Excel::Template::Factory->register(@_) }
255 Excel::Template - Excel::Template
259 First, make a template. This is an XML file, describing the layout of the
262 For example, test.xml:
265 <worksheet name="tester">
266 <cell text="$HOME" />
267 <cell text="$PATH" />
271 Now, create a small program to use it:
279 # Create the Excel template
280 my $template = Excel::Template->new(
281 filename => 'test.xml',
284 # Add a few parameters
290 $template->write_file('test.xls');
292 If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like:
295 +----------------+----------------+----------------
296 1 | /home/me | /bin:/usr/bin |
297 +----------------+----------------+----------------
299 +----------------+----------------+----------------
304 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>.
308 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.
314 This creates a Excel::Template object.
320 =item * FILE / FILENAME
322 Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.)
324 If you want to use the __DATA__ section, you can do so by passing
330 The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are:
334 =item * Excel::Template->RENDER_NML
336 This is the default of L<Spreadsheet::WriteExcel>.
338 =item * Excel::Template->RENDER_BIG
340 This attempts to load L<Spreadsheet::WriteExcel::Big>.
342 =item * Excel::Template->RENDER_XML
344 This attempts to load L<Spreadsheet::WriteExcelXML>.
350 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.
352 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.
354 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.
364 Instead, use RENDERER => Excel::Template->RENDER_BIG
370 This method is exactly like L<HTML::Template>'s param() method.
372 =head2 parse() / parse_xml()
374 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.
376 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.
380 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.)
384 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.)
388 This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info.
390 =head1 SUPPORTED NODES
392 This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like.
394 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.
398 =item * L<WORKBOOK|Excel::Template::Container::Workbook>
400 This is the node representing the workbook. It is the parent for all other nodes.
402 =item * L<WORKSHEET|Excel::Template::Container::Worksheet>
404 This is the node representing a given worksheet.
406 =item * L<IF|Excel::Template::Container::Conditional>
408 This node represents a conditional expression. Its children may or may not be rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
410 =item * L<LOOP|Excel::Template::Container::Loop>
412 This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
414 =item * L<ROW|Excel::Template::Container::Row>
416 This node represents a row of data. This is the 1 in A1. There is no COLUMN node, as of yet.
418 =item * L<FORMAT|Excel::Template::Container::Format>
420 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>.
422 =item * L<BACKREF|Excel::Template::Element::Backref>
424 This refers back to a cell previously named.
426 =item * L<CELL|Excel::Template::Element::Cell>
428 This is the actual cell in a spreadsheet.
430 =item * L<FORMULA|Excel::Template::Element::Formula>
432 This is a formula in a spreadsheet.
434 =item * L<RANGE|Excel::Template::Element::Range>
436 This is a BACKREF for a number of identically-named cells.
438 =item * L<VAR|Excel::Template::Element::Var>
440 This is a variable. It is generally used when the 'text' attribute isn't
447 None, that I know of.
451 This is production quality software, used in several production web applications.
455 Rob Kinyon (rob.kinyon@gmail.com)
459 There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com
465 =item * Finishing formats
467 =item * Fixing several bugs in worksheet naming
473 I use L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers.
475 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.
477 ---------------------------- ------ ------ ------ ------ ------ ------ ------
478 File stmt bran cond sub pod time total
479 ---------------------------- ------ ------ ------ ------ ------ ------ ------
480 blib/lib/Excel/Template.pm 93.8 60.0 58.8 100.0 100.0 31.8 83.3
481 ...ib/Excel/Template/Base.pm 94.4 50.0 n/a 100.0 0.0 4.4 80.0
482 ...cel/Template/Container.pm 100.0 50.0 33.3 100.0 0.0 2.0 83.3
483 ...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
484 .../Container/Conditional.pm 95.9 90.0 66.7 100.0 0.0 0.3 91.0
485 ...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 1.5 96.8
486 ...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
487 ...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
488 ...ainer/KeepLeadingZeros.pm 100.0 100.0 n/a 100.0 0.0 0.0 96.3
489 ...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
490 ...emplate/Container/Loop.pm 96.8 50.0 50.0 100.0 0.0 0.1 82.7
491 ...late/Container/Outline.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
492 ...Template/Container/Row.pm 100.0 75.0 n/a 100.0 0.0 0.1 90.6
493 ...mplate/Container/Scope.pm 100.0 n/a n/a 100.0 n/a 0.0 100.0
494 ...plate/Container/Shadow.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
495 ...te/Container/Strikeout.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
496 ...ate/Container/Workbook.pm 100.0 n/a n/a 100.0 n/a 7.0 100.0
497 ...te/Container/Worksheet.pm 95.5 87.5 100.0 100.0 0.0 1.1 90.2
498 ...Excel/Template/Context.pm 98.0 80.0 75.0 100.0 73.3 17.0 90.7
499 ...Excel/Template/Element.pm 100.0 n/a n/a 100.0 n/a 0.1 100.0
500 ...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.1 87.1
501 .../Template/Element/Cell.pm 97.9 75.0 80.0 100.0 0.0 5.6 88.6
502 ...mplate/Element/Formula.pm 100.0 n/a n/a 100.0 0.0 0.0 94.1
503 ...te/Element/FreezePanes.pm 100.0 n/a n/a 100.0 0.0 0.0 95.5
504 ...Template/Element/Image.pm 100.0 100.0 n/a 100.0 0.0 0.0 94.3
505 ...Template/Element/Range.pm 100.0 66.7 n/a 100.0 0.0 0.1 88.9
506 ...l/Template/Element/Var.pm 100.0 n/a n/a 100.0 0.0 0.0 94.1
507 ...Excel/Template/Factory.pm 100.0 73.1 66.7 100.0 100.0 22.3 91.4
508 .../Excel/Template/Format.pm 98.4 75.0 33.3 100.0 66.7 2.6 90.5
509 ...xcel/Template/Iterator.pm 98.6 80.0 70.6 100.0 50.0 0.3 88.8
510 ...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 0.0 3.3 80.9
511 Total 97.8 74.7 64.6 100.0 35.7 100.0 89.4
512 ---------------------------- ------ ------ ------ ------ ------ ------ ------
516 This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
518 The full text of the license can be found in the LICENSE file included with this module.
522 perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel>