PERL   17

csv to excel

Guest on 10th July 2022 07:55:18 PM

  1. #!/usr/bin/perl
  2.  
  3. # csv_to_excel.cgi - given CSV file convert to XLS and output for download
  4. #
  5. # created: 2013-07-27
  6. # author: darko.prelec@gmail.com
  7.  
  8. use 5.010;
  9. use strict;
  10. #use warnings;
  11. use utf8;
  12.  
  13. our $VERSION = '1.0';
  14.  
  15. use Carp;
  16. use CGI;
  17. use Text::CSV;
  18. use Excel::Writer::XLSX;
  19. use Scalar::Util qw/looks_like_number/;
  20.  
  21. $SIG{__DIE__} = sub {
  22.     use Carp;
  23.     my $err = shift;
  24.     print "<pre>";
  25.     print "ERROR: $err";
  26.     print Carp::longmess();
  27.     print "</pre>";
  28. };
  29.  
  30. our $FS   = ';';
  31. our $ROOT;
  32. our $CGI  = CGI->new;
  33.  
  34.  $ROOT   = $ENV{'DOCUMENT_ROOT'};
  35.  
  36.  
  37. sub http_die {
  38.     my $msg = shift;
  39.  
  40.     print $CGI->header("-content-type" => "text/html");
  41.     print "<h2>Error!</h2>\n";
  42.     print "<b>$!</b>\n";
  43.     print "<b>$Excel::Writer::XLSX::VERSION</b>\n";
  44.     print "<pre>\n$msg\n</pre>\n";
  45.  
  46.     print "<pre>";
  47.     for my $k (sort keys %ENV) {
  48.         print "$k => $ENV{$k}\n";
  49.     }
  50.     print "</pre>";
  51.  
  52.     print "<pre>";
  53.     for my $k (sort keys %INC) {
  54.         print "$k => $INC{$k}\n";
  55.     }
  56.     print "</pre>\n";
  57.  
  58.     print STDERR "csv2xls.pl error $msg";
  59.  
  60.     exit;
  61. }
  62.  
  63. sub convert_csv_to_xlsx {
  64.     my $file = shift;
  65.     my $content;
  66.  
  67.     open my $fh_csv,   "<", $file     or http_die("Cannot open $file: $!");
  68.     open my $fh_excel, ">", \$content or http_die("Cannot init content: $!");
  69.  
  70.     my $workbook = Excel::Writer::XLSX->new($fh_excel)
  71.       or http_die("Cannot init Excel::Writer: $!");
  72.  
  73.     my $worksheet  = $workbook->add_worksheet;
  74.     my $num_format = $workbook->add_format;
  75.     $num_format->set_num_format("0.00");
  76.  
  77.     my $csv = Text::CSV->new({ binary => 1, sep_char => $FS })
  78.       or http_die("Text::CSV died: " . Text::CSV->error_diag);
  79.  
  80.     my $count = 0;
  81.     my @format_fields;
  82.     while (my $row = $csv->getline($fh_csv)) {
  83.         if ($count == 0) {
  84.             for my $i (0 .. $#$row) {
  85.                 if ($row->[$i] =~ /(Koli.ina|Iznos|Osnovica za PDV)/) {
  86.                     push @format_fields, $i;
  87.                 }
  88.             }
  89.  
  90.             # skip warnings about non-numeric value
  91.             {no warnings; $worksheet->write($count, 0, $row);};
  92.  
  93.             $count++;
  94.             next;
  95.         }
  96.  
  97.         for my $i (0 .. $#$row) {
  98.             if (looks_like_number($row->[$i])
  99.                 and grep {$i == $_} @format_fields)
  100.             {
  101.                 $worksheet->write_number($count, $i, $row->[$i], $num_format);
  102.             }
  103.             else {
  104.                 $worksheet->write($count, $i, $row->[$i]);
  105.             }
  106.         }
  107.  
  108.         $count++;
  109.     }
  110.  
  111.     $workbook->close;
  112.     return $content;
  113. }
  114.  
  115. # only for debugging
  116. #$SIG{__DIE__} = sub {
  117. #    http_die(Carp::longmess(@_));
  118. #};
  119.  
  120. my $file = $CGI->param('file');
  121.  
  122. if (not defined $file) {
  123.     print $CGI->header("-content-type" => "text/html");
  124.     print "<h2>No file specified</h2>";
  125.     print "No input file specified.";
  126.     exit;
  127. }
  128. elsif ($file !~ m|^/mercur[^\/.]*/([^\/]+)\.csv$|
  129.     or not -e "$ROOT$file")
  130. {
  131.     print $CGI->header(
  132.         "-content-type" => "text/html",
  133.         "-status"       => "404 Not Found"
  134.     );
  135.     print "<h2>Error: File not found or invalid file specified.</h2>";
  136.     exit;
  137. }
  138.  
  139. my $filename     = "$1.xlsx";
  140. my $xlsx_content = convert_csv_to_xlsx("$ROOT$file");
  141.  
  142. if (length($xlsx_content) > 0) {
  143.     print "X-Converter-Agent: csv_to_excel.pl (v.$VERSION)\n";
  144.     print "Content-length: " . length($xlsx_content) . "\n";
  145.     print "Content-type: application/vnd.ms-excel\n";
  146.     print "Content-Disposition:attachment;filename=$filename\n\n";
  147.     print $xlsx_content;
  148. }
  149. else {
  150.     print $CGI->header("-content-type" => "text/html");
  151.     print "<h2>Error: failed to generate Excel file</h2>";
  152. }

Raw Paste

Login or Register to edit or fork this paste. It's free.