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->parse_xml($self->{FILENAME})
33 if defined $self->{FILENAME};
35 my @renderer_classes = ( 'Spreadsheet::WriteExcel' );
37 if (exists $self->{RENDERER} && $self->{RENDERER})
39 if (exists $renderers{ lc $self->{RENDERER} })
41 unshift @renderer_classes, $renderers{ lc $self->{RENDERER} };
45 warn "'$self->{RENDERER}' is not recognized\n";
48 elsif (exists $self->{BIG_FILE} && $self->{BIG_FILE})
50 warn "Use of BIG_FILE is deprecated.\n";
51 unshift @renderer_classes, 'Spreadsheet::WriteExcel::Big';
54 $self->{RENDERER} = undef;
55 foreach my $class (@renderer_classes)
57 (my $filename = $class) =~ s!::!/!g;
59 require "$filename.pm";
63 warn "Could not find or compile '$class'\n" if $^W;
65 $self->{RENDERER} = $class;
70 defined $self->{RENDERER} ||
71 die "Could not find a renderer class. Tried:\n\t" .
72 join("\n\t", @renderer_classes) .
75 $self->{USE_UNICODE} = ~~0
85 # Allow an arbitrary number of hashrefs, so long as they're the first things # into param(). Put each one onto the end, de-referenced.
86 push @_, %{shift @_} while UNIVERSAL::isa($_[0], 'HASH');
89 && die __PACKAGE__, "->param() : Odd number of parameters to param()\n";
92 $params{uc $_} = delete $params{$_} for keys %params;
93 @{$self->{PARAM_MAP}}{keys %params} = @params{keys %params};
103 my $xls = $self->{RENDERER}->new($filename)
104 || die "Cannot create XLS in '$filename': $!\n";
106 $self->_prepare_output($xls);
118 tie *XLS, 'IO::Scalar', \$output;
120 $self->write_file(\*XLS);
130 my ($filename, $dirname) = fileparse($fname);
133 my $parser = XML::Parser->new(
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]);
190 open( INFILE, "<$fname" )
191 || die "Cannot open '$fname' for reading: $!\n";
193 $parser->parse(do { local $/ = undef; <INFILE> });
200 *parse = \&parse_xml;
207 my $context = Excel::Template::Factory->_create(
211 PARAM_MAP => [ $self->{PARAM_MAP} ],
212 UNICODE => $self->{UNICODE},
215 $_->render($context) for @{$self->{WORKBOOKS}};
220 sub register { shift; Excel::Template::Factory::register(@_) }
227 Excel::Template - Excel::Template
231 First, make a template. This is an XML file, describing the layout of the
234 For example, test.xml:
237 <worksheet name="tester">
243 Now, create a small program to use it:
248 # Create the Excel template
249 my $template = Excel::Template->new(
250 filename => 'test.xml',
253 # Add a few parameters
259 $template->write_file('test.xls');
261 If everything worked, then you should have a spreadsheet in your work directory
262 that looks something like:
265 +----------------+----------------+----------------
266 1 | /home/me | /bin:/usr/bin |
267 +----------------+----------------+----------------
269 +----------------+----------------+----------------
274 This is a module used for templating Excel files. Its genesis came from the
275 need to use the same datastructure as HTML::Template, but provide Excel files
276 instead. The existing modules don't do the trick, as they require replication
277 of logic that's already been done within HTML::Template.
281 I do a lot of Perl/CGI for reporting purposes. In nearly every place I've been,
282 I've been asked for HTML, PDF, and Excel. HTML::Template provides the first, and
283 PDF::Template does the second pretty well. But, generating Excel was the
284 sticking point. I already had the data structure for the other templating
285 modules, but I just didn't have an easy mechanism to get that data structure
292 This creates a Excel::Template object. If passed a FILENAME parameter, it will
293 parse the template in the given file. (You can also use the parse() method,
302 The default rendering engine is Spreadsheet::WriteExcel. You may, if you choose, change that to another choice. The legal values are:
306 =item * Excel::Template->RENDER_NML
308 This is the default of Spreadsheet::WriteExcel.
310 =item * Excel::Template->RENDER_BIG
312 This attempts to load Spreadsheet::WriteExcel::Big.
314 =item * Excel::Template->RENDER_XML
316 This attempts to load Spreadsheet::WriteExcelXML.
322 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.
324 The USE_UNICODE parameter will be ignored if you are using Perl 5.8 or higher as
325 Perl's internal string handling is unicode-aware.
327 NOTE: Certain older versions of L<OLE::Storage_Lite> and mod_perl clash for some
328 reason. Upgrading to the latest version of L<OLE::Storage_Lite> should fix the
339 Instead, use RENDERER => Excel::Template->RENDER_BIG
345 This method is exactly like L<HTML::Template>'s param() method.
347 =head2 parse() / parse_xml()
349 This method actually parses the template file. It can either be called
350 separately or through the new() call. It will die() if it runs into a situation
355 Create the Excel file and write it to the specified filename, if possible. (This
356 is when the actual merging of the template and the parameters occurs.)
360 It will act just like HTML::Template's output() method, returning the resultant
361 file as a stream, usually for output to the web. (This is when the actual
362 merging of the template and the parameters occurs.)
366 This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info.
368 =head1 SUPPORTED NODES
370 This is a partial list of nodes. See the other classes in this distro for more
371 details on specific parameters and the like.
373 Every node can set the ROW and COL parameters. These are the actual ROW/COL
374 values that the next CELL-type tag will write into.
378 =item * L<WORKBOOK|Excel::Template::Container::Workbook>
380 This is the node representing the workbook. It is the parent for all other
383 =item * L<WORKSHEET|Excel::Template::Container::Worksheet>
385 This is the node representing a given worksheet.
387 =item * L<IF|Excel::Template::Container::Conditional>
389 This node represents a conditional expression. Its children may or may not be
390 rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
392 =item * L<LOOP|Excel::Template::Container::Loop>
394 This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
396 =item * L<ROW|Excel::Template::Container::Row>
398 This node represents a row of data. This is the A in A1.
400 =item * L<FORMAT|Excel::Template::Container::Format>
402 This node varies the format for its children. All formatting options supported
403 in L<Spreadsheet::WriteExcel> are supported here. There are also a number of
404 formatting shortcuts, such as L<BOLD|Excel::Template::Container::Bold> and
405 L<ITALIC|Excel::Template::Container::Italic>.
407 =item * L<BACKREF|Excel::Template::Element::Backref>
409 This refers back to a cell previously named.
411 =item * L<CELL|Excel::Template::Element::Cell>
413 This is the actual cell in a spreadsheet.
415 =item * L<FORMULA|Excel::Template::Element::Formula>
417 This is a formula in a spreadsheet.
419 =item * L<RANGE|Excel::Template::Element::Range>
421 This is a BACKREF for a number of identically-named cells.
423 =item * L<VAR|Excel::Template::Element::Var>
425 This is a variable. It is generally used when the 'text' attribute isn't
432 None, that I know of.
436 This is production quality software, used in several production web
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 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 branch cond sub pod time total
465 ---------------------------- ------ ------ ------ ------ ------ ------ ------
466 blib/lib/Excel/Template.pm 90.0 57.1 50.0 90.5 100.0 26.0 80.8
467 ...ib/Excel/Template/Base.pm 83.3 50.0 66.7 75.0 88.9 8.8 80.0
468 ...cel/Template/Container.pm 46.3 20.0 33.3 58.3 85.7 4.6 47.7
469 ...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.6 95.0
470 .../Container/Conditional.pm 58.5 52.3 66.7 75.0 66.7 0.7 58.4
471 ...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 0.8 96.6
472 ...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.2 95.0
473 ...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.2 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 55.6 40.0 50.0 77.8 75.0 0.5 56.6
476 ...late/Container/Outline.pm 71.4 n/a n/a 80.0 0.0 0.0 70.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 1.1 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 83.1 53.4 54.2 95.0 92.9 19.2 75.2
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.6 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.2 94.1
490 ...Excel/Template/Factory.pm 57.1 34.6 n/a 88.9 100.0 15.4 55.2
491 .../Excel/Template/Format.pm 98.3 81.2 33.3 100.0 100.0 9.9 93.2
492 ...xcel/Template/Iterator.pm 85.2 70.6 70.6 84.6 87.5 2.0 80.4
493 ...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 50.0 3.6 83.0
494 Total 83.0 55.6 57.0 91.1 98.7 100.0 78.6
495 ---------------------------- ------ ------ ------ ------ ------ ------ ------
499 This program is free software; you can redistribute
500 it and/or modify it under the same terms as Perl itself.
502 The full text of the license can be found in the
503 LICENSE file included with this module.
507 perl(1), HTML::Template, Spreadsheet::WriteExcel.