[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.19';
    @ISA      = qw( Excel::Template::Base );
}

use File::Basename;
use XML::Parser;
use IO::Scalar;

sub new
{
    my $class = shift;
    my $self = $class->SUPER::new(@_);

    $self->parse_xml($self->{FILENAME})
        if defined $self->{FILENAME};

    my @renderer_classes = ( 'Spreadsheet::WriteExcel' );
    if (exists $self->{BIG_FILE} && $self->{BIG_FILE})
    {
        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";

    return $self;
}

sub param
{
    my $self = shift;

    # Allow an arbitrary number of hashrefs, so long as they're the first things    # into param(). Put each one onto the end, de-referenced.
    push @_, %{shift @_} while UNIVERSAL::isa($_[0], 'HASH');

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

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

    return !!1;
}

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

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

    $self->_prepare_output($xls);

    $xls->close;

    return !!1;
}

sub output
{
    my $self = shift;

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

    $self->write_file(\*XLS);

    return $output;
}

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

    my ($filename, $dirname) = fileparse($fname);
 
    my @stack;
    my $parser = XML::Parser->new(
        Base => $dirname,
        Handlers => {
            Start => sub {
                shift;

                my $name = uc shift;

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

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

                my $parent = $stack[-1];

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

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

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

        $parser->parse(do { local $/ = undef; <INFILE> });

        close INFILE;
    }

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

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

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

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

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

    return ~~1;
}

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

1;
__END__

=head1 NAME

Excel::Template - Excel::Template

=head1 SYNOPSIS

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

For example, test.xml:

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

Now, create a small program to use it:

  #!/usr/bin/perl -w
  use 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 in your work 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 HTML::Template, but provide Excel files
instead. The existing modules don't do the trick, as they require replication
of logic that's already been done within HTML::Template.

=head1 MOTIVATION

I do a lot of Perl/CGI for reporting purposes. In nearly every place I've been,
I've been asked for HTML, PDF, and Excel. HTML::Template provides the first, and
PDF::Template does the second pretty well. But, generating Excel was the
sticking point. I already had the data structure for the other templating
modules, but I just didn't have an easy mechanism to get that data structure
into an XLS file.

=head1 USAGE

=head2 new()

This creates a Excel::Template object. If passed a FILENAME parameter, it will
parse the template in the given file. (You can also use the parse() method,
described below.)

new() accepts an optional BIG_FILE parameter. This will attempt to change the
renderer from L<Spreadsheet::WriteExcel> to L<Spreadsheet::WriteExcel::Big>. You
must have L<Spreadsheet::WriteExcel::Big> installed on your system.

NOTE: L<Spreadsheet::WriteExcel::Big> and mod_perl clash for some reason. This
is outside of my control.

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

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

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

=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

Robert Graff -

=over 4

=item * Finishing formats

=item * Fixing several bugs in worksheet naming

=back 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), HTML::Template, 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
8ca5e726	9	$VERSION = '0.19';
d0eafc11	10	@ISA = qw( Excel::Template::Base );
	11	}
	12
	13	use File::Basename;
	14	use XML::Parser;
d0eafc11	15	use IO::Scalar;
	16
	17	sub new
	18	{
	19	my $class = shift;
	20	my $self = $class->SUPER::new(@_);
	21
	22	$self->parse_xml($self->{FILENAME})
	23	if defined $self->{FILENAME};
	24
	25	my @renderer_classes = ( 'Spreadsheet::WriteExcel' );
	26	if (exists $self->{BIG_FILE} && $self->{BIG_FILE})
	27	{
	28	unshift @renderer_classes, 'Spreadsheet::WriteExcel::Big';
	29	}
	30
	31	$self->{RENDERER} = undef;
	32	foreach my $class (@renderer_classes)
	33	{
	34	(my $filename = $class) =~ s!::!/!g;
	35	eval {
	36	require "$filename.pm";
	37	$class->import;
	38	};
	39	if ($@) {
dee1f239	40	warn "Could not find or compile '$class'\n" if $^W;
d0eafc11	41	} else {
	42	$self->{RENDERER} = $class;
	43	last;
	44	}
	45	}
	46
	47	defined $self->{RENDERER} \|\|
	48	die "Could not find a renderer class. Tried:\n\t" .
	49	join("\n\t", @renderer_classes) .
	50	"\n";
	51
	52	return $self;
	53	}
	54
	55	sub param
	56	{
	57	my $self = shift;
	58
	59	# Allow an arbitrary number of hashrefs, so long as they're the first things # into param(). Put each one onto the end, de-referenced.
	60	push @_, %{shift @_} while UNIVERSAL::isa($_[0], 'HASH');
	61
	62	(@_ % 2)
	63	&& die __PACKAGE__, "->param() : Odd number of parameters to param()\n";
	64
	65	my %params = @_;
	66	$params{uc $_} = delete $params{$_} for keys %params;
	67	@{$self->{PARAM_MAP}}{keys %params} = @params{keys %params};
	68
b6bc5a5d	69	return !!1;
d0eafc11	70	}
	71
	72	sub write_file
	73	{
	74	my $self = shift;
	75	my ($filename) = @_;
	76
	77	my $xls = $self->{RENDERER}->new($filename)
	78	\|\| die "Cannot create XLS in '$filename': $!\n";
	79
	80	$self->_prepare_output($xls);
	81
	82	$xls->close;
	83
b6bc5a5d	84	return !!1;
d0eafc11	85	}
	86
	87	sub output
	88	{
	89	my $self = shift;
	90
	91	my $output;
	92	tie *XLS, 'IO::Scalar', \$output;
	93
	94	$self->write_file(\*XLS);
	95
	96	return $output;
	97	}
	98
d0eafc11	99	sub parse_xml
	100	{
	101	my $self = shift;
	102	my ($fname) = @_;
	103
	104	my ($filename, $dirname) = fileparse($fname);
	105
	106	my @stack;
	107	my $parser = XML::Parser->new(
	108	Base => $dirname,
	109	Handlers => {
	110	Start => sub {
	111	shift;
	112
	113	my $name = uc shift;
	114
	115	my $node = Excel::Template::Factory->create_node($name, @_);
	116	die "'$name' (@_) didn't make a node!\n" unless defined $node;
	117
37513eae	118	if ( $node->isa( 'WORKBOOK' ) )
d0eafc11	119	{
	120	push @{$self->{WORKBOOKS}}, $node;
	121	}
37513eae	122	elsif ( $node->is_embedded )
d0eafc11	123	{
	124	return unless @stack;
	125
	126	if (exists $stack[-1]{TXTOBJ} &&
	127	$stack[-1]{TXTOBJ}->isa('TEXTOBJECT'))
	128	{
	129	push @{$stack[-1]{TXTOBJ}{STACK}}, $node;
	130	}
	131
	132	}
	133	else
	134	{
	135	push @{$stack[-1]{ELEMENTS}}, $node
	136	if @stack;
	137	}
	138	push @stack, $node;
	139	},
	140	Char => sub {
	141	shift;
	142	return unless @stack;
	143
	144	my $parent = $stack[-1];
	145
	146	if (
	147	exists $parent->{TXTOBJ}
	148	&&
	149	$parent->{TXTOBJ}->isa('TEXTOBJECT')
	150	) {
	151	push @{$parent->{TXTOBJ}{STACK}}, @_;
	152	}
	153	},
	154	End => sub {
	155	shift;
	156	return unless @stack;
	157
	158	pop @stack if $stack[-1]->isa(uc $_[0]);
	159	},
	160	},
	161	);
	162
	163	{
b6bc5a5d	164	open( INFILE, "<$fname" )
d0eafc11	165	\|\| die "Cannot open '$fname' for reading: $!\n";
d0eafc11	166
b6bc5a5d	167	$parser->parse(do { local $/ = undef; <INFILE> });
d0eafc11	168
b6bc5a5d	169	close INFILE;
d0eafc11	170	}
d0eafc11	171
8ca5e726	172	return ~~1;
d0eafc11	173	}
b6bc5a5d	174	*parse = \&parse_xml;
d0eafc11	175
	176	sub _prepare_output
	177	{
	178	my $self = shift;
	179	my ($xls) = @_;
	180
	181	my $context = Excel::Template::Factory->create(
	182	'CONTEXT',
	183
	184	XLS => $xls,
	185	PARAM_MAP => [ $self->{PARAM_MAP} ],
	186	);
	187
	188	$_->render($context) for @{$self->{WORKBOOKS}};
	189
8ca5e726	190	return ~~1;
d0eafc11	191	}
	192
	193	sub register { shift; Excel::Template::Factory::register(@_) }
	194
	195	1;
	196	__END__
	197
	198	=head1 NAME
	199
	200	Excel::Template - Excel::Template
	201
	202	=head1 SYNOPSIS
	203
	204	First, make a template. This is an XML file, describing the layout of the
	205	spreadsheet.
	206
	207	For example, test.xml:
	208
	209	<workbook>
	210	<worksheet name="tester">
	211	<cell text="$HOME"/>
	212	<cell text="$PATH"/>
	213	</worksheet>
	214	</workbook>
	215
	216	Now, create a small program to use it:
	217
	218	#!/usr/bin/perl -w
	219	use Excel::Template
	220
	221	# Create the Excel template
	222	my $template = Excel::Template->new(
	223	filename => 'test.xml',
	224	);
	225
	226	# Add a few parameters
	227	$template->param(
	228	HOME => $ENV{HOME},
	229	PATH => $ENV{PATH},
	230	);
	231
	232	$template->write_file('test.xls');
	233
	234	If everything worked, then you should have a spreadsheet in your work directory
	235	that looks something like:
	236
	237	A B C
	238	+----------------+----------------+----------------
	239	1 \| /home/me \| /bin:/usr/bin \|
	240	+----------------+----------------+----------------
	241	2 \| \| \|
	242	+----------------+----------------+----------------
	243	3 \| \| \|
	244
	245	=head1 DESCRIPTION
	246
	247	This is a module used for templating Excel files. Its genesis came from the
	248	need to use the same datastructure as HTML::Template, but provide Excel files
	249	instead. The existing modules don't do the trick, as they require replication
	250	of logic that's already been done within HTML::Template.
	251
d0eafc11	252	=head1 MOTIVATION
	253
	254	I do a lot of Perl/CGI for reporting purposes. In nearly every place I've been,
	255	I've been asked for HTML, PDF, and Excel. HTML::Template provides the first, and
	256	PDF::Template does the second pretty well. But, generating Excel was the
	257	sticking point. I already had the data structure for the other templating
	258	modules, but I just didn't have an easy mechanism to get that data structure
	259	into an XLS file.
	260
	261	=head1 USAGE
	262
	263	=head2 new()
	264
8ca5e726	265	This creates a Excel::Template object. If passed a FILENAME parameter, it will
d0eafc11	266	parse the template in the given file. (You can also use the parse() method,
	267	described below.)
	268
8ca5e726	269	new() accepts an optional BIG_FILE parameter. This will attempt to change the
	270	renderer from L<Spreadsheet::WriteExcel> to L<Spreadsheet::WriteExcel::Big>. You
	271	must have L<Spreadsheet::WriteExcel::Big> installed on your system.
	272
	273	NOTE: L<Spreadsheet::WriteExcel::Big> and mod_perl clash for some reason. This
	274	is outside of my control.
	275
d0eafc11	276	=head2 param()
d0eafc11	277
8ca5e726	278	This method is exactly like L<HTML::Template>'s param() method.
d0eafc11	279
	280	=head2 parse() / parse_xml()
	281
	282	This method actually parses the template file. It can either be called
	283	separately or through the new() call. It will die() if it runs into a situation
	284	it cannot handle.
	285
	286	=head2 write_file()
	287
	288	Create the Excel file and write it to the specified filename, if possible. (This
	289	is when the actual merging of the template and the parameters occurs.)
	290
	291	=head2 output()
	292
	293	It will act just like HTML::Template's output() method, returning the resultant
	294	file as a stream, usually for output to the web. (This is when the actual
	295	merging of the template and the parameters occurs.)
	296
	297	=head1 SUPPORTED NODES
	298
8ca5e726	299	This is a partial list of nodes. See the other classes in this distro for more
d0eafc11	300	details on specific parameters and the like.
	301
	302	Every node can set the ROW and COL parameters. These are the actual ROW/COL
8ca5e726	303	values that the next CELL-type tag will write into.
d0eafc11	304
	305	=over 4
	306
8ca5e726	307	=item * L<WORKBOOK\|Excel::Template::Container::Workbook>
	308
	309	This is the node representing the workbook. It is the parent for all other
	310	nodes.
	311
	312	=item * L<WORKSHEET\|Excel::Template::Container::Worksheet>
	313
	314	This is the node representing a given worksheet.
	315
	316	=item * L<IF\|Excel::Template::Container::Conditional>
	317
	318	This node represents a conditional expression. Its children may or may not be
	319	rendered. It behaves just like L<HTML::Template>'s TMPL_IF.
	320
	321	=item * L<LOOP\|Excel::Template::Container::Loop>
	322
	323	This node represents a loop. It behaves just like L<HTML::Template>'s TMPL_LOOP.
	324
	325	=item * L<ROW\|Excel::Template::Container::Row>
	326
	327	This node represents a row of data. This is the A in A1.
	328
	329	=item * L<FORMAT\|Excel::Template::Container::Format>
	330
	331	This node varies the format for its children. All formatting options supported
	332	in L<Spreadsheet::WriteExcel> are supported here. There are also a number of
	333	formatting shortcuts, such as L<BOLD\|Excel::Template::Container::Bold> and
	334	L<ITALIC\|Excel::Template::Container::Italic>.
	335
	336	=item * L<BACKREF\|Excel::Template::Element::Backref>
	337
	338	This refers back to a cell previously named.
d0eafc11	339
8ca5e726	340	=item * L<CELL\|Excel::Template::Element::Cell>
d0eafc11	341
8ca5e726	342	This is the actual cell in a spreadsheet.
d0eafc11	343
8ca5e726	344	=item * L<FORMULA\|Excel::Template::Element::Formula>
d0eafc11	345
8ca5e726	346	This is a formula in a spreadsheet.
d0eafc11	347
8ca5e726	348	=item * L<RANGE\|Excel::Template::Element::Range>
d0eafc11	349
8ca5e726	350	This is a BACKREF for a number of identically-named cells.
d0eafc11	351
8ca5e726	352	=item * L<VAR\|Excel::Template::Element::Var>
d0eafc11	353
8ca5e726	354	This is a variable. It is generally used when the 'text' attribute isn't
8ca5e726	355	sufficient.
d0eafc11	356
	357	=back 4
	358
	359	=head1 BUGS
	360
	361	None, that I know of.
	362
	363	=head1 SUPPORT
	364
8ca5e726	365	This is production quality software, used in several production web
8ca5e726	366	applications.
d0eafc11	367
	368	=head1 AUTHOR
	369
8ca5e726	370	Rob Kinyon (rob.kinyon@gmail.com)
a8441e01	371
	372	=head1 CONTRIBUTORS
	373
8ca5e726	374	There is a mailing list at http://groups.google.com/group/ExcelTemplate
d0eafc11	375
37513eae	376	Robert Graff -
	377
	378	=over 4
	379
	380	=item * Finishing formats
	381
	382	=item * Fixing several bugs in worksheet naming
	383
	384	=back 4
	385
d0eafc11	386	=head1 COPYRIGHT
	387
	388	This program is free software; you can redistribute
	389	it and/or modify it under the same terms as Perl itself.
	390
	391	The full text of the license can be found in the
	392	LICENSE file included with this module.
	393
	394	=head1 SEE ALSO
	395
	396	perl(1), HTML::Template, Spreadsheet::WriteExcel.
	397
	398	=cut