From 8ce2c1f11378b22966ec536f11898b4708a40237 Mon Sep 17 00:00:00 2001
From: Mark Wells <mark@freeside.biz>
Date: Fri, 13 Jul 2012 15:33:43 -0700
Subject: [PATCH] XLSX format for spreadsheet download, #17971

---
 FS/FS/Conf.pm                              | 11 ++++++++++
 FS/FS/Mason.pm                             |  3 +++
 FS/FS/access_user.pm                       | 32 ++++++++++++++++++++++++++++++
 httemplate/pref/pref-process.html          |  3 ++-
 httemplate/pref/pref.html                  | 15 ++++++++++++++
 httemplate/search/elements/search-xls.html | 13 +++++++-----
 6 files changed, 71 insertions(+), 6 deletions(-)

diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index b4ce0baaa..f081c1796 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -5204,6 +5204,17 @@ and customer address. Include units.',
     'description' => 'If set, automatically log users out of the backoffice after this many minutes.',
     'type'       => 'text',
   },
+  
+  {
+    'key'         => 'spreadsheet_format',
+    'section'     => 'UI',
+    'description' => 'Default format for spreadsheet download.',
+    'type'        => 'select',
+    'select_hash' => [
+      'XLS' => 'XLS (Excel 97/2000/XP)',
+      'XLSX' => 'XLSX (Excel 2007+)',
+    ],
+  },
 
   { key => "apacheroot", section => "deprecated", description => "<b>DEPRECATED</b>", type => "text" },
   { key => "apachemachine", section => "deprecated", description => "<b>DEPRECATED</b>", type => "text" },
diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index e26a4b747..51edd97cc 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -91,6 +91,9 @@ if ( -e $addl_handler_use_file ) {
   use Text::CSV_XS;
   use Spreadsheet::WriteExcel;
   use Spreadsheet::WriteExcel::Utility;
+  use Excel::Writer::XLSX;
+  use Excel::Writer::XLSX::Utility;
+
   use Business::CreditCard 0.30; #for mask-aware cardtype()
   use NetAddr::IP;
   use Net::Ping;
diff --git a/FS/FS/access_user.pm b/FS/FS/access_user.pm
index 5d5cc126c..4d72c2ee7 100644
--- a/FS/FS/access_user.pm
+++ b/FS/FS/access_user.pm
@@ -511,6 +511,38 @@ sub default_customer_view {
 
 }
 
+=item spreadsheet_format
+
+Returns a hashref of this user's Excel spreadsheet download settings:
+'extension' (xls or xlsx), 'class' (Spreadsheet::WriteExcel or
+Excel::Writer::XLSX), and 'mime_type'.
+
+=cut
+
+my %formats = (
+  XLS => {
+    extension => '.xls',
+    class => 'Spreadsheet::WriteExcel',
+    mime_type => 'application/vnd.ms-excel',
+  },
+  XLSX => {
+    extension => '.xlsx',
+    class => 'Excel::Writer::XLSX',
+    mime_type => # it's on wikipedia, it must be true
+      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
+  }
+);
+
+sub spreadsheet_format {
+  my $self = shift;
+
+  my $f = $self->option('spreadsheet_format') 
+       || $conf->config('spreadsheet_format')
+       || 'XLS';
+
+  $formats{$f};
+}
+
 =item is_system_user
 
 Returns true if this user has the name of a known system account.  These 
diff --git a/httemplate/pref/pref-process.html b/httemplate/pref/pref-process.html
index bd6bb860a..932cf1a0a 100644
--- a/httemplate/pref/pref-process.html
+++ b/httemplate/pref/pref-process.html
@@ -48,7 +48,8 @@ unless ( $error ) { # if ($access_user) {
   my %param = $access_user->options;
 
   #XXX autogen
-  my @paramlist = qw( locale menu_position default_customer_view mobile_menu
+  my @paramlist = qw( locale menu_position default_customer_view 
+                      spreadsheet_format mobile_menu
                       disable_html_editor disable_enter_submit_onetimecharge
                       email_address
                       snom-ip snom-username snom-password
diff --git a/httemplate/pref/pref.html b/httemplate/pref/pref.html
index 8e56355db..9ebf2f1ba 100644
--- a/httemplate/pref/pref.html
+++ b/httemplate/pref/pref.html
@@ -75,6 +75,21 @@ Interface
       </SELECT>
     </TD>
   </TR>
+  
+  <TR>
+    <TH ALIGN="right">Spreadsheet download format: </TH>
+    <TD COLSPAN=2>
+      <SELECT NAME="spreadsheet_format">
+%       my $xls =  $curuser->option('spreadsheet_format') eq 'XLS';
+%       my $xlsx = $curuser->option('spreadsheet_format') eq 'XLSX';
+        <OPTION VALUE=""></OPTION>
+        <OPTION VALUE="XLS"<%  $xls ? 'SELECTED' : '' %>>XLS (Excel 97/2000/XP)
+        </OPTION>
+        <OPTION VALUE="XLSX"<% $xlsx ? 'SELECTED' : ''%>>XLSX (Excel 2007+)
+        </OPTION>
+      </SELECT>
+    </TD>
+  </TR>
  
   <TR>
     <TH ALIGN="right" COLSPAN=1>Disable HTML editor for customer notes: </TH>
diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html
index a3a8226c5..c862dfbbe 100644
--- a/httemplate/search/elements/search-xls.html
+++ b/httemplate/search/elements/search-xls.html
@@ -7,14 +7,17 @@ my $header = $args{'header'};
 my $rows   = $args{'rows'};
 my %opt    = %{ $args{'opt'} };    
 
+my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
+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 +25,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));
 
-- 
2.11.0