use Excel::Template::Base;
use vars qw ($VERSION @ISA);
- $VERSION = '0.12';
+ $VERSION = '0.26';
@ISA = qw( Excel::Template::Base );
}
use File::Basename;
use XML::Parser;
-use IO::File;
use IO::Scalar;
+use constant RENDER_NML => 'normal';
+use constant RENDER_BIG => 'big';
+use constant RENDER_XML => 'xml';
+
+my %renderers = (
+ RENDER_NML, 'Spreadsheet::WriteExcel',
+ RENDER_BIG, 'Spreadsheet::WriteExcel::Big',
+ RENDER_XML, 'Spreadsheet::WriteExcelXML',
+);
+
sub new
{
my $class = shift;
my $self = $class->SUPER::new(@_);
- $self->parse_xml($self->{FILENAME})
- if defined $self->{FILENAME};
+ $self->{FILE} = $self->{FILENAME}
+ if !defined $self->{FILE} && defined $self->{FILENAME};
+
+ $self->parse_xml($self->{FILE})
+ if defined $self->{FILE};
my @renderer_classes = ( 'Spreadsheet::WriteExcel' );
- if (exists $self->{BIG_FILE} && $self->{BIG_FILE})
+
+ if (exists $self->{RENDERER} && $self->{RENDERER})
+ {
+ if (exists $renderers{ lc $self->{RENDERER} })
+ {
+ unshift @renderer_classes, $renderers{ lc $self->{RENDERER} };
+ }
+ elsif ($^W)
+ {
+ warn "'$self->{RENDERER}' is not recognized\n";
+ }
+ }
+ elsif (exists $self->{BIG_FILE} && $self->{BIG_FILE})
{
+ warn "Use of BIG_FILE is deprecated.\n";
unshift @renderer_classes, 'Spreadsheet::WriteExcel::Big';
}
$class->import;
};
if ($@) {
- warn "Could not find or compile '$class'\n";
+ warn "Could not find or compile '$class'\n" if $^W;
} else {
$self->{RENDERER} = $class;
last;
join("\n\t", @renderer_classes) .
"\n";
+ $self->{USE_UNICODE} = ~~0
+ if $] >= 5.008;
+
return $self;
}
my $self = shift;
# Allow an arbitrary number of hashrefs, so long as they're the first things # into param(). Put each one onto the end, de-referenced.
- push @_, %{shift @_} while UNIVERSAL::isa($_[0], 'HASH');
+ push @_, %{shift @_} while ref $_[0] eq 'HASH';
(@_ % 2)
&& die __PACKAGE__, "->param() : Odd number of parameters to param()\n";
$params{uc $_} = delete $params{$_} for keys %params;
@{$self->{PARAM_MAP}}{keys %params} = @params{keys %params};
- return 1;
+ return ~~1;
}
sub write_file
my $xls = $self->{RENDERER}->new($filename)
|| die "Cannot create XLS in '$filename': $!\n";
- $self->_prepare_output($xls);
+ eval {
+ $self->_prepare_output($xls);
+ };
+print $@ if $@;
$xls->close;
- return 1;
+ return if $@;
+
+ return ~~1;
}
sub output
my $output;
tie *XLS, 'IO::Scalar', \$output;
- $self->write_file(\*XLS);
+ $self->write_file(\*XLS)
+ or return;
return $output;
}
-sub parse
-{
- my $self = shift;
-
- $self->parse_xml(@_);
-}
-
sub parse_xml
{
my $self = shift;
- my ($fname) = @_;
+ my ($file) = @_;
- my ($filename, $dirname) = fileparse($fname);
-
my @stack;
- my $parser = XML::Parser->new(
- Base => $dirname,
+ my @parms = (
Handlers => {
Start => sub {
shift;
my $name = uc shift;
- my $node = Excel::Template::Factory->create_node($name, @_);
+ my $node = Excel::Template::Factory->_create_node($name, @_);
die "'$name' (@_) didn't make a node!\n" unless defined $node;
- if ($name eq 'WORKBOOK')
+ if ( $node->isa( 'WORKBOOK' ) )
{
- push @{$self->{WORKBOOKS}}, $node;
+ $self->{WORKBOOK} = $node;
}
- elsif ($name eq 'VAR')
+ elsif ( $node->is_embedded )
{
return unless @stack;
},
);
+ if ( ref $file )
+ {
+ *INFILE = $file;
+ }
+ else
{
- my $fh = IO::File->new($fname)
- || die "Cannot open '$fname' for reading: $!\n";
+ my ($filename, $dirname) = fileparse($file);
+
+ push @parms, Base => $dirname;
+
+ eval q{
+ open( INFILE, '<', $file )
+ || die "Cannot open '$file' for reading: $!\n";
+ }; if ( $@ ) {
+ if ( $@ =~ /Too many arguments for open/ ) {
+ open( INFILE, "< $file" )
+ || die "Cannot open '$file' for reading: $!\n";
+ } else {
+ die $@;
+ }
+ }
+ }
- $parser->parse(do { local $/ = undef; <$fh> });
+ my $parser = XML::Parser->new( @parms );
+ $parser->parse(do { local $/ = undef; <INFILE> });
- $fh->close;
- }
+ close INFILE
+ unless ref $file;
- return 1;
+ return ~~1;
}
+*parse = *parse = \&parse_xml;
sub _prepare_output
{
my $self = shift;
+ return unless $self->{WORKBOOK};
+
my ($xls) = @_;
- my $context = Excel::Template::Factory->create(
+ my $context = Excel::Template::Factory->_create(
'CONTEXT',
XLS => $xls,
PARAM_MAP => [ $self->{PARAM_MAP} ],
+ UNICODE => $self->{UNICODE},
);
- $_->render($context) for @{$self->{WORKBOOKS}};
+ $self->{WORKBOOK}->render($context);
- return 1;
+ return ~~1;
}
-sub register { shift; Excel::Template::Factory::register(@_) }
+sub register { shift; Excel::Template::Factory->register(@_) }
1;
__END__
<workbook>
<worksheet name="tester">
- <cell text="$HOME"/>
- <cell text="$PATH"/>
+ <cell text="$HOME" />
+ <cell text="$PATH" />
</worksheet>
</workbook>
Now, create a small program to use it:
#!/usr/bin/perl -w
- use Excel::Template
+
+ use strict;
+
+ use Excel::Template;
# Create the Excel template
my $template = Excel::Template->new(
$template->write_file('test.xls');
-If everything worked, then you should have a spreadsheet in your work directory
-that looks something like:
+If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like:
A B C
+----------------+----------------+----------------
=head1 DESCRIPTION
-This is a module used for templating Excel files. Its genesis came from the
-need to use the same datastructure as 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 HTML::Template.
-
-Currently, only a small subset of the planned features are supported. This is
-meant to be a test of the waters, to see what features people actually want.
+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>.
=head1 MOTIVATION
-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. HTML::Template provides the first, and
-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.
+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.
=head1 USAGE
=head2 new()
-This creates a Excel::Template object. If passed a filename parameter, it will
-parse the template in the given file. (You can also use the parse() method,
-described below.)
+This creates a Excel::Template object.
+
+=head3 Parameters
+
+=over 4
+
+=item * FILE / FILENAME
+
+Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.)
+
+If you want to use the __DATA__ section, you can do so by passing
+
+ FILE => \*DATA
+
+=item * RENDERER
+
+The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are:
+
+=over 4
+
+=item * Excel::Template->RENDER_NML
+
+This is the default of L<Spreadsheet::WriteExcel>.
+
+=item * Excel::Template->RENDER_BIG
+
+This attempts to load L<Spreadsheet::WriteExcel::Big>.
+
+=item * Excel::Template->RENDER_XML
+
+This attempts to load L<Spreadsheet::WriteExcelXML>.
+
+=back
+
+=item * USE_UNICODE
+
+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.
+
+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.
+
+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.
+
+=back
+
+=head3 Deprecated
+
+=over 4
+
+=item * BIG_FILE
+
+Instead, use RENDERER => Excel::Template->RENDER_BIG
+
+=back
=head2 param()
-This method is exactly like HTML::Template's param() method. Although I will
-be adding more to this section later, please see HTML::Template's description
-for info right now.
+This method is exactly like L<HTML::Template>'s param() method.
=head2 parse() / parse_xml()
-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.
+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.
+
+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.
=head2 write_file()
-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.)
+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.)
=head2 output()
-It will act just like 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.)
+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.)
+
+=head2 register()
+
+This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info.
=head1 SUPPORTED NODES
-This is just a list of nodes. See the other classes in this distro for more
-details on specific parameters and the like.
+This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like.
-Every node can set the ROW and COL parameters. These are the actual ROW/COL
-values that the next CELL tag will write into.
+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.
=over 4
-=item * WORKBOOK
+=item * L<WORKBOOK|Excel::Template::Container::Workbook>
+
+This is the node representing the workbook. It is the parent for all other nodes.
+
+=item * L<WORKSHEET|Excel::Template::Container::Worksheet>
+
+This is the node representing a given worksheet.
+
+=item * L<IF|Excel::Template::Container::Conditional>
+
+This node represents a conditional expression. Its children may or may not be rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
+
+=item * L<LOOP|Excel::Template::Container::Loop>
+
+This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
+
+=item * L<ROW|Excel::Template::Container::Row>
-=item * WORKSHEET
+This node represents a row of data. This is the 1 in A1. There is no COLUMN node, as of yet.
-=item * IF
+=item * L<FORMAT|Excel::Template::Container::Format>
-=item * LOOP
+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>.
-=item * ROW
+=item * L<BACKREF|Excel::Template::Element::Backref>
-=item * CELL
+This refers back to a cell previously named.
-=item * FORMULA
+=item * L<CELL|Excel::Template::Element::Cell>
-=item * BOLD
+This is the actual cell in a spreadsheet.
-=item * ITALIC
+=item * L<FORMULA|Excel::Template::Element::Formula>
-=back 4
+This is a formula in a spreadsheet.
+
+=item * L<RANGE|Excel::Template::Element::Range>
+
+This is a BACKREF for a number of identically-named cells.
+
+=item * L<VAR|Excel::Template::Element::Var>
+
+This is a variable. It is generally used when the 'text' attribute isn't
+sufficient.
+
+=back
=head1 BUGS
=head1 SUPPORT
-This is currently beta-quality software. The featureset is extremely limited,
-but I expect to be adding on to it very soon.
+This is production quality software, used in several production web applications.
=head1 AUTHOR
- Rob Kinyon
- rob.kinyon@gmail.com
+ Rob Kinyon (rob.kinyon@gmail.com)
=head1 CONTRIBUTORS
-There is a mailing list at http://groups-beta.google.com/group/ExcelTemplate
+There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com
+
+=head2 Robert Graff
+
+=over 4
+
+=item * Finishing formats
+
+=item * Fixing several bugs in worksheet naming
+
+=back
+
+=head1 TEST COVERAGE
+
+I use L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers.
+
+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.
+
+ ---------------------------- ------ ------ ------ ------ ------ ------ ------
+ File stmt bran cond sub pod time total
+ ---------------------------- ------ ------ ------ ------ ------ ------ ------
+ blib/lib/Excel/Template.pm 93.8 60.0 58.8 100.0 100.0 24.6 83.3
+ ...ib/Excel/Template/Base.pm 94.4 50.0 n/a 100.0 0.0 7.0 80.0
+ ...cel/Template/Container.pm 100.0 50.0 33.3 100.0 0.0 4.1 83.3
+ ...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.3 95.0
+ .../Container/Conditional.pm 95.9 90.0 66.7 100.0 0.0 1.2 91.0
+ ...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 0.5 96.6
+ ...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
+ ...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
+ ...ainer/KeepLeadingZeros.pm 100.0 100.0 n/a 100.0 0.0 0.1 96.3
+ ...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
+ ...emplate/Container/Loop.pm 96.8 50.0 50.0 100.0 0.0 0.3 82.7
+ ...late/Container/Outline.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
+ ...Template/Container/Row.pm 100.0 75.0 n/a 100.0 0.0 0.2 90.6
+ ...mplate/Container/Scope.pm 100.0 n/a n/a 100.0 n/a 0.0 100.0
+ ...plate/Container/Shadow.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
+ ...te/Container/Strikeout.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
+ ...ate/Container/Workbook.pm 100.0 n/a n/a 100.0 n/a 2.6 100.0
+ ...te/Container/Worksheet.pm 94.7 75.0 n/a 100.0 0.0 1.0 87.1
+ ...Excel/Template/Context.pm 98.0 80.0 75.0 100.0 73.3 21.4 90.7
+ ...Excel/Template/Element.pm 100.0 n/a n/a 100.0 n/a 0.3 100.0
+ ...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.4 87.1
+ .../Template/Element/Cell.pm 97.9 75.0 80.0 100.0 0.0 3.5 88.6
+ ...mplate/Element/Formula.pm 100.0 n/a n/a 100.0 0.0 0.2 94.1
+ ...te/Element/FreezePanes.pm 100.0 n/a n/a 100.0 0.0 0.0 95.5
+ ...Template/Element/Image.pm 100.0 100.0 n/a 100.0 0.0 0.1 94.3
+ ...Template/Element/Range.pm 100.0 66.7 n/a 100.0 0.0 0.1 88.9
+ ...l/Template/Element/Var.pm 100.0 n/a n/a 100.0 0.0 0.0 94.1
+ ...Excel/Template/Factory.pm 100.0 73.1 66.7 100.0 100.0 23.5 91.4
+ .../Excel/Template/Format.pm 98.4 75.0 33.3 100.0 66.7 5.4 90.5
+ ...xcel/Template/Iterator.pm 98.6 80.0 70.6 100.0 50.0 1.1 88.8
+ ...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 0.0 1.8 80.9
+ Total 97.8 74.3 63.5 100.0 35.7 100.0 89.4
+ ---------------------------- ------ ------ ------ ------ ------ ------ ------
=head1 COPYRIGHT
-This program is free software; you can redistribute
-it and/or modify it under the same terms as Perl itself.
+This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
-The full text of the license can be found in the
-LICENSE file included with this module.
+The full text of the license can be found in the LICENSE file included with this module.
=head1 SEE ALSO
-perl(1), HTML::Template, Spreadsheet::WriteExcel.
+perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel>
=cut