diff options
Diffstat (limited to 'httemplate/search/elements/search-xls.html')
| -rw-r--r-- | httemplate/search/elements/search-xls.html | 38 |
1 files changed, 32 insertions, 6 deletions
diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html index 0b5636c0e..09dbe46e0 100644 --- a/httemplate/search/elements/search-xls.html +++ b/httemplate/search/elements/search-xls.html @@ -7,14 +7,20 @@ my $header = $args{'header'}; my $rows = $args{'rows'}; my %opt = %{ $args{'opt'} }; +my $override = scalar(@$rows) >= 65536 ? 'XLSX' : ''; + +my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override); + +my $filename = $opt{'name'} || PL($opt{'name_singular'}); +$filename .= $format->{extension}; + #http_header('Content-Type' => 'application/excel' ); #eww #http_header('Content-Type' => 'application/msexcel' ); #alas #http_header('Content-Type' => 'application/x-msexcel' ); #? #http://support.microsoft.com/kb/199841 -http_header('Content-Type' => 'application/vnd.ms-excel' ); -http_header('Content-Disposition' => - 'attachment;filename="'.($opt{'name'} || PL($opt{'name_singular'}) ).'.xls"'); +http_header('Content-Type' => $format->{mime_type} ); +http_header('Content-Disposition' => qq!attachment;filename="$filename"! ); #http://support.microsoft.com/kb/812935 #http://support.microsoft.com/kb/323308 @@ -22,8 +28,8 @@ $HTML::Mason::Commands::r->headers_out->{'Cache-control'} = 'max-age=0'; my $data = ''; my $XLS = new IO::Scalar \$data; -my $workbook = Spreadsheet::WriteExcel->new($XLS) - or die "Error opening .xls file: $!"; +my $workbook = $format->{class}->new($XLS) + or die "Error opening Excel file: $!"; my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31)); @@ -42,14 +48,18 @@ my $default_format = $workbook->add_format(locked => 0); my %money_format; my $money_char = FS::Conf->new->config('money_char') || '$'; +my %date_format; +xl_parse_date_init(); + my $writer = sub { # Wrapper for $worksheet->write. # Do any massaging of the value/format here. my ($r, $c, $value, $format) = @_; - if ( $value =~ /^\Q$money_char\E(\d+\.?\d*)$/ ) { + if ( $value =~ /^\Q$money_char\E(-?\d+\.?\d*)$/ ) { # Currency: strip the symbol, clone the requested format, # and format it for currency $value = $1; +# warn "formatting $value as money\n"; if ( !exists($money_format{$format}) ) { $money_format{$format} = $workbook->add_format(); $money_format{$format}->copy($format); @@ -57,6 +67,22 @@ my $writer = sub { } $format = $money_format{$format}; } + elsif ( $value =~ /^([A-Z][a-z]{2}) (\d{2}) (\d{4})$/ ) { + # Date: convert the value to an Excel date number and set + # the format + $value = xl_parse_date($value); +# warn "formatting $value as date\n"; + if ( !exists($date_format{$format}) ) { + $date_format{$format} = $workbook->add_format(); + $date_format{$format}->copy($format); + $date_format{$format}->set_num_format('mmm dd yyyy'); + } + $format = $date_format{$format}; + } + else { + # String: replace line breaks with newlines + $value =~ s/<BR>/\n/gi; + } $worksheet->write($r, $c, $value, $format); }; |