[p5sagit/Excel-Template.git] / lib / Excel / Template.pm

package Excel::Template;

use strict;

BEGIN {
    use Excel::Template::Base;
    use vars qw ($VERSION @ISA);

    $VERSION  = '0.23';
    @ISA      = qw( Excel::Template::Base );
}

use File::Basename;
use XML::Parser;
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->{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->{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';
    }

    $self->{RENDERER} = undef;
    foreach my $class (@renderer_classes)
    {
        (my $filename = $class) =~ s!::!/!g;
        eval {
            require "$filename.pm";
            $class->import;
        };
        if ($@) {
            warn "Could not find or compile '$class'\n" if $^W;
        } else {
            $self->{RENDERER} = $class;
            last;
        }
    }

    defined $self->{RENDERER} ||
        die "Could not find a renderer class. Tried:\n\t" .
            join("\n\t", @renderer_classes) .
            "\n";

    $self->{USE_UNICODE} = ~~0
        if $] >= 5.008;

    return $self;
}

sub param
{
    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');

    (@_ % 2)
        && die __PACKAGE__, "->param() : Odd number of parameters to param()\n";

    my %params = @_;
    $params{uc $_} = delete $params{$_} for keys %params;
    @{$self->{PARAM_MAP}}{keys %params} = @params{keys %params};

    return ~~1;
}

sub write_file
{
    my $self = shift;
    my ($filename) = @_;

    my $xls = $self->{RENDERER}->new($filename)
        || die "Cannot create XLS in '$filename': $!\n";

    $self->_prepare_output($xls);

    $xls->close;

    return ~~1;
}

sub output
{
    my $self = shift;

    my $output;
    tie *XLS, 'IO::Scalar', \$output;

    $self->write_file(\*XLS);

    return $output;
}

sub parse_xml
{
    my $self = shift;
    my ($file) = @_;

    my @stack;
    my @parms = (
        Handlers => {
            Start => sub {
                shift;

                my $name = uc shift;

                my $node = Excel::Template::Factory->_create_node($name, @_);
                die "'$name' (@_) didn't make a node!\n" unless defined $node;

                if ( $node->isa( 'WORKBOOK' ) )
                {
                    push @{$self->{WORKBOOKS}}, $node;
                }
                elsif ( $node->is_embedded )
                {
                    return unless @stack;
                                                                                
                    if (exists $stack[-1]{TXTOBJ} &&
                        $stack[-1]{TXTOBJ}->isa('TEXTOBJECT'))
                    {
                        push @{$stack[-1]{TXTOBJ}{STACK}}, $node;
                    }
 
                }
                else
                {
                    push @{$stack[-1]{ELEMENTS}}, $node
                        if @stack;
                }
                push @stack, $node;
            },
            Char => sub {
                shift;
                return unless @stack;

                my $parent = $stack[-1];

                if (
                    exists $parent->{TXTOBJ}
                        &&
                    $parent->{TXTOBJ}->isa('TEXTOBJECT')
                ) {
                    push @{$parent->{TXTOBJ}{STACK}}, @_;
                }
            },
            End => sub {
                shift;
                return unless @stack;

                pop @stack if $stack[-1]->isa(uc $_[0]);
            },
        },
    );

    if ( ref $file )
    {
        *INFILE = $file;
    }
    else
    {
        my ($filename, $dirname) = fileparse($file);
 
        push @parms, Base => $dirname;

        open( INFILE, "<$file" )
            || die "Cannot open '$file' for reading: $!\n";

    }

    my $parser = XML::Parser->new( @parms );
    $parser->parse(do { local $/ = undef; <INFILE> });

    unless ( ref $file )
    {
        close INFILE;
    }

    return ~~1;
}
*parse = \&parse_xml;

sub _prepare_output
{
    my $self = shift;
    my ($xls) = @_;

    my $context = Excel::Template::Factory->_create(
        'CONTEXT',

        XLS       => $xls,
        PARAM_MAP => [ $self->{PARAM_MAP} ],
        UNICODE   => $self->{UNICODE},
    );

    $_->render($context) for @{$self->{WORKBOOKS}};

    return ~~1;
}

sub register { shift; Excel::Template::Factory->register(@_) }

1;
__END__

=head1 NAME

Excel::Template - Excel::Template

=head1 SYNOPSIS

First, make a template. This is an XML file, describing the layout of the
spreadsheet.

For example, test.xml:

  <workbook>
      <worksheet name="tester">
          <cell text="$HOME" />
          <cell text="$PATH" />
      </worksheet>
  </workbook>

Now, create a small program to use it:

  #!/usr/bin/perl -w

  use strict;

  use Excel::Template;

  # Create the Excel template
  my $template = Excel::Template->new(
      filename => 'test.xml',
  );

  # Add a few parameters
  $template->param(
      HOME => $ENV{HOME},
      PATH => $ENV{PATH},
  );

  $template->write_file('test.xls');

If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like:

             A                B                C
    +----------------+----------------+----------------
  1 | /home/me       | /bin:/usr/bin  |
    +----------------+----------------+----------------
  2 |                |                |
    +----------------+----------------+----------------
  3 |                |                |

=head1 DESCRIPTION

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. 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.

=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 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.

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.)

=head2 output()

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 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-type tag will write into.

=over 4

=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>

This node represents a row of data. This is the A in A1.

=item * L<FORMAT|Excel::Template::Container::Format>

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 * L<BACKREF|Excel::Template::Element::Backref>

This refers back to a cell previously named.

=item * L<CELL|Excel::Template::Element::Cell>

This is the actual cell in a spreadsheet.

=item * L<FORMULA|Excel::Template::Element::Formula>

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

None, that I know of.

=head1 SUPPORT

This is production quality software, used in several production web applications.

=head1 AUTHOR

    Rob Kinyon (rob.kinyon@gmail.com)

=head1 CONTRIBUTORS

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 used 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 brnch  cond   sub   pod  time total
  -----------------------------------------------------------------------
  blib/lib/Excel/Template.pm     96.0  62.5  58.8 100.0 100.0  25.2  86.2
  ...ib/Excel/Template/Base.pm   87.0  50.0  66.7  81.8  87.5   8.7  83.0
  ...cel/Template/Container.pm   71.4  50.0  33.3  70.0  80.0   4.9  68.4
  ...emplate/Container/Bold.pm  100.0   n/a   n/a 100.0   0.0   0.7  95.0
  .../Container/Conditional.pm   64.9  57.5  66.7 100.0   0.0   0.7  63.9
  ...plate/Container/Format.pm  100.0   n/a   n/a 100.0   0.0   0.7  96.6
  ...plate/Container/Hidden.pm  100.0   n/a   n/a 100.0   0.0   0.1  95.0
  ...plate/Container/Italic.pm  100.0   n/a   n/a 100.0   0.0   0.1  95.0
  ...plate/Container/Locked.pm  100.0   n/a   n/a 100.0   0.0   0.1  95.0
  ...emplate/Container/Loop.pm   90.9  50.0  50.0 100.0  50.0   0.5  80.4
  ...late/Container/Outline.pm  100.0   n/a   n/a 100.0   0.0   0.1  95.0
  ...Template/Container/Row.pm  100.0  75.0   n/a 100.0  50.0   0.3  93.8
  ...mplate/Container/Scope.pm  100.0   n/a   n/a 100.0   n/a   0.1 100.0
  ...plate/Container/Shadow.pm  100.0   n/a   n/a 100.0   0.0   0.1  95.0
  ...te/Container/Strikeout.pm  100.0   n/a   n/a 100.0   0.0   0.1  95.0
  ...ate/Container/Workbook.pm  100.0   n/a   n/a 100.0   n/a   0.9 100.0
  ...te/Container/Worksheet.pm   94.1  50.0   n/a 100.0   0.0   0.9  88.0
  ...Excel/Template/Context.pm   84.3  53.4  54.2 100.0  92.3  19.5  76.0
  ...Excel/Template/Element.pm  100.0   n/a   n/a 100.0   n/a   0.5 100.0
  ...mplate/Element/Backref.pm  100.0  50.0  33.3 100.0   0.0   0.1  87.1
  .../Template/Element/Cell.pm   95.8  65.0  80.0 100.0  66.7   3.8  86.9
  ...mplate/Element/Formula.pm  100.0   n/a   n/a 100.0   0.0   0.3  94.1
  ...Template/Element/Range.pm  100.0  66.7   n/a 100.0  66.7   0.2  93.3
  ...l/Template/Element/Var.pm  100.0   n/a   n/a 100.0   0.0   0.1  94.1
  ...Excel/Template/Factory.pm  100.0  73.1   n/a 100.0 100.0  16.3  92.6
  .../Excel/Template/Format.pm   98.3  81.2  33.3 100.0 100.0  10.0  93.2
  ...xcel/Template/Iterator.pm   98.6  80.0  70.6 100.0  83.3   1.9  90.3
  ...el/Template/TextObject.pm   92.9  62.5  33.3 100.0  50.0   3.1  83.0
  Total                          92.0  63.5  58.3  97.5  98.5 100.0  86.0
  -----------------------------------------------------------------------

=head1 COPYRIGHT

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.

=head1 SEE ALSO

perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel>

=cut
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
e976988f	9	$VERSION = '0.23';
d0eafc11	10	@ISA = qw( Excel::Template::Base );
	11	}
	12
	13	use File::Basename;
	14	use XML::Parser;
d0eafc11	15	use IO::Scalar;
d0eafc11	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
d0eafc11	38	my @renderer_classes = ( 'Spreadsheet::WriteExcel' );
6dd4c89d	39
6dd4c89d	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";
6dd4c89d	54	unshift @renderer_classes, 'Spreadsheet::WriteExcel::Big';
9d172425	55	}
9d172425	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.
	89	push @_, %{shift @_} while UNIVERSAL::isa($_[0], '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
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
	109	$self->_prepare_output($xls);
	110
	111	$xls->close;
	112
8c63e224	113	return ~~1;
d0eafc11	114	}
	115
	116	sub output
	117	{
	118	my $self = shift;
	119
	120	my $output;
	121	tie *XLS, 'IO::Scalar', \$output;
	122
	123	$self->write_file(\*XLS);
	124
	125	return $output;
	126	}
	127
d0eafc11	128	sub parse_xml
	129	{
	130	my $self = shift;
a084e413	131	my ($file) = @_;
d0eafc11	132
d0eafc11	133	my @stack;
a084e413	134	my @parms = (
d0eafc11	135	Handlers => {
	136	Start => sub {
	137	shift;
	138
	139	my $name = uc shift;
	140
6dd4c89d	141	my $node = Excel::Template::Factory->_create_node($name, @_);
d0eafc11	142	die "'$name' (@_) didn't make a node!\n" unless defined $node;
d0eafc11	143
37513eae	144	if ( $node->isa( 'WORKBOOK' ) )
d0eafc11	145	{
	146	push @{$self->{WORKBOOKS}}, $node;
	147	}
37513eae	148	elsif ( $node->is_embedded )
d0eafc11	149	{
	150	return unless @stack;
	151
	152	if (exists $stack[-1]{TXTOBJ} &&
	153	$stack[-1]{TXTOBJ}->isa('TEXTOBJECT'))
	154	{
	155	push @{$stack[-1]{TXTOBJ}{STACK}}, $node;
	156	}
	157
	158	}
	159	else
	160	{
	161	push @{$stack[-1]{ELEMENTS}}, $node
	162	if @stack;
	163	}
	164	push @stack, $node;
	165	},
	166	Char => sub {
	167	shift;
	168	return unless @stack;
	169
	170	my $parent = $stack[-1];
	171
	172	if (
	173	exists $parent->{TXTOBJ}
	174	&&
	175	$parent->{TXTOBJ}->isa('TEXTOBJECT')
	176	) {
	177	push @{$parent->{TXTOBJ}{STACK}}, @_;
	178	}
	179	},
	180	End => sub {
	181	shift;
	182	return unless @stack;
	183
	184	pop @stack if $stack[-1]->isa(uc $_[0]);
	185	},
	186	},
	187	);
	188
a084e413	189	if ( ref $file )
	190	{
	191	*INFILE = $file;
	192	}
	193	else
d0eafc11	194	{
a084e413	195	my ($filename, $dirname) = fileparse($file);
	196
	197	push @parms, Base => $dirname;
	198
	199	open( INFILE, "<$file" )
	200	\|\| die "Cannot open '$file' for reading: $!\n";
	201
	202	}
d0eafc11	203
a084e413	204	my $parser = XML::Parser->new( @parms );
a084e413	205	$parser->parse(do { local $/ = undef; <INFILE> });
d0eafc11	206
a084e413	207	unless ( ref $file )
a084e413	208	{
b6bc5a5d	209	close INFILE;
d0eafc11	210	}
d0eafc11	211
8ca5e726	212	return ~~1;
d0eafc11	213	}
b6bc5a5d	214	*parse = \&parse_xml;
d0eafc11	215
	216	sub _prepare_output
	217	{
	218	my $self = shift;
	219	my ($xls) = @_;
	220
6dd4c89d	221	my $context = Excel::Template::Factory->_create(
d0eafc11	222	'CONTEXT',
	223
	224	XLS => $xls,
	225	PARAM_MAP => [ $self->{PARAM_MAP} ],
8c63e224	226	UNICODE => $self->{UNICODE},
d0eafc11	227	);
	228
	229	$_->render($context) for @{$self->{WORKBOOKS}};
	230
8ca5e726	231	return ~~1;
d0eafc11	232	}
d0eafc11	233
e976988f	234	sub register { shift; Excel::Template::Factory->register(@_) }
d0eafc11	235
	236	1;
	237	__END__
	238
	239	=head1 NAME
	240
	241	Excel::Template - Excel::Template
	242
	243	=head1 SYNOPSIS
	244
	245	First, make a template. This is an XML file, describing the layout of the
	246	spreadsheet.
	247
	248	For example, test.xml:
	249
	250	<workbook>
	251	<worksheet name="tester">
e976988f	252	<cell text="$HOME" />
e976988f	253	<cell text="$PATH" />
d0eafc11	254	</worksheet>
	255	</workbook>
	256
	257	Now, create a small program to use it:
	258
	259	#!/usr/bin/perl -w
e976988f	260
	261	use strict;
	262
9d172425	263	use Excel::Template;
d0eafc11	264
	265	# Create the Excel template
	266	my $template = Excel::Template->new(
	267	filename => 'test.xml',
	268	);
	269
	270	# Add a few parameters
	271	$template->param(
	272	HOME => $ENV{HOME},
	273	PATH => $ENV{PATH},
	274	);
	275
	276	$template->write_file('test.xls');
	277
e976988f	278	If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like:
d0eafc11	279
	280	A B C
	281	+----------------+----------------+----------------
	282	1 \| /home/me \| /bin:/usr/bin \|
	283	+----------------+----------------+----------------
	284	2 \| \| \|
	285	+----------------+----------------+----------------
	286	3 \| \| \|
	287
	288	=head1 DESCRIPTION
	289
e976988f	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>.
d0eafc11	291
d0eafc11	292	=head1 MOTIVATION
d0eafc11	293
e976988f	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.
d0eafc11	295
	296	=head1 USAGE
	297
	298	=head2 new()
	299
a084e413	300	This creates a Excel::Template object.
d0eafc11	301
6dd4c89d	302	=head3 Parameters
	303
	304	=over 4
	305
a084e413	306	=item * FILE / FILENAME
	307
	308	Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.)
	309
	310	If you want to use the __DATA__ section, you can do so by passing
	311
	312	FILE => \*DATA
	313
6dd4c89d	314	=item * RENDERER
6dd4c89d	315
e976988f	316	The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are:
6dd4c89d	317
	318	=over 4
	319
	320	=item * Excel::Template->RENDER_NML
	321
e976988f	322	This is the default of L<Spreadsheet::WriteExcel>.
8c63e224	323
6dd4c89d	324	=item * Excel::Template->RENDER_BIG
6dd4c89d	325
e976988f	326	This attempts to load L<Spreadsheet::WriteExcel::Big>.
6dd4c89d	327
	328	=item * Excel::Template->RENDER_XML
	329
e976988f	330	This attempts to load L<Spreadsheet::WriteExcelXML>.
6dd4c89d	331
	332	=back
	333
	334	=item * USE_UNICODE
	335
	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.
8c63e224	337
e976988f	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.
8ca5e726	339
e976988f	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.
8ca5e726	341
6dd4c89d	342	=back
	343
	344	=head3 Deprecated
	345
	346	=over 4
	347
	348	=item * BIG_FILE
	349
	350	Instead, use RENDERER => Excel::Template->RENDER_BIG
	351
	352	=back
	353
d0eafc11	354	=head2 param()
d0eafc11	355
8ca5e726	356	This method is exactly like L<HTML::Template>'s param() method.
d0eafc11	357
	358	=head2 parse() / parse_xml()
	359
a084e413	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.
a084e413	361
e976988f	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.
d0eafc11	363
	364	=head2 write_file()
	365
e976988f	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.)
d0eafc11	367
	368	=head2 output()
	369
e976988f	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.)
d0eafc11	371
6dd4c89d	372	=head2 register()
	373
	374	This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info.
	375
d0eafc11	376	=head1 SUPPORTED NODES
d0eafc11	377
e976988f	378	This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like.
d0eafc11	379
e976988f	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.
d0eafc11	381
	382	=over 4
	383
8ca5e726	384	=item * L<WORKBOOK\|Excel::Template::Container::Workbook>
8ca5e726	385
e976988f	386	This is the node representing the workbook. It is the parent for all other nodes.
8ca5e726	387
	388	=item * L<WORKSHEET\|Excel::Template::Container::Worksheet>
	389
	390	This is the node representing a given worksheet.
	391
	392	=item * L<IF\|Excel::Template::Container::Conditional>
	393
e976988f	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.
8ca5e726	395
	396	=item * L<LOOP\|Excel::Template::Container::Loop>
	397
	398	This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
	399
	400	=item * L<ROW\|Excel::Template::Container::Row>
	401
	402	This node represents a row of data. This is the A in A1.
	403
	404	=item * L<FORMAT\|Excel::Template::Container::Format>
	405
e976988f	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>.
8ca5e726	407
	408	=item * L<BACKREF\|Excel::Template::Element::Backref>
	409
	410	This refers back to a cell previously named.
d0eafc11	411
8ca5e726	412	=item * L<CELL\|Excel::Template::Element::Cell>
d0eafc11	413
8ca5e726	414	This is the actual cell in a spreadsheet.
d0eafc11	415
8ca5e726	416	=item * L<FORMULA\|Excel::Template::Element::Formula>
d0eafc11	417
8ca5e726	418	This is a formula in a spreadsheet.
d0eafc11	419
8ca5e726	420	=item * L<RANGE\|Excel::Template::Element::Range>
d0eafc11	421
8ca5e726	422	This is a BACKREF for a number of identically-named cells.
d0eafc11	423
8ca5e726	424	=item * L<VAR\|Excel::Template::Element::Var>
d0eafc11	425
8ca5e726	426	This is a variable. It is generally used when the 'text' attribute isn't
8ca5e726	427	sufficient.
d0eafc11	428
6dd4c89d	429	=back
d0eafc11	430
	431	=head1 BUGS
	432
	433	None, that I know of.
	434
	435	=head1 SUPPORT
	436
e976988f	437	This is production quality software, used in several production web applications.
d0eafc11	438
	439	=head1 AUTHOR
	440
8ca5e726	441	Rob Kinyon (rob.kinyon@gmail.com)
a8441e01	442
	443	=head1 CONTRIBUTORS
	444
c11fa570	445	There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com
d0eafc11	446
c11fa570	447	=head2 Robert Graff
37513eae	448
	449	=over 4
	450
	451	=item * Finishing formats
	452
	453	=item * Fixing several bugs in worksheet naming
	454
6dd4c89d	455	=back
37513eae	456
c11fa570	457	=head1 TEST COVERAGE
c11fa570	458
e976988f	459	I used L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers.
c11fa570	460
	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.
	462
e976988f	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	-----------------------------------------------------------------------
c11fa570	496
d0eafc11	497	=head1 COPYRIGHT
d0eafc11	498
e976988f	499	This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
d0eafc11	500
e976988f	501	The full text of the license can be found in the LICENSE file included with this module.
d0eafc11	502
	503	=head1 SEE ALSO
	504
e976988f	505	perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel>
d0eafc11	506
d0eafc11	507	=cut