[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.27';
    @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 ref $_[0] eq '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";

    eval {
        $self->_prepare_output($xls);
    };
print $@ if $@;

    $xls->close;

    return if $@;

    return ~~1;
}

sub output
{
    my $self = shift;

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

    $self->write_file(\*XLS)
        or return;

    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' ) )
                {
                    $self->{WORKBOOK} = $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;

        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 $@;
            }
        }
    }

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

    close INFILE
        unless ref $file;

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

sub _prepare_output
{
    my $self = shift;
    return unless $self->{WORKBOOK};

    my ($xls) = @_;

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

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

    $self->{WORKBOOK}->render($context);

    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 1 in A1. There is no COLUMN node, as of yet.

=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 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   15.8   83.3
  ...ib/Excel/Template/Base.pm   94.4   50.0    n/a  100.0    0.0    6.7   80.0
  ...cel/Template/Container.pm  100.0   50.0   33.3  100.0    0.0    5.7   83.3
  ...emplate/Container/Bold.pm  100.0    n/a    n/a  100.0    0.0    0.1   95.0
  .../Container/Conditional.pm   95.9   90.0   66.7  100.0    0.0    2.0   91.0
  ...plate/Container/Format.pm  100.0    n/a    n/a  100.0    0.0    0.8   96.8
  ...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
  ...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.5   82.7
  ...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    0.0    0.3   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    0.6  100.0
  ...te/Container/Worksheet.pm   94.7   75.0    n/a  100.0    0.0    0.9   87.1
  ...Excel/Template/Context.pm   98.0   80.0   75.0  100.0   73.3   31.4   90.7
  ...Excel/Template/Element.pm  100.0    n/a    n/a  100.0    n/a    0.4  100.0
  ...mplate/Element/Backref.pm  100.0   50.0   33.3  100.0    0.0    0.1   87.1
  .../Template/Element/Cell.pm   97.9   75.0   80.0  100.0    0.0    3.9   88.6
  ...mplate/Element/Formula.pm  100.0    n/a    n/a  100.0    0.0    0.1   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   16.3   91.4
  .../Excel/Template/Format.pm   98.4   75.0   33.3  100.0   66.7    9.1   90.5
  ...xcel/Template/Iterator.pm   98.6   80.0   70.6  100.0   50.0    2.0   88.8
  ...el/Template/TextObject.pm   92.9   62.5   33.3  100.0    0.0    2.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.

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
1aa9e458	9	$VERSION = '0.27';
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.
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 $@;
d01e4722	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)
d01e4722	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;
d0eafc11	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 );
a084e413	219	$parser->parse(do { local $/ = undef; <INFILE> });
d0eafc11	220
d01e4722	221	close INFILE
d01e4722	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};
d01e4722	232
d0eafc11	233	my ($xls) = @_;
d0eafc11	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	);
d0eafc11	242
d01e4722	243	$self->{WORKBOOK}->render($context);
d0eafc11	244
8ca5e726	245	return ~~1;
d0eafc11	246	}
d0eafc11	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" />
e976988f	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
d0eafc11	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
6dd4c89d	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
6dd4c89d	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()
d0eafc11	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.
a084e413	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
d0eafc11	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>
8ca5e726	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
8ca5e726	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
c11fa570	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	---------------------------- ------ ------ ------ ------ ------ ------ ------
1aa9e458	480	blib/lib/Excel/Template.pm 93.8 60.0 58.8 100.0 100.0 15.8 83.3
	481	...ib/Excel/Template/Base.pm 94.4 50.0 n/a 100.0 0.0 6.7 80.0
	482	...cel/Template/Container.pm 100.0 50.0 33.3 100.0 0.0 5.7 83.3
	483	...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
	484	.../Container/Conditional.pm 95.9 90.0 66.7 100.0 0.0 2.0 91.0
	485	...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 0.8 96.8
	486	...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
0671c67d	487	...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
9ee3aea0	488	...ainer/KeepLeadingZeros.pm 100.0 100.0 n/a 100.0 0.0 0.1 96.3
0671c67d	489	...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.0 95.0
1aa9e458	490	...emplate/Container/Loop.pm 96.8 50.0 50.0 100.0 0.0 0.5 82.7
	491	...late/Container/Outline.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
	492	...Template/Container/Row.pm 100.0 75.0 n/a 100.0 0.0 0.3 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
1aa9e458	496	...ate/Container/Workbook.pm 100.0 n/a n/a 100.0 n/a 0.6 100.0
	497	...te/Container/Worksheet.pm 94.7 75.0 n/a 100.0 0.0 0.9 87.1
	498	...Excel/Template/Context.pm 98.0 80.0 75.0 100.0 73.3 31.4 90.7
	499	...Excel/Template/Element.pm 100.0 n/a n/a 100.0 n/a 0.4 100.0
	500	...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.1 87.1
	501	.../Template/Element/Cell.pm 97.9 75.0 80.0 100.0 0.0 3.9 88.6
	502	...mplate/Element/Formula.pm 100.0 n/a n/a 100.0 0.0 0.1 94.1
0671c67d	503	...te/Element/FreezePanes.pm 100.0 n/a n/a 100.0 0.0 0.0 95.5
	504	...Template/Element/Image.pm 100.0 100.0 n/a 100.0 0.0 0.1 94.3
	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
1aa9e458	507	...Excel/Template/Factory.pm 100.0 73.1 66.7 100.0 100.0 16.3 91.4
	508	.../Excel/Template/Format.pm 98.4 75.0 33.3 100.0 66.7 9.1 90.5
	509	...xcel/Template/Iterator.pm 98.6 80.0 70.6 100.0 50.0 2.0 88.8
	510	...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 0.0 2.8 80.9
0671c67d	511	Total 97.8 74.3 63.5 100.0 35.7 100.0 89.4
9ee3aea0	512	---------------------------- ------ ------ ------ ------ ------ ------ ------
c11fa570	513
d0eafc11	514	=head1 COPYRIGHT
d0eafc11	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
d0eafc11	524	=cut