[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);
    };

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