1package Excel::Template; 2 3use strict; 4 5BEGIN { 6 use Excel::Template::Base; 7 use vars qw ($VERSION @ISA); 8 9 $VERSION = '0.34'; 10 @ISA = qw( Excel::Template::Base ); 11} 12 13use File::Basename; 14use XML::Parser; 15use IO::Scalar; 16 17use constant RENDER_NML => 'normal'; 18use constant RENDER_BIG => 'big'; 19use constant RENDER_XML => 'xml'; 20 21my %renderers = ( 22 RENDER_NML, 'Spreadsheet::WriteExcel', 23 RENDER_BIG, 'Spreadsheet::WriteExcel::Big', 24 RENDER_XML, 'Spreadsheet::WriteExcelXML', 25); 26 27sub new 28{ 29 my $class = shift; 30 my $self = $class->SUPER::new(@_); 31 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}; 37 38 my @renderer_classes = ( 'Spreadsheet::WriteExcel' ); 39 40 if (exists $self->{RENDERER} && $self->{RENDERER}) 41 { 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 } 50 } 51 elsif (exists $self->{BIG_FILE} && $self->{BIG_FILE}) 52 { 53 warn "Use of BIG_FILE is deprecated.\n"; 54 unshift @renderer_classes, 'Spreadsheet::WriteExcel::Big'; 55 } 56 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 ($@) { 66 warn "Could not find or compile '$class'\n" if $^W; 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 78 $self->{USE_UNICODE} = ~~0 79 if $] >= 5.008; 80 81 return $self; 82} 83 84sub 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. 89 push @_, %{shift @_} while ref $_[0] eq 'HASH'; 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 98 return ~~1; 99} 100 101sub 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 109 eval { 110 $self->_prepare_output($xls); 111 }; 112print $@ if $@; 113 114 $xls->close; 115 116 return if $@; 117 118 return ~~1; 119} 120 121sub output 122{ 123 my $self = shift; 124 125 my $output; 126 tie *XLS, 'IO::Scalar', \$output; 127 128 $self->write_file(\*XLS) 129 or return; 130 131 return $output; 132} 133 134sub parse_xml 135{ 136 my $self = shift; 137 my ($file) = @_; 138 139 my @stack; 140 my @parms = ( 141 Handlers => { 142 Start => sub { 143 shift; 144 145 my $name = uc shift; 146 147 my $node = Excel::Template::Factory->_create_node($name, @_); 148 die "'$name' (@_) didn't make a node!\n" unless defined $node; 149 150 if ( $node->isa( 'WORKBOOK' ) ) 151 { 152 $self->{WORKBOOK} = $node; 153 } 154 elsif ( $node->is_embedded ) 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 195 if ( ref $file ) 196 { 197 *INFILE = $file; 198 } 199 else 200 { 201 my ($filename, $dirname) = fileparse($file); 202 203 push @parms, Base => $dirname; 204 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 } 216 } 217 218 my $parser = XML::Parser->new( @parms ); 219 $parser->parse(do { local $/ = undef; <INFILE> }); 220 221 close INFILE 222 unless ref $file; 223 224 return ~~1; 225} 226*parse = *parse = \&parse_xml; 227 228sub _prepare_output 229{ 230 my $self = shift; 231 return unless $self->{WORKBOOK}; 232 233 my ($xls) = @_; 234 235 my $context = Excel::Template::Factory->_create( 236 'CONTEXT', 237 238 XLS => $xls, 239 PARAM_MAP => [ $self->{PARAM_MAP} ], 240 UNICODE => $self->{UNICODE}, 241 ); 242 243 $self->{WORKBOOK}->render($context); 244 245 return ~~1; 246} 247 248sub register { shift; Excel::Template::Factory->register(@_) } 249 2501; 251__END__ 252 253=head1 NAME 254 255Excel::Template - Excel::Template 256 257=head1 SYNOPSIS 258 259First, make a template. This is an XML file, describing the layout of the 260spreadsheet. 261 262For example, test.xml: 263 264 <workbook> 265 <worksheet name="tester"> 266 <cell text="$HOME" /> 267 <cell text="$PATH" /> 268 </worksheet> 269 </workbook> 270 271Now, create a small program to use it: 272 273 #!/usr/bin/perl -w 274 275 use strict; 276 277 use Excel::Template; 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 292If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like: 293 294 A B C 295 +----------------+----------------+---------------- 296 1 | /home/me | /bin:/usr/bin | 297 +----------------+----------------+---------------- 298 2 | | | 299 +----------------+----------------+---------------- 300 3 | | | 301 302=head1 DESCRIPTION 303 304This 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>. 305 306=head1 MOTIVATION 307 308I 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. 309 310=head1 USAGE 311 312=head2 new() 313 314This creates a Excel::Template object. 315 316=head3 Parameters 317 318=over 4 319 320=item * FILE / FILENAME 321 322Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.) 323 324If you want to use the __DATA__ section, you can do so by passing 325 326 FILE => \*DATA 327 328=item * RENDERER 329 330The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are: 331 332=over 4 333 334=item * Excel::Template->RENDER_NML 335 336This is the default of L<Spreadsheet::WriteExcel>. 337 338=item * Excel::Template->RENDER_BIG 339 340This attempts to load L<Spreadsheet::WriteExcel::Big>. 341 342=item * Excel::Template->RENDER_XML 343 344This attempts to load L<Spreadsheet::WriteExcelXML>. 345 346=back 347 348=item * USE_UNICODE 349 350This 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. 351 352The USE_UNICODE parameter will be ignored if you are using Perl 5.8 or higher as Perl's internal string handling is unicode-aware. 353 354NOTE: 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. 355 356=back 357 358=head3 Deprecated 359 360=over 4 361 362=item * BIG_FILE 363 364Instead, use RENDERER => Excel::Template->RENDER_BIG 365 366=back 367 368=head2 param() 369 370This method is exactly like L<HTML::Template>'s param() method. 371 372=head2 parse() / parse_xml() 373 374This 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 376If 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. 377 378=head2 write_file() 379 380Create 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.) 381 382=head2 output() 383 384It 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.) 385 386=head2 register() 387 388This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info. 389 390=head1 SUPPORTED NODES 391 392This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like. 393 394Every node can set the ROW and COL parameters. These are the actual ROW/COL values that the next CELL-type tag will write into. 395 396=over 4 397 398=item * L<WORKBOOK|Excel::Template::Container::Workbook> 399 400This is the node representing the workbook. It is the parent for all other nodes. 401 402=item * L<WORKSHEET|Excel::Template::Container::Worksheet> 403 404This is the node representing a given worksheet. 405 406=item * L<IF|Excel::Template::Container::Conditional> 407 408This node represents a conditional expression. Its children may or may not be rendered. It behaves just like L<HTML::Template>'s TMPL_IF. 409 410=item * L<LOOP|Excel::Template::Container::Loop> 411 412This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP. 413 414=item * L<ROW|Excel::Template::Container::Row> 415 416This node represents a row of data. This is the 1 in A1. There is no COLUMN node, as of yet. 417 418=item * L<FORMAT|Excel::Template::Container::Format> 419 420This 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>. 421 422=item * L<BACKREF|Excel::Template::Element::Backref> 423 424This refers back to a cell previously named. 425 426=item * L<CELL|Excel::Template::Element::Cell> 427 428This is the actual cell in a spreadsheet. 429 430=item * L<FORMULA|Excel::Template::Element::Formula> 431 432This is a formula in a spreadsheet. 433 434=item * L<RANGE|Excel::Template::Element::Range> 435 436This is a BACKREF for a number of identically-named cells. 437 438=item * L<VAR|Excel::Template::Element::Var> 439 440This is a variable. It is generally used when the 'text' attribute isn't 441sufficient. 442 443=back 444 445=head1 BUGS 446 447None, that I know of. 448 449=head1 SUPPORT 450 451This is production quality software, used in several production web applications. 452 453=head1 MAINTAINERS 454 455 Jens Gassmann <jegade@cpan.org> 456 Robert Bohne <rbo@cpan.org> 457 Rob Kinyon <rkinyon@cpan.org> 458 459=head1 CONTRIBUTORS 460 461There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com 462 463=head2 Robert Bohne <rbo@cpan.org> 464 465=over 4 466 467=item * Swichting to Module::Install 468 469=item * Add autofilter to worksheet 470 471=back 472 473=head2 Robert Graff 474 475=over 4 476 477=item * Finishing formats 478 479=item * Fixing several bugs in worksheet naming 480 481=back 482 483=head2 Jens Gassmann 484 485=over 4 486 487=item * Add hide_gridlines to worksheet 488 489=back 490 491=head1 TEST COVERAGE 492 493I use L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers. 494 495Excel::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. 496 497 ---------------------------- ------ ------ ------ ------ ------ ------ ------ 498 File stmt bran cond sub pod time total 499 ---------------------------- ------ ------ ------ ------ ------ ------ ------ 500 blib/lib/Excel/Template.pm 93.8 60.0 58.8 100.0 100.0 31.8 83.3 501 ...ib/Excel/Template/Base.pm 94.4 50.0 n/a 100.0 0.0 4.4 80.0 502 ...cel/Template/Container.pm 100.0 50.0 33.3 100.0 0.0 2.0 83.3 503 ...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0 504 .../Container/Conditional.pm 95.9 90.0 66.7 100.0 0.0 0.3 91.0 505 ...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 1.5 96.8 506 ...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0 507 ...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0 508 ...ainer/KeepLeadingZeros.pm 100.0 100.0 n/a 100.0 0.0 0.0 96.3 509 ...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0 510 ...emplate/Container/Loop.pm 96.8 50.0 50.0 100.0 0.0 0.1 82.7 511 ...late/Container/Outline.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0 512 ...Template/Container/Row.pm 100.0 75.0 n/a 100.0 0.0 0.1 90.6 513 ...mplate/Container/Scope.pm 100.0 n/a n/a 100.0 n/a 0.0 100.0 514 ...plate/Container/Shadow.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0 515 ...te/Container/Strikeout.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0 516 ...ate/Container/Workbook.pm 100.0 n/a n/a 100.0 n/a 7.0 100.0 517 ...te/Container/Worksheet.pm 95.5 87.5 100.0 100.0 0.0 1.1 90.2 518 ...Excel/Template/Context.pm 98.0 80.0 75.0 100.0 73.3 17.0 90.7 519 ...Excel/Template/Element.pm 100.0 n/a n/a 100.0 n/a 0.1 100.0 520 ...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.1 87.1 521 .../Template/Element/Cell.pm 97.9 75.0 80.0 100.0 0.0 5.6 88.6 522 ...mplate/Element/Formula.pm 100.0 n/a n/a 100.0 0.0 0.0 94.1 523 ...te/Element/FreezePanes.pm 100.0 n/a n/a 100.0 0.0 0.0 95.5 524 ...Template/Element/Image.pm 100.0 100.0 n/a 100.0 0.0 0.0 94.3 525 ...Template/Element/Range.pm 100.0 66.7 n/a 100.0 0.0 0.1 88.9 526 ...l/Template/Element/Var.pm 100.0 n/a n/a 100.0 0.0 0.0 94.1 527 ...Excel/Template/Factory.pm 100.0 73.1 66.7 100.0 100.0 22.3 91.4 528 .../Excel/Template/Format.pm 98.4 75.0 33.3 100.0 66.7 2.6 90.5 529 ...xcel/Template/Iterator.pm 98.6 80.0 70.6 100.0 50.0 0.3 88.8 530 ...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 0.0 3.3 80.9 531 Total 97.8 74.7 64.6 100.0 35.7 100.0 89.4 532 ---------------------------- ------ ------ ------ ------ ------ ------ ------ 533 534=head1 COPYRIGHT 535 536This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. 537 538The full text of the license can be found in the LICENSE file included with this module. 539 540=head1 SEE ALSO 541 542perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel> 543 544=cut 545