3 my $htmldoc = include('report_tax.cgi');
5 my ($title) = ($htmldoc =~ /<title>\s*(.*)\s*<\/title>/i);
7 # do this first so we can override the format if it's too many rows
8 # attribs option: how to locate the table? It's the only one with class="grid".
9 my $te = HTML::TableExtract->new(attribs => {class => 'grid'});
11 my $table = $te->first_table_found;
13 my $override = ($table->row_count >= 65536 ? 'XLSX' : '');
14 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override);
15 my $filename = 'report_tax'.$format->{extension};
17 http_header('Content-Type' => $format->{mime_type});
18 http_header('Content-Disposition' => qq!attachment;filename="$filename"! );
21 my $XLS = new IO::Scalar \$data;
22 my $workbook = $format->{class}->new($XLS)
23 or die "Error opening .xls file: $!";
25 # hardcoded formats, this could be handled better
26 my $light_gray = $workbook->set_custom_color(63, '#eeeeee');
71 bg_color => $light_gray,
75 18, (10.5, 3) x 6, 10.5, 10.5, 3, 10.5, 3, 10.5, 3, 10.5
77 foreach (keys(%format)) {
78 my %f = (%default, %{$format{$_}});
79 $format{$_} = $workbook->add_format(%f);
80 $format{"m_$_"} = $workbook->add_format(%f); # for merged cells
81 $format{"t_$_"} = $workbook->add_format(%f, bg_color => 'yellow'); # totals
83 my $ws = $workbook->add_worksheet('taxreport');
94 foreach my $row ($table->rows()) {
97 foreach my $cell (@$row) {
98 if ($cell and ref($cell) eq 'HTML::ElementTable::DataElement') {
100 if ( $cell->as_HTML =~ /font/i ) {
101 my ($el) = $cell->content_list;
102 $f = 'size'.$el->attr('size') if $el->attr('size');
104 elsif ( $cell->as_text =~ /^\$/ ) {
107 elsif ( $cell->tag eq 'th' ) {
114 text => $cell->as_text,
116 rowspan => $cell->attr('rowspan'),
117 colspan => $cell->attr('colspan'),
126 foreach my $row (@sheet) {
128 my $t_row = 1 if($row->[0]->{'text'} eq 'Total');
129 foreach my $cell (@$row) {
131 my $f = $cell->{format};
132 if ($cell->{rowspan} > 1 or $cell->{colspan} > 1) {
133 my $range = xl_range_formula(
136 $y - 1 + ($cell->{rowspan} || 1),
138 $x - 1 + ($cell->{colspan} || 1)
140 $ws->merge_range($range, $cell->{text}, $format{"m_$f"});
143 $f = "t_$f" if $t_row;
144 $ws->write($y, $x, $cell->{text}, $format{$f});
152 for my $x (0..scalar(@widths)-1) {
153 $ws->set_column($x, $x, $widths[$x]);