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
8 This element defines "total", "shaded", and "totalshaded" CSS classes. For
9 anything else, insert a <style> element via the 'head' argument.
13 <& elements/grid-report.html,
32 head => q[<div>Thing to insert before the table</div>],
33 foot => q[<span>That's all folks!</span>].
36 % if ( $cgi->param('_type') =~ /(xls)$/ ) {
38 # egregious false laziness w/ search/report_tax-xls.cgi
39 # and search/customer_cdr_profit.html
40 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
41 my $filename = $cgi->url(-relative => 1);
42 $filename =~ s/\.html$//;
43 $filename .= $format->{extension};
44 http_header('Content-Type' => $format->{mime_type});
45 http_header('Content-Disposition' => qq!attachment;filename="$filename"!);
48 my $XLS = IO::String->new($output);
49 my $workbook = $format->{class}->new($XLS)
50 or die "Error opening .xls file: $!";
52 my $worksheet = $workbook->add_worksheet('Summary');
74 foreach (keys %format) {
75 my %f = (%default, %{$format{$_}});
76 $format{$_} = $workbook->add_format(%f);
77 $format{"m_$_"} = $workbook->add_format(%f);
81 # indices in these correspond to column positions
87 my $thisrow = shift @cells;
88 for my $cell (@$thisrow) {
89 # skip over cells that are occupied by rowspans above them
90 while ($rowspans[$c]) {
95 # skip this cell if it's empty, also
99 $f = 'header' if $row->{header} or $cell->{header};
100 $f = 'money' if $cell->{format} eq 'money';
101 if ( $cell->{rowspan} > 1 or $cell->{colspan} > 1 ) {
102 my $range = xl_range_formula(
104 $r, $r - 1 + ($cell->{rowspan} || 1),
105 $c, $c - 1 + ($cell->{colspan} || 1)
107 #warn "merging $range\n";
108 $worksheet->merge_range($range, $cell->{value}, $format{"m_$f"});
110 #warn "writing ".xl_rowcol_to_cell($r, $c)."\n";
111 $worksheet->write( $r, $c, $cell->{value}, $format{$f} );
114 # estimate column width, as in search-xls, but without date formats
115 my $width = length($cell->{value}) / ($cell->{colspan} || 1);
116 $width *= 1.1 if $f eq 'header';
117 $width++ if $f eq 'money'; # for money symbol
118 $width += 2; # pad it
120 for (1 .. ($cell->{colspan} || 1)) {
121 # adjust minimum widths to allow for this cell's contents
122 $widths[$c] = $width if $width > ($widths[$c] || 0);
124 # and if this cell has a rowspan, block off that many rows below it
125 if ( $cell->{rowspan} > 1 ) {
126 $rowspans[$c] = $cell->{rowspan} - 1;
135 for my $c (0 .. scalar(@widths) - 1) {
136 $worksheet->set_column($c, $c, $widths[$c]);
140 http_header('Content-Length' => length($output));
144 % unless ( $suppress_header ) {
145 <& /elements/header.html, $title &>
148 % my $myself = $cgi->self_url;
149 % unless ( $suppress_header ) {
150 <P ALIGN="right" CLASS="noprint">
151 Download full reports<BR>
152 as <A HREF="<% "$myself;_type=xls" %>">Excel spreadsheet</A><BR>
155 <style type="text/css">
157 background-color: #f8f8f8;
158 border: 1px solid #999999;
164 .total { background-color: #f5f6be; }
165 .shaded { background-color: #c8c8c8; }
166 .totalshaded { background-color: #bfc094; }
168 <table class="<% $table_class %>" width="<% $table_width %>" cellspacing=0>
169 % foreach my $rowinfo (@rows) {
170 <tr<% $rowinfo->{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>>
171 % my $thisrow = shift @cells;
172 % foreach my $cell (@$thisrow) {
173 % next if !ref($cell); # placeholders
174 % my $td = $cell->{header} ? 'th' : 'td';
176 % $style .= " rowspan=".$cell->{rowspan}
177 % if exists $cell->{rowspan} && $cell->{rowspan} > 1;
178 % $style .= " colspan=".$cell->{colspan}
179 % if exists $cell->{colspan} && $cell->{colspan} > 1;
180 % $style .= ' class="' . $cell->{class} . '"' if $cell->{class};
181 % if ($cell->{bypass_filter}) {
182 <<%$td%><%$style%>><% $cell->{value} %></<%$td%>>
184 <<%$td%><%$style%>><% $cell->{value} |h %></<%$td%>>
191 % unless ( $suppress_footer ) {
192 <& /elements/footer.html &>
201 $table_width => "100%"
202 $table_class => "report"
203 $suppress_header => undef
204 $suppress_footer => undef