#!/usr/bin/perl -w
#
# prerequisiti da installare:
# perl
# libole-storage-lite-perl
# libspreasheet-writeexcel-perl
# libtext-csv-perl
#

use strict;
use Spreadsheet::WriteExcel::Big;
use Text::CSV_XS;

# Check for valid number of arguments
if (($#ARGV < 1) || ($#ARGV > 2)) {
   die("Usage: c2x.pl csvfile.txt newfile.xls\n");
}

# Open the Tab Separated Variable file
open (CSVFILE, $ARGV[0]) or die "$ARGV[0]: $!";

# Create a new Excel workbook
my $workbook  = Spreadsheet::WriteExcel::Big->new($ARGV[1]);
my $worksheet = $workbook->add_worksheet();
my $date_format = $workbook->add_format(num_format => 'dd/mm/yyyy');
my $ora_format = $workbook->add_format(num_format => 'HH:MM:SS');

my $dateval = "";
my $lun = 0;

# Row and column are zero indexed
my $row = 0;

while (<CSVFILE>) {
  # print $_;
  chomp($_);
  my @values = split('\t', $_);
  my $col = 0;
  foreach my $val (@values) {
    # print "$val\n";
    $lun = length($val);
    if ($lun > 0) {
      if (substr($val,0,1) eq '"') {
        # e' una stringa
        $worksheet->write_string($row, $col, substr($val,1,($lun-2)));
      }
      elsif (($lun > 2) and (substr($val,2,1) eq '/')) {
        # e' una data ...
        if ($lun == 8) {
          # ... lunga 8 oppure ...
          if (substr($val,6,2) le '15') {
            $dateval = 
            '20' . substr($val,6,2) . '-' . substr($val,3,2) . '-' . substr($val,0,2) . 'T';
          }
          else {
            $dateval = 
            '19' . substr($val,6,2) . '-' . substr($val,3,2) . '-' . substr($val,0,2) . 'T';
          }
        }
        else {
          # ... lunga 10
          $dateval = substr($val,6,4) . '-' . substr($val,3,2) . '-' . substr($val,0,2) . 'T';
        }
        $worksheet->write_date_time($row, $col, $dateval, $date_format);
      }
      elsif (($lun > 2) and (substr($val,2,1) eq ':')) {
	     # e' un orario
        my $oraval = 'T' . $val;
        $worksheet->write_date_time($row, $col, $oraval, $ora_format);
      }
      else {
        # e' un numero
        $worksheet->write($row, $col, $val);
      }
    } 
    $col++;
  }
  $row++;
}