/[SQL2XLS]/sql2xlsx.cgi
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /sql2xlsx.cgi

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 26 by dpavlin, Tue Dec 28 13:35:57 2010 UTC revision 27 by dpavlin, Tue Dec 28 13:56:16 2010 UTC
# Line 64  Dobrica Pavlinusic, dpavlin@rot13.org, L Line 64  Dobrica Pavlinusic, dpavlin@rot13.org, L
64    
65  =cut  =cut
66    
67  use Spreadsheet::WriteExcel;  use Excel::Writer::XLSX;
68  use DBI;  use DBI;
69  use CGI::Carp qw(fatalsToBrowser);  use CGI::Carp qw(fatalsToBrowser);
70  use Encode qw/decode/;  use Encode qw/decode/;
# Line 74  our $dsn      = 'DBI:Pg:dbname='; Line 74  our $dsn      = 'DBI:Pg:dbname=';
74  our $database = 'template1';  our $database = 'template1';
75  our $user     = 'dpavlin';  our $user     = 'dpavlin';
76  our $passwd   = '';  our $passwd   = '';
77  our $path     = 'sql_reports.xls';  our $path     = 'sql_reports.xlsx';
78    
79  our $db_encoding     = 'iso-8859-2';  our $db_encoding     = 'iso-8859-2';
80  our $xls_date_format = 'dd.mm.yyyy';  our $xls_date_format = 'dd.mm.yyyy';
81    
82  our $debug = 1;  our $debug = $ENV{DEBUG} || 0;
83    
84  my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';  my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
85  $sql_dir =~ s,/[^/]+$,,;  $sql_dir =~ s,/[^/]+$,,;
# Line 109  my $workbook; Line 109  my $workbook;
109  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
110          # use as cgi script          # use as cgi script
111          print "Content-type: application/vnd.ms-excel\n\n";          print "Content-type: application/vnd.ms-excel\n\n";
112          $workbook = Spreadsheet::WriteExcel->new("-");          $workbook = Excel::Writer::XLSX->new("-");
113  } else {  } else {
114          # Create a new Excel workbook          # Create a new Excel workbook
115          $workbook = Spreadsheet::WriteExcel->new( $path );          $path =~ s{\.xls$}{\.xlsx};
116            $workbook = Excel::Writer::XLSX->new( $path );
117          warn "Creating XLS file $path\n";          warn "Creating XLS file $path\n";
118  }  }
119    
# Line 151  foreach my $sql_file (@sql_files) { Line 152  foreach my $sql_file (@sql_files) {
152    
153          # Add a worksheet          # Add a worksheet
154          warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;          warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
155          my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );          my $worksheet = $workbook->add_worksheet( substr($sheet_name,0,31) );
156    
157          print STDERR "working on $sql_file\n" if ($debug);          print STDERR "working on $sql_file\n" if ($debug);
158    
# Line 180  foreach my $sql_file (@sql_files) { Line 181  foreach my $sql_file (@sql_files) {
181          if ($comment) {          if ($comment) {
182    
183                  #  Add and define a format                  #  Add and define a format
184                  my $fmt_comment = $workbook->addformat();    # Add a format                  my $fmt_comment = $workbook->add_format();    # Add a format
185                  $fmt_comment->set_bold();                  $fmt_comment->set_bold();
186    
187                  $comment =~ s/^\s+//;                  $comment =~ s/^\s+//;
# Line 190  foreach my $sql_file (@sql_files) { Line 191  foreach my $sql_file (@sql_files) {
191                  $row+=2;                  $row+=2;
192          }          }
193    
194          my $fmt_header = $workbook->addformat();    # Add a format          my $fmt_header = $workbook->add_format();    # Add a format
195          $fmt_header->set_italic();          $fmt_header->set_italic();
196    
197          foreach my $sql ( split(/;/, $full_sql ) ) {          foreach my $sql ( split(/;/, $full_sql ) ) {
# Line 231  foreach my $sql_file (@sql_files) { Line 232  foreach my $sql_file (@sql_files) {
232                  }                  }
233    
234                  $row++; # separete queries by one row                  $row++; # separete queries by one row
235                    warn "# row $row\n";
236          }          }
237  }  }
238    
Legend:
Removed from v.26  
changed lines
  Added in v.27
  ViewVC Help
Powered by ViewVC 1.1.26