Adding Excel and OpenDocument spreadsheet options. ODS needs more work.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1657 4979c152-3d1c-0410-bac9-87ea11338e46

6 files changed, 376 insertions, 17 deletions

diff --git a/LedgerSMB/Template/ODS.pm b/LedgerSMB/Template/ODS.pm
new file mode 100644
index 00000000..b425494b
--- /dev/null
+++ b/LedgerSMB/Template/ODS.pm
@@ -0,0 +1,167 @@
+
+=head1 NAME
+
+LedgerSMB::Template::ODS  Template support module for LedgerSMB
+
+=head1 SYNOPSIS
+
+OpenDocument Spreadsheet output.
+
+=head1 METHODS
+
+=over
+
+=item get_template ($name)
+
+Returns the appropriate template filename for this format.  '.xlst' is the
+extension that was chosen for the templates.
+
+=item preprocess ($vars)
+
+Returns $vars.
+
+=item process ($parent, $cleanvars)
+
+Processes the template for text.
+
+=item postprocess ($parent)
+
+Returns the output filename.
+
+=back
+
+=head1 Copyright (C) 2007, The LedgerSMB core team.
+
+This work contains copyrighted information from a number of sources all used
+with permission.  
+
+It is released under the GNU General Public License Version 2 or, at your 
+option, any later version.  See COPYRIGHT file for details.  For a full list 
+including contact information of contributors, maintainers, and copyright 
+holders, see the CONTRIBUTORS file.
+=cut
+
+package LedgerSMB::Template::ODS;
+
+use Error qw(:try);
+use CGI::Simple::Standard qw(:html);
+use Template;
+use XML::Twig;
+use OpenOffice::OODoc;
+use LedgerSMB::Template::TTI18N;
+
+sub get_template {
+	my $name = shift;
+	return "${name}.odst";
+}
+
+sub preprocess {
+    my $rawvars = shift;
+    my $vars;
+    my $type = ref $rawvars;
+
+    #XXX fix escaping function
+    return $rawvars if $type =~ /^LedgerSMB::Locale/;
+    if ( $type eq 'ARRAY' ) {
+        for (@{$rawvars}) {
+            push @{$vars}, preprocess( $_ );
+        }
+    } elsif (!$type) {
+        return escapeHTML($rawvars);
+    } else { # Hashes and objects
+        for ( keys %{$rawvars} ) {
+            $vars->{preprocess($_)} = preprocess( $rawvars->{$_} );
+        }
+    }
+    
+    return $vars;
+}
+
+sub _worksheet_handler {
+	if ($sheet) {
+		$rowcount = -1;
+		$currcol = 0;
+	}
+	$sheet = $ods->getTable(0, $_->{att}->{rows}, $_->{att}->{columns});
+	$ods->renameTable($sheet, $_->{att}->{name});
+}
+
+sub _row_handler {
+	$rowcount++;
+	$currcol = 0;
+}
+
+sub _cell_handler {
+	my $cell = $ods->getCell(-1, $rowcount, $currcol);
+	$ods->cellValue($cell, $_->{att}->{text});
+	$currcol++;
+}
+
+sub _ods_process {
+	my ($filename, $template, $user) = @_;
+
+	# the handlers need these vars in common
+	local $ods = ooDocument(file => $filename, create => 'spreadsheet');
+	local $sheet;
+	local $rowcount;
+	local $currcol;
+	my $parser = XML::Twig->new(
+		start_tag_handlers => {
+			worksheet => \&_worksheet_handler,
+			row => \&_row_handler,
+			},
+		twig_handlers => {
+			cell => \&_cell_handler,
+			}
+		);
+	$parser->parse($template);
+	$parser->flush;
+	#$ods->normalizeSheet($sheet, $rowcount, $colcount);
+	$ods->save;
+}
+
+sub process {
+	my $parent = shift;
+	my $cleanvars = shift;
+	my $template;
+	my $source;
+	my $tempdir = ${LedgerSMB::Sysconfig::tempdir};
+	my $output = '';
+	$parent->{outputfile} ||= "$tempdir/$parent->{template}-output-$$";
+
+	if (ref $parent->{template} eq 'SCALAR') {
+		$source = $parent->{template};
+	} elsif (ref $parent->{template} eq 'ARRAY') {
+		$source = join "\n", @{$parent->{template}};
+	} else {
+		$source = get_template($parent->{template});
+	}
+	$template = Template->new({
+		INCLUDE_PATH => $parent->{include_path},
+		START_TAG => quotemeta('<?lsmb'),
+		END_TAG => quotemeta('?>'),
+		DELIMITER => ';',
+		DEBUG => ($parent->{debug})? 'dirs': undef,
+		DEBUG_FORMAT => '',
+		}) || throw Error::Simple Template->error(); 
+
+	if (not $template->process(
+		$source, 
+		{%$cleanvars, %$LedgerSMB::Template::TTI18N::ttfuncs,
+			'escape' => \&preprocess},
+		\$output, binmode => ':utf8')) {
+		throw Error::Simple $template->error();
+	}
+	&_ods_process("$parent->{outputfile}.ods", $output, $parent->{myconfig});
+
+	parent->{mimetype} = 'application/vnd.oasis.opendocument.spreadsheet';
+}
+
+sub postprocess {
+	my $parent = shift;
+	$parent->{rendered} = "$parent->{outputfile}.ods";
+	return $parent->{rendered};
+}
+
+1;
+
diff --git a/LedgerSMB/Template/XLS.pm b/LedgerSMB/Template/XLS.pm
new file mode 100644
index 00000000..5e03c7c9
--- /dev/null
+++ b/LedgerSMB/Template/XLS.pm
@@ -0,0 +1,123 @@
+
+=head1 NAME
+
+LedgerSMB::Template::XLS  Template support module for LedgerSMB
+
+=head1 SYNOPSIS
+
+Excel spreadsheet output.  For details about the XML template document
+elements, see Excel::Template.  For details about various parameters used, see
+Spreadsheet::WriteExcel.  As this module uses Excel::Template::Plus, flow
+control and variable substitution are handled with TT with the usual for LSMB
+tag formatting of <?lsmb foo ?> instead of the more HTML::Template-like forms
+of Excel::Template.
+
+=head1 METHODS
+
+=over
+
+=item get_template ($name)
+
+Returns the appropriate template filename for this format.  '.xlst' is the
+extension that was chosen for the templates.
+
+=item preprocess ($vars)
+
+Returns $vars.
+
+=item process ($parent, $cleanvars)
+
+Processes the template for text.
+
+=item postprocess ($parent)
+
+Returns the output filename.
+
+=back
+
+=head1 Copyright (C) 2007, The LedgerSMB core team.
+
+This work contains copyrighted information from a number of sources all used
+with permission.  
+
+It is released under the GNU General Public License Version 2 or, at your 
+option, any later version.  See COPYRIGHT file for details.  For a full list 
+including contact information of contributors, maintainers, and copyright 
+holders, see the CONTRIBUTORS file.
+=cut
+
+package LedgerSMB::Template::XLS;
+
+use Error qw(:try);
+use CGI::Simple::Standard qw(:html);
+use Excel::Template::Plus;
+use LedgerSMB::Template::TTI18N;
+
+sub get_template {
+	my $name = shift;
+	return "${name}.xlst";
+}
+
+sub preprocess {
+    my $rawvars = shift;
+    my $vars;
+    my $type = ref $rawvars;
+
+    #XXX fix escaping function
+    return $rawvars if $type =~ /^LedgerSMB::Locale/;
+    if ( $type eq 'ARRAY' ) {
+        for (@{$rawvars}) {
+            push @{$vars}, preprocess( $_ );
+        }
+    } elsif (!$type) {
+        return escapeHTML($rawvars);
+    } else { # Hashes and objects
+        for ( keys %{$rawvars} ) {
+            $vars->{preprocess($_)} = preprocess( $rawvars->{$_} );
+        }
+    }
+    
+    return $vars;
+}
+
+sub process {
+	my $parent = shift;
+	my $cleanvars = shift;
+	my $template;
+	my $source;
+	my $tempdir = ${LedgerSMB::Sysconfig::tempdir};
+	$parent->{outputfile} ||= "$tempdir/$parent->{template}-output-$$";
+
+	if (ref $parent->{template} eq 'SCALAR') {
+		$source = $parent->{template};
+	} elsif (ref $parent->{template} eq 'ARRAY') {
+		$source = join "\n", @{$parent->{template}};
+	} else {
+		$source = get_template($parent->{template});
+	}
+	$template = Excel::Template::Plus->new(
+		engine => 'TT',
+		template => $source,
+		params => {%$cleanvars, %$LedgerSMB::Template::TTI18N::ttfuncs,
+			'escape' => \&preprocess},
+		config => {
+			INCLUDE_PATH => $parent->{include_path},
+			START_TAG => quotemeta('<?lsmb'),
+			END_TAG => quotemeta('?>'),
+			DELIMITER => ';',
+			DEBUG => ($parent->{debug})? 'dirs': undef,
+			DEBUG_FORMAT => '',},
+	);
+	$template->write_file("$parent->{outputfile}.xls");
+
+	parent->{mimetype} = 'application/vnd.ms-excel';
+}
+
+sub postprocess {
+	my $parent = shift;
+	$parent->{rendered} = "$parent->{outputfile}.xls" if $parent->{outputfile};
+	return $parent->{rendered};
+}
+
+1;
+
diff --git a/Makefile.PL b/Makefile.PL
index f7950a9b..05c61287 100644
--- a/Makefile.PL
+++ b/Makefile.PL
@@ -28,7 +28,6 @@ requires 'Cwd';
 requires 'Config::Std';
 requires 'MIME::Lite';
 requires 'Template' => '2.14';
-requires 'Template::Latex';
 requires 'Error';
 requires 'CGI::Simple';
 
@@ -49,14 +48,19 @@ feature 'Developer tool dependencies',
     'Getopt::Long' => 0,
     'FileHandle' => 0;
 
-#feature 'Excel output',
-#    -default => 0,
-#    'Excel::Template::Plus' => 0;
-#
-#feature 'OpenOffice.org output',
-#    -default => 0,
-#    'XML::Twig' => 0,
-#    'OpenOffice::OODoc' => 0;
+# Rendering options
+feature 'PDF and Postscript output'
+    -default => 0,
+    'Template::Latex' => 0;
+
+feature 'OpenOffice.org output',
+    -default => 0,
+    'XML::Twig' => 0,
+    'OpenOffice::OODoc' => 0;
+
+feature 'Excel output',
+    -default => 0,
+    'Excel::Template::Plus' => 0;
 
 auto_install;
 WriteAll;
diff --git a/UI/am-list-accounts.odst b/UI/am-list-accounts.odst
new file mode 100644
index 00000000..3b6a7392
--- /dev/null
+++ b/UI/am-list-accounts.odst
@@ -0,0 +1,30 @@
+<workbook>
+	<worksheet name="test" rows="<?lsmb rows.size + 1 ?>" columns="<?lsmb columns.size ?>">
+		<format bottom="1"><row>
+	<?lsmb FOREACH column IN columns ?>
+	<?lsmb IF heading.$column.text ?>
+			<cell text="<?lsmb heading.$column.text ?>" />
+	<?lsmb ELSE ?>
+			<cell text="<?lsmb heading.$column ?>" />
+	<?lsmb END; END ?>
+		</row></format>
+	<?lsmb FOREACH row IN rows ?>
+		<row>
+		<?lsmb FOREACH column IN columns ?>
+			<?lsmb IF row.$column.href ?>
+			<cell text="<?lsmb row.$column.text ?>" />
+			<?lsmb ELSE ?>
+			<cell text="<?lsmb row.$column ?>" />
+			<?lsmb END ?>
+		<?lsmb END ?>
+		</row>
+	<?lsmb END ?>
+	<?lsmb IF totals; t = [] ?>
+	<row>
+		<?lsmb FOREACH column IN columns ?>
+		<cell text="<?lsmb totals.$column ?>" />
+		<?lsmb END ?>
+	</row>
+	<?lsmb END ?>
+	</worksheet>
+</workbook>
diff --git a/UI/am-list-accounts.xlst b/UI/am-list-accounts.xlst
new file mode 100644
index 00000000..7c00fb67
--- /dev/null
+++ b/UI/am-list-accounts.xlst
@@ -0,0 +1,30 @@
+<workbook>
+	<worksheet name="test">
+		<format bottom="1"><row>
+	<?lsmb FOREACH column IN columns ?>
+	<?lsmb IF heading.$column.text ?>
+			<cell text="<?lsmb heading.$column.text ?>" />
+	<?lsmb ELSE ?>
+			<cell text="<?lsmb heading.$column ?>" />
+	<?lsmb END; END ?>
+		</row></format>
+	<?lsmb FOREACH row IN rows ?>
+		<row>
+		<?lsmb FOREACH column IN columns ?>
+			<?lsmb IF row.$column.href ?>
+			<cell text="<?lsmb row.$column.text ?>" />
+			<?lsmb ELSE ?>
+			<cell text="<?lsmb row.$column ?>" />
+			<?lsmb END ?>
+		<?lsmb END ?>
+		</row>
+	<?lsmb END ?>
+	<?lsmb IF totals; t = [] ?>
+	<row>
+		<?lsmb FOREACH column IN columns ?>
+		<cell text="<?lsmb totals.$column ?>" />
+		<?lsmb END ?>
+	</row>
+	<?lsmb END ?>
+	</worksheet>
+</workbook>
diff --git a/bin/am.pl b/bin/am.pl
index 120207a0..02070cf6 100644
--- a/bin/am.pl
+++ b/bin/am.pl
@@ -436,20 +436,23 @@ sub list_account {
     }
 
     my @buttons;
-    push @buttons, {
-        name => 'action',
-        value => 'csv_list_account',
-        text => $locale->text('CSV Report'),
-        type => 'submit',
-        class => 'submit',
-    };
+    for my $type (qw(CSV XLS ODS)) {
+        push @buttons, {
+            name => 'action',
+            value => lc "${type}_list_account",
+            text => $locale->text("$type Report"),
+            type => 'submit',
+            class => 'submit',
+        };
+    }
 
+    my $format = uc substr($form->{action}, 0, 3);
     my $template = LedgerSMB::Template->new(
         user => \%myconfig, 
         locale => $locale,
         path => 'UI',
         template => 'am-list-accounts',
-        format => ($form->{action} =~ /^csv/)? 'CSV': 'HTML');
+        format => ($format ne 'LIS')? $format: 'HTML');
     $template->render({
         form => \%$form,
         buttons => \@buttons,
@@ -460,6 +463,8 @@ sub list_account {
 }
 
 sub csv_list_account { &list_account }
+sub xls_list_account { &list_account }
+sub ods_list_account { &list_account }
 
 sub delete_account {