From 11686f7c21bed96f563e70212f44044604f8a733 Mon Sep 17 00:00:00 2001 From: mark Date: Sat, 6 Nov 2010 23:45:42 +0000 Subject: [PATCH] sales tax report in Excel, RT#10321 --- httemplate/search/report_tax-xls.cgi | 158 +++++++++++++++++++++++++++++++++++ httemplate/search/report_tax.cgi | 48 ++++++----- 2 files changed, 187 insertions(+), 19 deletions(-) create mode 100755 httemplate/search/report_tax-xls.cgi diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi new file mode 100755 index 000000000..54a17d0b6 --- /dev/null +++ b/httemplate/search/report_tax-xls.cgi @@ -0,0 +1,158 @@ +<% $data %> +<%init> + +use Spreadsheet::WriteExcel; +use Spreadsheet::WriteExcel::Utility 'xl_range_formula'; +use List::Util 'max'; +use HTML::TableExtract 'tree'; + +my $data = ''; +my $XLS = new IO::Scalar \$data; +my $workbook = Spreadsheet::WriteExcel->new($XLS) + or die "Error opening .xls file: $!"; + +# hardcoded formats, this could be handled better +my $light_gray = $workbook->set_custom_color(63, '#eeeeee'); +my %format = ( + title => { + size => 24, + align => 'center', + bg_color => 'silver', + }, + colhead => { + size => 11, + bold => 1, + align => 'center', + valign => 'vcenter', + text_wrap => 1, + }, + rowhead => { + size => 11, + valign => 'bottom', + text_wrap => 1, + }, + amount => { + size => 11, + align => 'right', + valign => 'bottom', + num_format=> 8, + }, + 'size-1' => { + size => 7.5, + align => 'center', + valign => 'vcenter', + bold => 1, + text_wrap => 1, + }, + 'size+1' => { + size => 12, + align => 'center', + valign => 'vcenter', + bold => 1, + }, + text => { + size => 11, + text_wrap => 1, + }, +); +my %default = ( + font => 'Calibri', + bg_color => $light_gray, + border => 1, +); +my @widths = ( #ick + 18, (10.5, 3) x 6, 10.5, 10.5, 3, 10.5, 3, 10.5, 3, 10.5 +); +foreach (keys(%format)) { + my %f = (%default, %{$format{$_}}); + $format{$_} = $workbook->add_format(%f); + $format{"m_$_"} = $workbook->add_format(%f); # for merged cells + $format{"t_$_"} = $workbook->add_format(%f, bg_color => 'yellow'); # totals +} +my $ws = $workbook->add_worksheet('taxreport'); + +my $htmldoc = include('report_tax.cgi'); + +my ($title) = ($htmldoc =~ /\s*(.*)\s*<\/title>/i); + +# attribs option: how to locate the table? It's the only one with class="grid". +my $te = HTML::TableExtract->new(attribs => {class => 'grid'}); +$te->parse($htmldoc); +my $table = $te->first_table_found; + +my @sheet; +$sheet[0][0] = { + text => $title, + format => 'title', + colspan => '18', +}; +# excel position +my $x = 0; +my $y = 3; +foreach my $row ($table->rows()) { + $x = 0; + $sheet[$y] = []; + foreach my $cell (@$row) { + if ($cell and ref($cell) eq 'HTML::ElementTable::DataElement') { + my $f = 'text'; + if ( $cell->as_HTML =~ /font/i ) { + my ($el) = $cell->content_list; + $f = 'size'.$el->attr('size') if $el->attr('size'); + } + elsif ( $cell->as_text =~ /^\$/ ) { + $f = 'amount' + } + elsif ( $cell->tag eq 'th' ) { + $f = 'colhead'; + } + elsif ( $x == 0 ) { + $f = 'rowhead'; + } + $sheet[$y][$x] = { + text => $cell->as_text, + format => $f, + rowspan => $cell->attr('rowspan'), + colspan => $cell->attr('colspan'), + }; + } + $x++; + } #for $cell + $y++; +} + +$y = 0; +foreach my $row (@sheet) { + $x = 0; + my $t_row = 1 if($row->[0]->{'text'} eq 'Total'); + foreach my $cell (@$row) { + if ($cell) { + my $f = $cell->{format}; + if ($cell->{rowspan} > 1 or $cell->{colspan} > 1) { + my $range = xl_range_formula( + 'Taxreport', + $y, + $y - 1 + ($cell->{rowspan} || 1), + $x, + $x - 1 + ($cell->{colspan} || 1) + ); + $ws->merge_range($range, $cell->{text}, $format{"m_$f"}); + } + else { + $f = "t_$f" if $t_row; + $ws->write($y, $x, $cell->{text}, $format{$f}); + } + } #if $cell + $x++; + } + $y++; +} + +for my $x (0..scalar(@widths)-1) { + $ws->set_column($x, $x, $widths[$x]); +} + +$workbook->close; + +http_header('Content-Type' => 'application/vnd.ms-excel'); +http_header('Content-Disposition' => 'attachment;filename="report_tax.xls"'); +</%init> diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 803b7d48f..93e5b51c9 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -10,35 +10,45 @@ ) ) %> +<TD ALIGN="right"> +Download full results<BR> +as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel spreadsheet</A> +</TD> <% include('/elements/table-grid.html') %> <TR> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH> <TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=9>Sales</TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Rate</TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax owed</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Rate</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax owed</TH> % unless ( $cgi->param('show_taxclasses') ) { - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax invoiced</TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax credited</TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax collected</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax invoiced</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax credited</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax collected</TH> % } </TR> <TR> - <TH CLASS="grid" BGCOLOR="#cccccc">Total</TH> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt customer)</FONT></TH> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt package)</FONT></TH> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(monthly exemption)</FONT></TH> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Taxable</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Total</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Taxable</TH> + </TR> + + <TR> + <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(tax-exempt customer)</FONT></TH> + <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(tax-exempt package)</FONT></TH> + <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(monthly exemption)</FONT></TH> </TR> % my $bgcolor1 = '#eeeeee'; -- 2.11.0