[R-gregmisc-users] SF.net SVN: r-gregmisc:[1879] trunk/gdata/inst/perl/xls2csv.pl

[<wa...@us...>]

Revision: 1879
          http://sourceforge.net/p/r-gregmisc/code/1879
Author:   warnes
Date:     2014-08-28 04:55:11 +0000 (Thu, 28 Aug 2014)
Log Message:
-----------
Handle Excel files created on the Mac, where by default Excel uses
1904-01-01 as the baseline for dates, rather than the usual 1900-01-01.

Modified Paths:
--------------
    trunk/gdata/inst/perl/xls2csv.pl

Modified: trunk/gdata/inst/perl/xls2csv.pl
===================================================================
--- trunk/gdata/inst/perl/xls2csv.pl	2014-08-28 03:08:23 UTC (rev 1878)
+++ trunk/gdata/inst/perl/xls2csv.pl	2014-08-28 04:55:11 UTC (rev 1879)
@@ -20,7 +20,7 @@
 my(
    $HAS_Spreadsheet_ParseExcel,
    $HAS_Compress_Raw_Zlib,
-   $HAS_Spreadsheet_XLSX
+   $HAS_Spreadsheet_ParseXLSX
   ) = check_modules_and_notify();
 
 # declare some varibles local
@@ -29,7 +29,7 @@
    $filename, $volume, $directories, $whoami,
    $sep, $sepName, $sepLabel, $sepExt, 
    $skipBlankLines, %switches,
-   $parser, $oBook
+   $parser, $oBook, $formatter
 );
 
 ##
@@ -135,7 +135,8 @@
 my $oExcel;
 my $oBook;
 
-$oExcel = new Spreadsheet::ParseExcel;
+$oExcel    = new Spreadsheet::ParseExcel;
+$formatter = Spreadsheet::ParseExcel::FmtDefault->new();
 
 open(FH, "<$ARGV[0]") or die "Unable to open file '$ARGV[0]'.\n";
 close(FH);
@@ -240,14 +241,27 @@
 
        for(my $col = $mincol; $col <= $maxcol; $col++)
          {
-           my $cell = $sheet->{Cells}[$row][$col];
+           my $cell   = $sheet->{Cells}[$row][$col];
+	   my $format = $formatter->FmtString($cell, $oBook);
 	   if( defined($cell) )
 	      {
-		$_=$cell->Value; #{Val};
+		  if ($cell->type() eq "Date" && $oBook->{Flag1904} )
+		    {
+			$_ = ExcelFmt($format,
+				      $cell->unformatted(),
+				      $oBook->{Flag1904});
+		    }
+		  else
+		    {  
+		      $_=$cell->value();
+		    }
 
 		# convert '#NUM!' strings to missing (empty) values
 		s/#NUM!//;
 
+		# convert "#DIV/0!" strings to missing (emtpy) values
+                s|#DIV/0!||;
+
 		# escape double-quote characters in the data since
 		# they are used as field delimiters
 		s/\"/\\\"/g;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.