3 Simple display front-end for reports that produce some kind of data table,
4 which the user can request as an Excel spreadsheet. /elements/header.html
5 and /elements/footer.html are included automatically, so don't include them
10 <& elements/grid-report.html,
29 head => q[<div>Thing to insert before the table</div>],
30 foot => q[<span>That's all folks!</span>].
33 % if ( $cgi->param('_type') =~ /(xls)$/ ) {
35 # egregious false laziness w/ search/report_tax-xls.cgi
36 # and search/customer_cdr_profit.html
37 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
38 my $filename = $cgi->url(-relative => 1);
39 $filename =~ s/\.html$//;
40 $filename .= $format->{extension};
41 http_header('Content-Type' => $format->{mime_type});
42 http_header('Content-Disposition' => qq!attachment;filename="$filename"!);
45 my $XLS = IO::String->new($output);
46 my $workbook = $format->{class}->new($XLS)
47 or die "Error opening .xls file: $!";
49 my $worksheet = $workbook->add_worksheet('Summary');
71 foreach (keys %format) {
72 my %f = (%default, %{$format{$_}});
73 $format{$_} = $workbook->add_format(%f);
74 $format{"m_$_"} = $workbook->add_format(%f);
78 # indices in these correspond to column positions
84 my $thisrow = shift @cells;
85 for my $cell (@$thisrow) {
86 # skip over cells that are occupied by rowspans above them
87 while ($rowspans[$c]) {
92 # skip this cell if it's empty, also
96 $f = 'header' if $row->{header} or $cell->{header};
97 $f = 'money' if $cell->{format} eq 'money';
98 if ( $cell->{rowspan} > 1 or $cell->{colspan} > 1 ) {
99 my $range = xl_range_formula(
101 $r, $r - 1 + ($cell->{rowspan} || 1),
102 $c, $c - 1 + ($cell->{colspan} || 1)
104 #warn "merging $range\n";
105 $worksheet->merge_range($range, $cell->{value}, $format{"m_$f"});
107 #warn "writing ".xl_rowcol_to_cell($r, $c)."\n";
108 $worksheet->write( $r, $c, $cell->{value}, $format{$f} );
111 # estimate column width, as in search-xls, but without date formats
112 my $width = length($cell->{value}) / ($cell->{colspan} || 1);
113 $width *= 1.1 if $f eq 'header';
114 $width++ if $f eq 'money'; # for money symbol
115 $width += 2; # pad it
117 for (1 .. ($cell->{colspan} || 1)) {
118 # adjust minimum widths to allow for this cell's contents
119 $widths[$c] = $width if $width > ($widths[$c] || 0);
121 # and if this cell has a rowspan, block off that many rows below it
122 if ( $cell->{rowspan} > 1 ) {
123 $rowspans[$c] = $cell->{rowspan} - 1;
132 for my $c (0 .. scalar(@widths) - 1) {
133 $worksheet->set_column($c, $c, $widths[$c]);
137 http_header('Content-Length' => length($output));
141 <& /elements/header.html, $title &>
143 % my $myself = $cgi->self_url;
144 <P ALIGN="right" CLASS="noprint">
145 Download full reports<BR>
146 as <A HREF="<% "$myself;_type=xls" %>">Excel spreadsheet</A><BR>
147 % # as <A HREF="<% "$myself;_type=csv" %>">CSV file</A> # is this still needed?
149 <style type="text/css">
151 background-color: #f8f8f8;
152 border: 1px solid #999999;
158 .total * { background-color: #f5f6be; }
159 .shaded * { background-color: #c8c8c8; }
160 .totalshaded * { background-color: #bfc094; }
162 <table class="report" width="100%" cellspacing=0>
163 % foreach my $rowinfo (@rows) {
164 <tr<% $rowinfo->{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>>
165 % my $thisrow = shift @cells;
166 % foreach my $cell (@$thisrow) {
167 % next if !ref($cell); # placeholders
168 % my $td = $cell->{header} ? 'th' : 'td';
170 % $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1;
171 % $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1;
172 % $style .= ' class="' . $cell->{class} . '"' if $cell->{class};
173 <<%$td%><%$style%>><% $cell->{value} |h %></<%$td%>>
179 <& /elements/footer.html &>