[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.24';
    @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;

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

    }

    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 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 branch   cond    sub    pod   time  total
  ---------------------------- ------ ------ ------ ------ ------ ------ ------
  blib/lib/Excel/Template.pm     96.0   64.3   58.8  100.0  100.0   26.3   85.8
  ...ib/Excel/Template/Base.pm   94.4   50.0    n/a  100.0   75.0    6.6   90.0
  ...cel/Template/Container.pm  100.0   50.0   33.3  100.0   66.7    4.5   88.1
  ...emplate/Container/Bold.pm  100.0    n/a    n/a  100.0    0.0    0.2   95.0
  .../Container/Conditional.pm   95.9   90.0   66.7  100.0    0.0    1.7   91.0
  ...plate/Container/Format.pm  100.0    n/a    n/a  100.0    0.0    0.6   96.6
  ...plate/Container/Hidden.pm  100.0    n/a    n/a  100.0    0.0    0.2   95.0
  ...plate/Container/Italic.pm  100.0    n/a    n/a  100.0    0.0    0.2   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.1   95.0
  ...emplate/Container/Loop.pm   96.8   50.0   50.0  100.0   50.0    0.4   84.6
  ...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    1.0  100.0
  ...te/Container/Worksheet.pm   94.7   75.0    n/a  100.0   50.0    1.0   90.3
  ...Excel/Template/Context.pm   98.0   80.0   75.0  100.0   93.3   23.4   91.8
  ...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.2   87.1
  .../Template/Element/Cell.pm   97.9   75.0   80.0  100.0   66.7    3.9   91.1
  ...mplate/Element/Formula.pm  100.0    n/a    n/a  100.0    0.0    0.2   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.2   94.1
  ...Excel/Template/Factory.pm  100.0   75.0    n/a  100.0  100.0   15.5   92.4
  .../Excel/Template/Format.pm   98.4   75.0   33.3  100.0   66.7    8.1   91.2
  ...xcel/Template/Iterator.pm   98.6   80.0   70.6  100.0   83.3    1.4   90.3
  ...el/Template/TextObject.pm   92.9   62.5   33.3  100.0   50.0    3.2   83.0
  Total                          98.0   75.2   63.4  100.0   98.5  100.0   92.2
  ---------------------------- ------ ------ ------ ------ ------ ------ ------

=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
d01e4722	9	$VERSION = '0.24';
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
	205	open( INFILE, "<$file" )
	206	\|\| die "Cannot open '$file' for reading: $!\n";
	207
	208	}
d0eafc11	209
a084e413	210	my $parser = XML::Parser->new( @parms );
a084e413	211	$parser->parse(do { local $/ = undef; <INFILE> });
d0eafc11	212
d01e4722	213	close INFILE
d01e4722	214	unless ref $file;
d0eafc11	215
8ca5e726	216	return ~~1;
d0eafc11	217	}
d01e4722	218	parse = parse = \&parse_xml;
d0eafc11	219
	220	sub _prepare_output
	221	{
	222	my $self = shift;
d01e4722	223	return unless $self->{WORKBOOK};
d01e4722	224
d0eafc11	225	my ($xls) = @_;
d0eafc11	226
6dd4c89d	227	my $context = Excel::Template::Factory->_create(
d0eafc11	228	'CONTEXT',
	229
	230	XLS => $xls,
	231	PARAM_MAP => [ $self->{PARAM_MAP} ],
8c63e224	232	UNICODE => $self->{UNICODE},
d0eafc11	233	);
d0eafc11	234
d01e4722	235	$self->{WORKBOOK}->render($context);
d0eafc11	236
8ca5e726	237	return ~~1;
d0eafc11	238	}
d0eafc11	239
e976988f	240	sub register { shift; Excel::Template::Factory->register(@_) }
d0eafc11	241
	242	1;
	243	__END__
	244
	245	=head1 NAME
	246
	247	Excel::Template - Excel::Template
	248
	249	=head1 SYNOPSIS
	250
	251	First, make a template. This is an XML file, describing the layout of the
	252	spreadsheet.
	253
	254	For example, test.xml:
	255
	256	<workbook>
	257	<worksheet name="tester">
e976988f	258	<cell text="$HOME" />
e976988f	259	<cell text="$PATH" />
d0eafc11	260	</worksheet>
	261	</workbook>
	262
	263	Now, create a small program to use it:
	264
	265	#!/usr/bin/perl -w
e976988f	266
	267	use strict;
	268
9d172425	269	use Excel::Template;
d0eafc11	270
	271	# Create the Excel template
	272	my $template = Excel::Template->new(
	273	filename => 'test.xml',
	274	);
	275
	276	# Add a few parameters
	277	$template->param(
	278	HOME => $ENV{HOME},
	279	PATH => $ENV{PATH},
	280	);
	281
	282	$template->write_file('test.xls');
	283
e976988f	284	If everything worked, then you should have a spreadsheet called text.xls in your working directory that looks something like:
d0eafc11	285
	286	A B C
	287	+----------------+----------------+----------------
	288	1 \| /home/me \| /bin:/usr/bin \|
	289	+----------------+----------------+----------------
	290	2 \| \| \|
	291	+----------------+----------------+----------------
	292	3 \| \| \|
	293
	294	=head1 DESCRIPTION
	295
e976988f	296	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	297
d0eafc11	298	=head1 MOTIVATION
d0eafc11	299
e976988f	300	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	301
	302	=head1 USAGE
	303
	304	=head2 new()
	305
a084e413	306	This creates a Excel::Template object.
d0eafc11	307
6dd4c89d	308	=head3 Parameters
	309
	310	=over 4
	311
a084e413	312	=item * FILE / FILENAME
	313
	314	Excel::Template will parse the template in the given file or filehandle automatically. (You can also use the parse() method, described below.)
	315
	316	If you want to use the __DATA__ section, you can do so by passing
	317
	318	FILE => \*DATA
	319
6dd4c89d	320	=item * RENDERER
6dd4c89d	321
e976988f	322	The default rendering engine is L<Spreadsheet::WriteExcel>. You may, if you choose, change that to another choice. The legal values are:
6dd4c89d	323
	324	=over 4
	325
	326	=item * Excel::Template->RENDER_NML
	327
e976988f	328	This is the default of L<Spreadsheet::WriteExcel>.
8c63e224	329
6dd4c89d	330	=item * Excel::Template->RENDER_BIG
6dd4c89d	331
e976988f	332	This attempts to load L<Spreadsheet::WriteExcel::Big>.
6dd4c89d	333
	334	=item * Excel::Template->RENDER_XML
	335
e976988f	336	This attempts to load L<Spreadsheet::WriteExcelXML>.
6dd4c89d	337
	338	=back
	339
	340	=item * USE_UNICODE
	341
	342	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	343
e976988f	344	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	345
e976988f	346	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	347
6dd4c89d	348	=back
	349
	350	=head3 Deprecated
	351
	352	=over 4
	353
	354	=item * BIG_FILE
	355
	356	Instead, use RENDERER => Excel::Template->RENDER_BIG
	357
	358	=back
	359
d0eafc11	360	=head2 param()
d0eafc11	361
8ca5e726	362	This method is exactly like L<HTML::Template>'s param() method.
d0eafc11	363
	364	=head2 parse() / parse_xml()
	365
a084e413	366	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	367
e976988f	368	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	369
	370	=head2 write_file()
	371
e976988f	372	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	373
	374	=head2 output()
	375
e976988f	376	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	377
6dd4c89d	378	=head2 register()
	379
	380	This allows you to register a class as handling a node. q.v. L<Excel::Template::Factory> for more info.
	381
d0eafc11	382	=head1 SUPPORTED NODES
d0eafc11	383
e976988f	384	This is a partial list of nodes. See the other classes in this distro for more details on specific parameters and the like.
d0eafc11	385
e976988f	386	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	387
	388	=over 4
	389
8ca5e726	390	=item * L<WORKBOOK\|Excel::Template::Container::Workbook>
8ca5e726	391
e976988f	392	This is the node representing the workbook. It is the parent for all other nodes.
8ca5e726	393
	394	=item * L<WORKSHEET\|Excel::Template::Container::Worksheet>
	395
	396	This is the node representing a given worksheet.
	397
	398	=item * L<IF\|Excel::Template::Container::Conditional>
	399
e976988f	400	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	401
	402	=item * L<LOOP\|Excel::Template::Container::Loop>
	403
	404	This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
	405
	406	=item * L<ROW\|Excel::Template::Container::Row>
	407
9ee3aea0	408	This node represents a row of data. This is the 1 in A1. There is no COLUMN node, as of yet.
8ca5e726	409
	410	=item * L<FORMAT\|Excel::Template::Container::Format>
	411
e976988f	412	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	413
	414	=item * L<BACKREF\|Excel::Template::Element::Backref>
	415
	416	This refers back to a cell previously named.
d0eafc11	417
8ca5e726	418	=item * L<CELL\|Excel::Template::Element::Cell>
d0eafc11	419
8ca5e726	420	This is the actual cell in a spreadsheet.
d0eafc11	421
8ca5e726	422	=item * L<FORMULA\|Excel::Template::Element::Formula>
d0eafc11	423
8ca5e726	424	This is a formula in a spreadsheet.
d0eafc11	425
8ca5e726	426	=item * L<RANGE\|Excel::Template::Element::Range>
d0eafc11	427
8ca5e726	428	This is a BACKREF for a number of identically-named cells.
d0eafc11	429
8ca5e726	430	=item * L<VAR\|Excel::Template::Element::Var>
d0eafc11	431
8ca5e726	432	This is a variable. It is generally used when the 'text' attribute isn't
8ca5e726	433	sufficient.
d0eafc11	434
6dd4c89d	435	=back
d0eafc11	436
	437	=head1 BUGS
	438
	439	None, that I know of.
	440
	441	=head1 SUPPORT
	442
e976988f	443	This is production quality software, used in several production web applications.
d0eafc11	444
	445	=head1 AUTHOR
	446
8ca5e726	447	Rob Kinyon (rob.kinyon@gmail.com)
a8441e01	448
	449	=head1 CONTRIBUTORS
	450
c11fa570	451	There is a mailing list at http://groups.google.com/group/ExcelTemplate or exceltemplate@googlegroups.com
d0eafc11	452
c11fa570	453	=head2 Robert Graff
37513eae	454
	455	=over 4
	456
	457	=item * Finishing formats
	458
	459	=item * Fixing several bugs in worksheet naming
	460
6dd4c89d	461	=back
37513eae	462
c11fa570	463	=head1 TEST COVERAGE
c11fa570	464
e976988f	465	I used L<Devel::Cover> to test the coverage of my tests. Every release, I intend to improve these numbers.
c11fa570	466
	467	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.
	468
9ee3aea0	469	---------------------------- ------ ------ ------ ------ ------ ------ ------
	470	File stmt branch cond sub pod time total
	471	---------------------------- ------ ------ ------ ------ ------ ------ ------
	472	blib/lib/Excel/Template.pm 96.0 64.3 58.8 100.0 100.0 26.3 85.8
	473	...ib/Excel/Template/Base.pm 94.4 50.0 n/a 100.0 75.0 6.6 90.0
	474	...cel/Template/Container.pm 100.0 50.0 33.3 100.0 66.7 4.5 88.1
	475	...emplate/Container/Bold.pm 100.0 n/a n/a 100.0 0.0 0.2 95.0
	476	.../Container/Conditional.pm 95.9 90.0 66.7 100.0 0.0 1.7 91.0
	477	...plate/Container/Format.pm 100.0 n/a n/a 100.0 0.0 0.6 96.6
	478	...plate/Container/Hidden.pm 100.0 n/a n/a 100.0 0.0 0.2 95.0
	479	...plate/Container/Italic.pm 100.0 n/a n/a 100.0 0.0 0.2 95.0
	480	...ainer/KeepLeadingZeros.pm 100.0 100.0 n/a 100.0 0.0 0.1 96.3
	481	...plate/Container/Locked.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
	482	...emplate/Container/Loop.pm 96.8 50.0 50.0 100.0 50.0 0.4 84.6
	483	...late/Container/Outline.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
	484	...Template/Container/Row.pm 100.0 75.0 n/a 100.0 50.0 0.3 93.8
	485	...mplate/Container/Scope.pm 100.0 n/a n/a 100.0 n/a 0.1 100.0
	486	...plate/Container/Shadow.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
	487	...te/Container/Strikeout.pm 100.0 n/a n/a 100.0 0.0 0.1 95.0
	488	...ate/Container/Workbook.pm 100.0 n/a n/a 100.0 n/a 1.0 100.0
	489	...te/Container/Worksheet.pm 94.7 75.0 n/a 100.0 50.0 1.0 90.3
	490	...Excel/Template/Context.pm 98.0 80.0 75.0 100.0 93.3 23.4 91.8
	491	...Excel/Template/Element.pm 100.0 n/a n/a 100.0 n/a 0.3 100.0
	492	...mplate/Element/Backref.pm 100.0 50.0 33.3 100.0 0.0 0.2 87.1
	493	.../Template/Element/Cell.pm 97.9 75.0 80.0 100.0 66.7 3.9 91.1
	494	...mplate/Element/Formula.pm 100.0 n/a n/a 100.0 0.0 0.2 94.1
	495	...Template/Element/Range.pm 100.0 66.7 n/a 100.0 66.7 0.2 93.3
	496	...l/Template/Element/Var.pm 100.0 n/a n/a 100.0 0.0 0.2 94.1
	497	...Excel/Template/Factory.pm 100.0 75.0 n/a 100.0 100.0 15.5 92.4
	498	.../Excel/Template/Format.pm 98.4 75.0 33.3 100.0 66.7 8.1 91.2
	499	...xcel/Template/Iterator.pm 98.6 80.0 70.6 100.0 83.3 1.4 90.3
	500	...el/Template/TextObject.pm 92.9 62.5 33.3 100.0 50.0 3.2 83.0
	501	Total 98.0 75.2 63.4 100.0 98.5 100.0 92.2
	502	---------------------------- ------ ------ ------ ------ ------ ------ ------
c11fa570	503
d0eafc11	504	=head1 COPYRIGHT
d0eafc11	505
e976988f	506	This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
d0eafc11	507
e976988f	508	The full text of the license can be found in the LICENSE file included with this module.
d0eafc11	509
	510	=head1 SEE ALSO
	511
e976988f	512	perl(1), L<HTML::Template>, L<Spreadsheet::WriteExcel>
d0eafc11	513
d0eafc11	514	=cut