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 UNIVERSAL::isa($_[0], '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";
109 $self->_prepare_output($xls);
121 tie *XLS, 'IO::Scalar', \$output;
123 $self->write_file(\*XLS);
141 my $node = Excel::Template::Factory->_create_node($name, @_);
142 die "'$name' (@_) didn't make a node!\n" unless defined $node;
144 if ( $node->isa( 'WORKBOOK' ) )
146 push @{$self->{WORKBOOKS}}, $node;
148 elsif ( $node->is_embedded )
150 return unless @stack;
152 if (exists $stack[-1]{TXTOBJ} &&
153 $stack[-1]{TXTOBJ}->isa('TEXTOBJECT'))
155 push @{$stack[-1]{TXTOBJ}{STACK}}, $node;
161 push @{$stack[-1]{ELEMENTS}}, $node
168 return unless @stack;
170 my $parent = $stack[-1];
173 exists $parent->{TXTOBJ}
175 $parent->{TXTOBJ}->isa('TEXTOBJECT')
177 push @{$parent->{TXTOBJ}{STACK}}, @_;
182 return unless @stack;
184 pop @stack if $stack[-1]->isa(uc $_[0]);
195 my ($filename, $dirname) = fileparse($file);
197 push @parms, Base => $dirname;
199 open( INFILE, "<$file" )
200 || die "Cannot open '$file' for reading: $!\n";
204 my $parser = XML::Parser->new( @parms );
205 $parser->parse(do { local $/ = undef; <INFILE> });
214 *parse = \&parse_xml;
221 my $context = Excel::Template::Factory->_create(
225 PARAM_MAP => [ $self->{PARAM_MAP} ],
226 UNICODE => $self->{UNICODE},
229 $_->render($context) for @{$self->{WORKBOOKS}};
234 sub register { shift; Excel::Template::Factory->register(@_) }
241 Excel::Template - Excel::Template
245 First, make a template. This is an XML file, describing the layout of the
248 For example, test.xml:
251 <worksheet name="tester">
252 <cell text="$HOME" />
253 <cell text="$PATH" />
257 Now, create a small program to use it:
265 # Create the Excel template
266 my $template = Excel::Template->new(
267 filename => 'test.xml',
270 # Add a few parameters
276 $template->write_file('test.xls');
278 If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like:
281 +----------------+----------------+----------------
282 1 | /home/me | /bin:/usr/bin |
283 +----------------+----------------+----------------
285 +----------------+----------------+----------------
290 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>.
294 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.
300 This creates a Excel::Template object.
306 =item * FILE / FILENAME
308 Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.)
310 If you want to use the __DATA__ section, you can do so by passing
316 The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are:
320 =item * Excel::Template->RENDER_NML
322 This is the default of L<Spreadsheet::WriteExcel>.
324 =item * Excel::Template->RENDER_BIG
326 This attempts to load L<Spreadsheet::WriteExcel::Big>.
328 =item * Excel::Template->RENDER_XML
330 This attempts to load L<Spreadsheet::WriteExcelXML>.
336 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.
338 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.
340 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.
350 Instead, use RENDERER => Excel::Template->RENDER_BIG
356 This method is exactly like L<HTML::Template>'s param() method.
358 =head2 parse() / parse_xml()
360 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.
362 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.
366 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.)
370 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.)
374 This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info.
376 =head1 SUPPORTED NODES
378 This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like.
380 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.
384 =item * L<WORKBOOK|Excel::Template::Container::Workbook>
386 This is the node representing the workbook. It is the parent for all other nodes.
388 =item * L<WORKSHEET|Excel::Template::Container::Worksheet>
390 This is the node representing a given worksheet.
392 =item * L<IF|Excel::Template::Container::Conditional>
394 This node represents a conditional expression. Its children may or may not be rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
396 =item * L<LOOP|Excel::Template::Container::Loop>
398 This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
400 =item * L<ROW|Excel::Template::Container::Row>
402 This node represents a row of data. This is the A in A1.
404 =item * L<FORMAT|Excel::Template::Container::Format>
406 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>.
408 =item * L<BACKREF|Excel::Template::Element::Backref>
410 This refers back to a cell previously named.
412 =item * L<CELL|Excel::Template::Element::Cell>
414 This is the actual cell in a spreadsheet.
416 =item * L<FORMULA|Excel::Template::Element::Formula>
418 This is a formula in a spreadsheet.
420 =item * L<RANGE|Excel::Template::Element::Range>
422 This is a BACKREF for a number of identically-named cells.
424 =item * L<VAR|Excel::Template::Element::Var>
426 This is a variable. It is generally used when the 'text' attribute isn't
433 None, that I know of.
437 This is production quality software, used in several production web applications.
441 Rob Kinyon (rob.kinyon@gmail.com)
445 There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com
451 =item * Finishing formats
453 =item * Fixing several bugs in worksheet naming
459 I used L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers.
461 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.
463 -----------------------------------------------------------------------
464 File stmt brnch cond sub pod time total
465 -----------------------------------------------------------------------
466 blib/lib/Excel/Template.pm 96.0 62.5 58.8 100.0 100.0 25.2 86.2
467 ...ib/Excel/Template/Base.pm 87.0 50.0 66.7 81.8 87.5 8.7 83.0
468 ...cel/Template/Container.pm 71.4 50.0 33.3 70.0 80.0 4.9 68.4
469 ...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.7 95.0
470 .../Container/Conditional.pm 64.9 57.5 66.7 100.0 0.0 0.7 63.9
471 ...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 0.7 96.6
472 ...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
473 ...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
474 ...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
475 ...emplate/Container/Loop.pm 90.9 50.0 50.0 100.0 50.0 0.5 80.4
476 ...late/Container/Outline.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
477 ...Template/Container/Row.pm 100.0 75.0 n/a 100.0 50.0 0.3 93.8
478 ...mplate/Container/Scope.pm 100.0 n/a n/a 100.0 n/a 0.1 100.0
479 ...plate/Container/Shadow.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
480 ...te/Container/Strikeout.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
481 ...ate/Container/Workbook.pm 100.0 n/a n/a 100.0 n/a 0.9 100.0
482 ...te/Container/Worksheet.pm 94.1 50.0 n/a 100.0 0.0 0.9 88.0
483 ...Excel/Template/Context.pm 84.3 53.4 54.2 100.0 92.3 19.5 76.0
484 ...Excel/Template/Element.pm 100.0 n/a n/a 100.0 n/a 0.5 100.0
485 ...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.1 87.1
486 .../Template/Element/Cell.pm 95.8 65.0 80.0 100.0 66.7 3.8 86.9
487 ...mplate/Element/Formula.pm 100.0 n/a n/a 100.0 0.0 0.3 94.1
488 ...Template/Element/Range.pm 100.0 66.7 n/a 100.0 66.7 0.2 93.3
489 ...l/Template/Element/Var.pm 100.0 n/a n/a 100.0 0.0 0.1 94.1
490 ...Excel/Template/Factory.pm 100.0 73.1 n/a 100.0 100.0 16.3 92.6
491 .../Excel/Template/Format.pm 98.3 81.2 33.3 100.0 100.0 10.0 93.2
492 ...xcel/Template/Iterator.pm 98.6 80.0 70.6 100.0 83.3 1.9 90.3
493 ...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 50.0 3.1 83.0
494 Total 92.0 63.5 58.3 97.5 98.5 100.0 86.0
495 -----------------------------------------------------------------------
499 This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
501 The full text of the license can be found in the LICENSE file included with this module.
505 perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel>