<%doc>
Simple display front-end for reports that produce some kind of data table,
which the user can request as an Excel spreadsheet. /elements/header.html
and /elements/footer.html are included automatically, so don't include them
again.
Usage:
<& elements/grid-report.html,
title => 'My Report',
rows => [
{ header => 1, },
...
],
cells => [
[ # row 0
{ value => '123.45',
# optional
format => 'money',
header => 1,
rowspan => 2,
colspan => 3,
class => 'highlight',
},
...
],
],
head => q[Thing to insert before the table
],
foot => q[That's all folks!].
&>
% if ( $cgi->param('_type') =~ /(xls)$/ ) {
<%perl>
# egregious false laziness w/ search/report_tax-xls.cgi
# and search/customer_cdr_profit.html
my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
my $filename = $cgi->url(-relative => 1);
$filename =~ s/\.html$//;
$filename .= $format->{extension};
http_header('Content-Type' => $format->{mime_type});
http_header('Content-Disposition' => qq!attachment;filename="$filename"!);
my $output = '';
my $XLS = IO::String->new($output);
my $workbook = $format->{class}->new($XLS)
or die "Error opening .xls file: $!";
my $worksheet = $workbook->add_worksheet('Summary');
my %format = (
header => {
size => 11,
bold => 1,
align => 'center',
valign => 'vcenter',
text_wrap => 1,
},
money => {
size => 11,
align => 'right',
valign => 'bottom',
num_format=> 8,
},
'' => {},
);
my %default = (
font => 'Calibri',
border => 1,
);
foreach (keys %format) {
my %f = (%default, %{$format{$_}});
$format{$_} = $workbook->add_format(%f);
$format{"m_$_"} = $workbook->add_format(%f);
}
my ($r, $c) = (0, 0);
# indices in these correspond to column positions
my @rowspans;
my @widths;
for my $row (@rows) {
$c = 0;
my $thisrow = shift @cells;
for my $cell (@$thisrow) {
# skip over cells that are occupied by rowspans above them
while ($rowspans[$c]) {
$rowspans[$c]--;
$c++;
}
# skip this cell if it's empty, also
next if !ref($cell);
# format name
my $f = '';
$f = 'header' if $row->{header} or $cell->{header};
$f = 'money' if $cell->{format} eq 'money';
if ( $cell->{rowspan} > 1 or $cell->{colspan} > 1 ) {
my $range = xl_range_formula(
'Summary',
$r, $r - 1 + ($cell->{rowspan} || 1),
$c, $c - 1 + ($cell->{colspan} || 1)
);
#warn "merging $range\n";
$worksheet->merge_range($range, $cell->{value}, $format{"m_$f"});
} else {
#warn "writing ".xl_rowcol_to_cell($r, $c)."\n";
$worksheet->write( $r, $c, $cell->{value}, $format{$f} );
}
# estimate column width, as in search-xls, but without date formats
my $width = length($cell->{value}) / ($cell->{colspan} || 1);
$width *= 1.1 if $f eq 'header';
$width++ if $f eq 'money'; # for money symbol
$width += 2; # pad it
for (1 .. ($cell->{colspan} || 1)) {
# adjust minimum widths to allow for this cell's contents
$widths[$c] = $width if $width > ($widths[$c] || 0);
# and if this cell has a rowspan, block off that many rows below it
if ( $cell->{rowspan} > 1 ) {
$rowspans[$c] = $cell->{rowspan} - 1;
}
$c++;
}
} #$cell
$r++;
} #$row
$c = 0;
for my $c (0 .. scalar(@widths) - 1) {
$worksheet->set_column($c, $c, $widths[$c]);
}
$workbook->close;
http_header('Content-Length' => length($output));
$m->print($output);
% } else {
<& /elements/header.html, $title &>
<% $head %>
% my $myself = $cgi->self_url;
Download full reports
as ">Excel spreadsheet
% # as ">CSV file # is this still needed?
% foreach my $rowinfo (@rows) {
{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>>
% my $thisrow = shift @cells;
% foreach my $cell (@$thisrow) {
% next if !ref($cell); # placeholders
% my $td = $cell->{header} ? 'th' : 'td';
% my $style = '';
% $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1;
% $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1;
% $style .= ' class="' . $cell->{class} . '"' if $cell->{class};
<<%$td%><%$style%>><% $cell->{value} |h %>>
% }
% }
<% $foot %>
<& /elements/footer.html &>
% }
<%args>
$title
@rows
@cells
$head => ''
$foot => ''