Re^2: Excel to CSV datetime conversion- format changes

[runrig]

Excel format handling is (to me) a twisty maze of passages. There's a map of format codes to default formats, which can change depending on locale (see the various Spreadsheet::ParseExcel::Fmt* libraries in the distribution). You may need to debug to see where things go off. As a workaround, you can use the ExcelFmt() function from Spreadsheet::ParseExcel::Utility in the distribution to format these cells (feeding it the value from calling the cell's unformatted() method rather than value()). You can also try posting the issue with the problem spreadsheet at the bug tracker (as indicated in the distribution). The maintainer may get around to looking at it.

[Anonymous Monk]

Thanks for the reply. I have seen ExcelFmt() function but I am not able to utilize in the current code. Can you please help me where to call that ExcelFmt in the line of code.

[runrig]

if ($something) {
  print ExcelFmt('dd/mm/yyyy hh:mm', $eSheet->{Cells}[$row][$column]->
+unformatted()) . ","
} else {
  print $eSheet->{Cells}[$row][$column]->Value . ",";
}

You'll have to fill in the '$something' yourself, and I'm not absolutely sure about the format I've specified, and you may also need an 'elsif ($something_else)' block to print the format without the hours and minutes.

[vsmeruga]

I modified code as below and got error as

Global symbol "$cell" requires explicit package name at ./perl_excel_xls2n.pl line 28. Global symbol "$cell" requires explicit package name at ./perl_excel_xls2n.pl line 28. Global symbol "$value" requires explicit package name at ./perl_excel_xls2n.pl line 30. Global symbol "$cell" requires explicit package name at ./perl_excel_xls2n.pl line 30. Execution of ./perl_excel_xls2n.pl aborted due to compilation errors.

#!/usr/bin/perl -w
# For each tab (worksheet) in a file (workbook),
# spit out columns separated by ",",
# and rows separated by c/r.

use Spreadsheet::ParseExcel::Utility qw(ExcelFmt);

use strict;
my $file='output2.csv';
my $datefmt = 'dd/mm/yyyy';    # ISO 8601

open STDOUT, ">", $file or die "$0: open: $!";
open STDERR, ">&STDOUT"        or die "$0: dup: $!";

my $filename = shift || "/home/etc/srcfile.xls";
my $e = new Spreadsheet::ParseExcel;
my $eBook = $e->Parse($filename);
my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);

foreach my $sheet (0 .. $sheets - 1) {
    $eSheet = $eBook->{Worksheet}[$sheet];
    $sheetName = $eSheet->{Name};
    #print "#Worksheet $sheet: $sheetName\n";
    next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{Max
+Col})));
    foreach my $row ($eSheet->{MinRow} .. $eSheet->{MaxRow}) {
        foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) {
           if (defined $cell->{Type} && $cell->{Type} eq 'Date')
            {
               $value = ExcelFmt($datefmt, $cell->{Val});
            }
            if (defined $eSheet->{Cells}[$row][$column])
            {
                print $eSheet->{Cells}[$row][$column]->Value . ",";
            } else {
                print ",";
            }
        }
        print "\n";
    }
}