Commit | Line | Data |
d0eafc11 |
1 | package Excel::Template; |
2 | |
3 | use strict; |
4 | |
5 | BEGIN { |
6 | use Excel::Template::Base; |
7 | use vars qw ($VERSION @ISA); |
8 | |
82112547 |
9 | $VERSION = '0.28'; |
d0eafc11 |
10 | @ISA = qw( Excel::Template::Base ); |
11 | } |
12 | |
13 | use File::Basename; |
14 | use XML::Parser; |
d0eafc11 |
15 | use IO::Scalar; |
16 | |
6dd4c89d |
17 | use constant RENDER_NML => 'normal'; |
18 | use constant RENDER_BIG => 'big'; |
19 | use constant RENDER_XML => 'xml'; |
20 | |
21 | my %renderers = ( |
22 | RENDER_NML, 'Spreadsheet::WriteExcel', |
23 | RENDER_BIG, 'Spreadsheet::WriteExcel::Big', |
24 | RENDER_XML, 'Spreadsheet::WriteExcelXML', |
25 | ); |
26 | |
d0eafc11 |
27 | sub new |
28 | { |
29 | my $class = shift; |
30 | my $self = $class->SUPER::new(@_); |
31 | |
a084e413 |
32 | $self->{FILE} = $self->{FILENAME} |
33 | if !defined $self->{FILE} && defined $self->{FILENAME}; |
34 | |
35 | $self->parse_xml($self->{FILE}) |
36 | if defined $self->{FILE}; |
d0eafc11 |
37 | |
38 | my @renderer_classes = ( 'Spreadsheet::WriteExcel' ); |
6dd4c89d |
39 | |
40 | if (exists $self->{RENDERER} && $self->{RENDERER}) |
d0eafc11 |
41 | { |
6dd4c89d |
42 | if (exists $renderers{ lc $self->{RENDERER} }) |
43 | { |
44 | unshift @renderer_classes, $renderers{ lc $self->{RENDERER} }; |
45 | } |
46 | elsif ($^W) |
47 | { |
48 | warn "'$self->{RENDERER}' is not recognized\n"; |
49 | } |
d0eafc11 |
50 | } |
6dd4c89d |
51 | elsif (exists $self->{BIG_FILE} && $self->{BIG_FILE}) |
9d172425 |
52 | { |
6dd4c89d |
53 | warn "Use of BIG_FILE is deprecated.\n"; |
54 | unshift @renderer_classes, 'Spreadsheet::WriteExcel::Big'; |
9d172425 |
55 | } |
56 | |
d0eafc11 |
57 | $self->{RENDERER} = undef; |
58 | foreach my $class (@renderer_classes) |
59 | { |
60 | (my $filename = $class) =~ s!::!/!g; |
61 | eval { |
62 | require "$filename.pm"; |
63 | $class->import; |
64 | }; |
65 | if ($@) { |
dee1f239 |
66 | warn "Could not find or compile '$class'\n" if $^W; |
d0eafc11 |
67 | } else { |
68 | $self->{RENDERER} = $class; |
69 | last; |
70 | } |
71 | } |
72 | |
73 | defined $self->{RENDERER} || |
74 | die "Could not find a renderer class. Tried:\n\t" . |
75 | join("\n\t", @renderer_classes) . |
76 | "\n"; |
77 | |
8c63e224 |
78 | $self->{USE_UNICODE} = ~~0 |
79 | if $] >= 5.008; |
80 | |
d0eafc11 |
81 | return $self; |
82 | } |
83 | |
84 | sub param |
85 | { |
86 | my $self = shift; |
87 | |
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. |
d01e4722 |
89 | push @_, %{shift @_} while ref $_[0] eq 'HASH'; |
d0eafc11 |
90 | |
91 | (@_ % 2) |
92 | && die __PACKAGE__, "->param() : Odd number of parameters to param()\n"; |
93 | |
94 | my %params = @_; |
95 | $params{uc $_} = delete $params{$_} for keys %params; |
96 | @{$self->{PARAM_MAP}}{keys %params} = @params{keys %params}; |
97 | |
8c63e224 |
98 | return ~~1; |
d0eafc11 |
99 | } |
100 | |
101 | sub write_file |
102 | { |
103 | my $self = shift; |
104 | my ($filename) = @_; |
105 | |
106 | my $xls = $self->{RENDERER}->new($filename) |
107 | || die "Cannot create XLS in '$filename': $!\n"; |
108 | |
d01e4722 |
109 | eval { |
110 | $self->_prepare_output($xls); |
111 | }; |
9ee3aea0 |
112 | print $@ if $@; |
d0eafc11 |
113 | |
114 | $xls->close; |
115 | |
d01e4722 |
116 | return if $@; |
117 | |
8c63e224 |
118 | return ~~1; |
d0eafc11 |
119 | } |
120 | |
121 | sub output |
122 | { |
123 | my $self = shift; |
124 | |
125 | my $output; |
126 | tie *XLS, 'IO::Scalar', \$output; |
127 | |
d01e4722 |
128 | $self->write_file(\*XLS) |
129 | or return; |
d0eafc11 |
130 | |
131 | return $output; |
132 | } |
133 | |
d0eafc11 |
134 | sub parse_xml |
135 | { |
136 | my $self = shift; |
a084e413 |
137 | my ($file) = @_; |
d0eafc11 |
138 | |
d0eafc11 |
139 | my @stack; |
a084e413 |
140 | my @parms = ( |
d0eafc11 |
141 | Handlers => { |
142 | Start => sub { |
143 | shift; |
144 | |
145 | my $name = uc shift; |
146 | |
6dd4c89d |
147 | my $node = Excel::Template::Factory->_create_node($name, @_); |
d0eafc11 |
148 | die "'$name' (@_) didn't make a node!\n" unless defined $node; |
149 | |
37513eae |
150 | if ( $node->isa( 'WORKBOOK' ) ) |
d0eafc11 |
151 | { |
d01e4722 |
152 | $self->{WORKBOOK} = $node; |
d0eafc11 |
153 | } |
37513eae |
154 | elsif ( $node->is_embedded ) |
d0eafc11 |
155 | { |
156 | return unless @stack; |
157 | |
158 | if (exists $stack[-1]{TXTOBJ} && |
159 | $stack[-1]{TXTOBJ}->isa('TEXTOBJECT')) |
160 | { |
161 | push @{$stack[-1]{TXTOBJ}{STACK}}, $node; |
162 | } |
163 | |
164 | } |
165 | else |
166 | { |
167 | push @{$stack[-1]{ELEMENTS}}, $node |
168 | if @stack; |
169 | } |
170 | push @stack, $node; |
171 | }, |
172 | Char => sub { |
173 | shift; |
174 | return unless @stack; |
175 | |
176 | my $parent = $stack[-1]; |
177 | |
178 | if ( |
179 | exists $parent->{TXTOBJ} |
180 | && |
181 | $parent->{TXTOBJ}->isa('TEXTOBJECT') |
182 | ) { |
183 | push @{$parent->{TXTOBJ}{STACK}}, @_; |
184 | } |
185 | }, |
186 | End => sub { |
187 | shift; |
188 | return unless @stack; |
189 | |
190 | pop @stack if $stack[-1]->isa(uc $_[0]); |
191 | }, |
192 | }, |
193 | ); |
194 | |
a084e413 |
195 | if ( ref $file ) |
196 | { |
197 | *INFILE = $file; |
198 | } |
199 | else |
d0eafc11 |
200 | { |
a084e413 |
201 | my ($filename, $dirname) = fileparse($file); |
202 | |
203 | push @parms, Base => $dirname; |
204 | |
64f0fd9a |
205 | eval q{ |
206 | open( INFILE, '<', $file ) |
207 | || die "Cannot open '$file' for reading: $!\n"; |
208 | }; if ( $@ ) { |
209 | if ( $@ =~ /Too many arguments for open/ ) { |
210 | open( INFILE, "< $file" ) |
211 | || die "Cannot open '$file' for reading: $!\n"; |
212 | } else { |
213 | die $@; |
214 | } |
215 | } |
a084e413 |
216 | } |
d0eafc11 |
217 | |
a084e413 |
218 | my $parser = XML::Parser->new( @parms ); |
219 | $parser->parse(do { local $/ = undef; <INFILE> }); |
d0eafc11 |
220 | |
d01e4722 |
221 | close INFILE |
222 | unless ref $file; |
d0eafc11 |
223 | |
8ca5e726 |
224 | return ~~1; |
d0eafc11 |
225 | } |
d01e4722 |
226 | *parse = *parse = \&parse_xml; |
d0eafc11 |
227 | |
228 | sub _prepare_output |
229 | { |
230 | my $self = shift; |
d01e4722 |
231 | return unless $self->{WORKBOOK}; |
232 | |
d0eafc11 |
233 | my ($xls) = @_; |
234 | |
6dd4c89d |
235 | my $context = Excel::Template::Factory->_create( |
d0eafc11 |
236 | 'CONTEXT', |
237 | |
238 | XLS => $xls, |
239 | PARAM_MAP => [ $self->{PARAM_MAP} ], |
8c63e224 |
240 | UNICODE => $self->{UNICODE}, |
d0eafc11 |
241 | ); |
242 | |
d01e4722 |
243 | $self->{WORKBOOK}->render($context); |
d0eafc11 |
244 | |
8ca5e726 |
245 | return ~~1; |
d0eafc11 |
246 | } |
247 | |
e976988f |
248 | sub register { shift; Excel::Template::Factory->register(@_) } |
d0eafc11 |
249 | |
250 | 1; |
251 | __END__ |
252 | |
253 | =head1 NAME |
254 | |
255 | Excel::Template - Excel::Template |
256 | |
257 | =head1 SYNOPSIS |
258 | |
259 | First, make a template. This is an XML file, describing the layout of the |
260 | spreadsheet. |
261 | |
262 | For example, test.xml: |
263 | |
264 | <workbook> |
265 | <worksheet name="tester"> |
e976988f |
266 | <cell text="$HOME" /> |
267 | <cell text="$PATH" /> |
d0eafc11 |
268 | </worksheet> |
269 | </workbook> |
270 | |
271 | Now, create a small program to use it: |
272 | |
273 | #!/usr/bin/perl -w |
e976988f |
274 | |
275 | use strict; |
276 | |
9d172425 |
277 | use Excel::Template; |
d0eafc11 |
278 | |
279 | # Create the Excel template |
280 | my $template = Excel::Template->new( |
281 | filename => 'test.xml', |
282 | ); |
283 | |
284 | # Add a few parameters |
285 | $template->param( |
286 | HOME => $ENV{HOME}, |
287 | PATH => $ENV{PATH}, |
288 | ); |
289 | |
290 | $template->write_file('test.xls'); |
291 | |
e976988f |
292 | If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like: |
d0eafc11 |
293 | |
294 | A B C |
295 | +----------------+----------------+---------------- |
296 | 1 | /home/me | /bin:/usr/bin | |
297 | +----------------+----------------+---------------- |
298 | 2 | | | |
299 | +----------------+----------------+---------------- |
300 | 3 | | | |
301 | |
302 | =head1 DESCRIPTION |
303 | |
e976988f |
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>. |
d0eafc11 |
305 | |
d0eafc11 |
306 | =head1 MOTIVATION |
307 | |
e976988f |
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. |
d0eafc11 |
309 | |
310 | =head1 USAGE |
311 | |
312 | =head2 new() |
313 | |
a084e413 |
314 | This creates a Excel::Template object. |
d0eafc11 |
315 | |
6dd4c89d |
316 | =head3 Parameters |
317 | |
318 | =over 4 |
319 | |
a084e413 |
320 | =item * FILE / FILENAME |
321 | |
322 | Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.) |
323 | |
324 | If you want to use the __DATA__ section, you can do so by passing |
325 | |
326 | FILE => \*DATA |
327 | |
6dd4c89d |
328 | =item * RENDERER |
329 | |
e976988f |
330 | The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are: |
6dd4c89d |
331 | |
332 | =over 4 |
333 | |
334 | =item * Excel::Template->RENDER_NML |
335 | |
e976988f |
336 | This is the default of L<Spreadsheet::WriteExcel>. |
8c63e224 |
337 | |
6dd4c89d |
338 | =item * Excel::Template->RENDER_BIG |
339 | |
e976988f |
340 | This attempts to load L<Spreadsheet::WriteExcel::Big>. |
6dd4c89d |
341 | |
342 | =item * Excel::Template->RENDER_XML |
343 | |
e976988f |
344 | This attempts to load L<Spreadsheet::WriteExcelXML>. |
6dd4c89d |
345 | |
346 | =back |
347 | |
348 | =item * USE_UNICODE |
349 | |
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. |
8c63e224 |
351 | |
e976988f |
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. |
8ca5e726 |
353 | |
e976988f |
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. |
8ca5e726 |
355 | |
6dd4c89d |
356 | =back |
357 | |
358 | =head3 Deprecated |
359 | |
360 | =over 4 |
361 | |
362 | =item * BIG_FILE |
363 | |
364 | Instead, use RENDERER => Excel::Template->RENDER_BIG |
365 | |
366 | =back |
367 | |
d0eafc11 |
368 | =head2 param() |
369 | |
8ca5e726 |
370 | This method is exactly like L<HTML::Template>'s param() method. |
d0eafc11 |
371 | |
372 | =head2 parse() / parse_xml() |
373 | |
a084e413 |
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. |
375 | |
e976988f |
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. |
d0eafc11 |
377 | |
378 | =head2 write_file() |
379 | |
e976988f |
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.) |
d0eafc11 |
381 | |
382 | =head2 output() |
383 | |
e976988f |
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.) |
d0eafc11 |
385 | |
6dd4c89d |
386 | =head2 register() |
387 | |
388 | This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info. |
389 | |
d0eafc11 |
390 | =head1 SUPPORTED NODES |
391 | |
e976988f |
392 | This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like. |
d0eafc11 |
393 | |
e976988f |
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. |
d0eafc11 |
395 | |
396 | =over 4 |
397 | |
8ca5e726 |
398 | =item * L<WORKBOOK|Excel::Template::Container::Workbook> |
399 | |
e976988f |
400 | This is the node representing the workbook. It is the parent for all other nodes. |
8ca5e726 |
401 | |
402 | =item * L<WORKSHEET|Excel::Template::Container::Worksheet> |
403 | |
404 | This is the node representing a given worksheet. |
405 | |
406 | =item * L<IF|Excel::Template::Container::Conditional> |
407 | |
e976988f |
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. |
8ca5e726 |
409 | |
410 | =item * L<LOOP|Excel::Template::Container::Loop> |
411 | |
412 | This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP. |
413 | |
414 | =item * L<ROW|Excel::Template::Container::Row> |
415 | |
9ee3aea0 |
416 | This node represents a row of data. This is the 1 in A1. There is no COLUMN node, as of yet. |
8ca5e726 |
417 | |
418 | =item * L<FORMAT|Excel::Template::Container::Format> |
419 | |
e976988f |
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>. |
8ca5e726 |
421 | |
422 | =item * L<BACKREF|Excel::Template::Element::Backref> |
423 | |
424 | This refers back to a cell previously named. |
d0eafc11 |
425 | |
8ca5e726 |
426 | =item * L<CELL|Excel::Template::Element::Cell> |
d0eafc11 |
427 | |
8ca5e726 |
428 | This is the actual cell in a spreadsheet. |
d0eafc11 |
429 | |
8ca5e726 |
430 | =item * L<FORMULA|Excel::Template::Element::Formula> |
d0eafc11 |
431 | |
8ca5e726 |
432 | This is a formula in a spreadsheet. |
d0eafc11 |
433 | |
8ca5e726 |
434 | =item * L<RANGE|Excel::Template::Element::Range> |
d0eafc11 |
435 | |
8ca5e726 |
436 | This is a BACKREF for a number of identically-named cells. |
d0eafc11 |
437 | |
8ca5e726 |
438 | =item * L<VAR|Excel::Template::Element::Var> |
d0eafc11 |
439 | |
8ca5e726 |
440 | This is a variable. It is generally used when the 'text' attribute isn't |
441 | sufficient. |
d0eafc11 |
442 | |
6dd4c89d |
443 | =back |
d0eafc11 |
444 | |
445 | =head1 BUGS |
446 | |
447 | None, that I know of. |
448 | |
449 | =head1 SUPPORT |
450 | |
e976988f |
451 | This is production quality software, used in several production web applications. |
d0eafc11 |
452 | |
453 | =head1 AUTHOR |
454 | |
8ca5e726 |
455 | Rob Kinyon (rob.kinyon@gmail.com) |
a8441e01 |
456 | |
457 | =head1 CONTRIBUTORS |
458 | |
c11fa570 |
459 | There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com |
d0eafc11 |
460 | |
c11fa570 |
461 | =head2 Robert Graff |
37513eae |
462 | |
463 | =over 4 |
464 | |
465 | =item * Finishing formats |
466 | |
467 | =item * Fixing several bugs in worksheet naming |
468 | |
6dd4c89d |
469 | =back |
37513eae |
470 | |
c11fa570 |
471 | =head1 TEST COVERAGE |
472 | |
0671c67d |
473 | I use L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers. |
c11fa570 |
474 | |
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. |
476 | |
9ee3aea0 |
477 | ---------------------------- ------ ------ ------ ------ ------ ------ ------ |
0671c67d |
478 | File stmt bran cond sub pod time total |
9ee3aea0 |
479 | ---------------------------- ------ ------ ------ ------ ------ ------ ------ |
82112547 |
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 |
1aa9e458 |
483 | ...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0 |
82112547 |
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 |
0671c67d |
489 | ...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0 |
82112547 |
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 |
0671c67d |
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 |
82112547 |
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 |
1aa9e458 |
500 | ...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.1 87.1 |
82112547 |
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 |
0671c67d |
503 | ...te/Element/FreezePanes.pm 100.0 n/a n/a 100.0 0.0 0.0 95.5 |
82112547 |
504 | ...Template/Element/Image.pm 100.0 100.0 n/a 100.0 0.0 0.0 94.3 |
0671c67d |
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 |
82112547 |
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 |
9ee3aea0 |
512 | ---------------------------- ------ ------ ------ ------ ------ ------ ------ |
c11fa570 |
513 | |
d0eafc11 |
514 | =head1 COPYRIGHT |
515 | |
e976988f |
516 | This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. |
d0eafc11 |
517 | |
e976988f |
518 | The full text of the license can be found in the LICENSE file included with this module. |
d0eafc11 |
519 | |
520 | =head1 SEE ALSO |
521 | |
e976988f |
522 | perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel> |
d0eafc11 |
523 | |
524 | =cut |