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

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

Revision: 1391
          http://r-gregmisc.svn.sourceforge.net/r-gregmisc/?rev=1391&view=rev
Author:   warnes
Date:     2010-01-24 07:26:58 +0000 (Sun, 24 Jan 2010)

Log Message:
-----------
Complete changes to handle Excel 2007 'xlsx' files

Modified Paths:
--------------
    trunk/gdata/inst/perl/Spreadsheet/XLSX/Fmt2007.pm
    trunk/gdata/inst/perl/Spreadsheet/XLSX.pm
    trunk/gdata/inst/perl/xls2csv.pl

Modified: trunk/gdata/inst/perl/Spreadsheet/XLSX/Fmt2007.pm
===================================================================
--- trunk/gdata/inst/perl/Spreadsheet/XLSX/Fmt2007.pm	2010-01-24 05:36:58 UTC (rev 1390)
+++ trunk/gdata/inst/perl/Spreadsheet/XLSX/Fmt2007.pm	2010-01-24 07:26:58 UTC (rev 1391)
@@ -6,6 +6,7 @@
 package Spreadsheet::XLSX::Fmt2007;
 use strict;
 use warnings;
+use POSIX;
 
 use Spreadsheet::XLSX::Utility2007 qw(ExcelFmt);
 our $VERSION = '0.12'; # 
@@ -34,7 +35,7 @@
     0x14 => 'h:mm',
     0x15 => 'h:mm:ss',
     0x16 => 'm-d-yy h:mm',
-#0x17-0x24 -- Differs in Natinal
+#0x17-0x24 -- Differs in National
     0x25 => '(#,##0_);(#,##0)',
     0x26 => '(#,##0_);[RED](#,##0)',
     0x27 => '(#,##0.00);(#,##0.00)',
@@ -93,27 +94,49 @@
         if ($oCell->{Type} eq 'Numeric') {
 	    if($oCell->{Format}){
 		$sFmtStr=$oCell->{Format};
-	    } elsif(int($oCell->{Val}) != $oCell->{Val}) {
-                $sFmtStr = '0.00';
-            }
-            else {
+	    }
+	    # Integer
+	    elsif( isdigit($oCell->{Val}) ){
                 $sFmtStr = '0';
-            }
-        }
+	    }
+	    # Floating Point
+            else{
+                $sFmtStr = '0.000000000000000';
+	    }
+	  }
         elsif($oCell->{Type} eq 'Date') {
 	    if($oCell->{Format}){
 		$sFmtStr=$oCell->{Format};
-            } elsif(int($oCell->{Val}) <= 0) {
-                $sFmtStr = 'h:mm:ss';
-            }
+	      }
+	    # Fraction < 1 --> Time
+	    elsif(int($oCell->{Val}) <= 0){
+                $sFmtStr = 'hh:mm:ss';
+	      }
+	    # Whole number --> Date
+	    elsif(int($oCell->{Val}) != $oCell->{Val}){
+                $sFmtStr = 'hh:mm:ss';
+	      }
+	    # Otherwise both Date and Time
             else {
-                $sFmtStr = 'm-d-yy';
-            }
-        }
-        else {
+                $sFmtStr = 'mm-dd-yyyy hh:mm:ss';
+	      }
+	  }
+        elsif($oCell->{Type} eq 'Time') 
+	  {
+	    if($oCell->{Format})
+	      {
+		$sFmtStr=$oCell->{Format};
+	      }
+	    elsif(int($oCell->{Val}) <= 0) 
+	      {
+                $sFmtStr = 'hh:mm:ss';
+	      }
+	  }
+        else 
+	  {
             $sFmtStr = '@';
-        }
-    }
+	  }
+      }
     return $sFmtStr;
 }
 #------------------------------------------------------------------------------

Modified: trunk/gdata/inst/perl/Spreadsheet/XLSX.pm
===================================================================
--- trunk/gdata/inst/perl/Spreadsheet/XLSX.pm	2010-01-24 05:36:58 UTC (rev 1390)
+++ trunk/gdata/inst/perl/Spreadsheet/XLSX.pm	2010-01-24 07:26:58 UTC (rev 1391)
@@ -60,7 +60,8 @@
 			my $formatCode = $1 || '';
 			if ($formatCode eq $default || not($formatCode)){
 				if ($t1 == 9 || $t1==10){ $formatCode="0.00000%";}
-				elsif ($t1 == 14){ $formatCode="m-d-yy";}
+				elsif ($t1 == 14){ $formatCode="yyyy-mm-dd";}
+				elsif ($t1 == 20){ $formatCode="h:mm";}
 				else {
 					$formatCode="";
 				}
@@ -159,7 +160,7 @@
 				
 				$s   = m/t=\"s\"/      ?  1 : 0;
 				$s2  = m/t=\"str\"/    ?  1 : 0;
-				$sty = m/s="([0-9]+)"/ ? $1 : 0;
+				$sty = m/s="([0-9]+)"/ ?  $1 : 0;
 
 			}
 			elsif (/^<v/) {

Modified: trunk/gdata/inst/perl/xls2csv.pl
===================================================================
--- trunk/gdata/inst/perl/xls2csv.pl	2010-01-24 05:36:58 UTC (rev 1390)
+++ trunk/gdata/inst/perl/xls2csv.pl	2010-01-24 07:26:58 UTC (rev 1391)
@@ -2,11 +2,13 @@
 
 BEGIN {
 use File::Basename;
-unshift(@INC, dirname $0);
+# Add current path to perl library search path
+use lib dirname($0);
 }
 
 use strict;
 use Spreadsheet::ParseExcel;
+use Spreadsheet::XLSX;
 use POSIX;
 use File::Spec::Functions;
 
@@ -111,19 +113,32 @@
 ## open spreadsheet
 ##
 
-my $oExcel = new Spreadsheet::ParseExcel;
+my $oExcel;
+my $oBook;
 
+$oExcel = new Spreadsheet::ParseExcel;
+
 open(FH, "<$ARGV[0]") or die "Unable to open file '$ARGV[0]'.\n";
 close(FH);
 
 print "Loading '$ARGV[0]'...\n";
-my $oBook = $oExcel->Parse($ARGV[0]) or die "Error parsing file '$ARGV[0]'.\n";
+
+## First try as a Excel 2007+ 'xml' file
+eval
+  {
+    local $SIG{__WARN__} = sub {};
+    $oBook = Spreadsheet::XLSX -> new ($ARGV[0]);
+  };
+if($@)
+  {
+    $oBook = new Spreadsheet::ParseExcel->Parse($ARGV[0]) or \
+      die "Error parsing file '$ARGV[0]'.\n";
+  }
 print "Done.\n";
 
 print "\n";
-print "Orignal Filename: ", $oBook->{File} , "\n";
+print "Orignal Filename: ", $ARGV[0], "\n";
 print "Number of Sheets: ", $oBook->{SheetCount} , "\n";
-print "Author          : ", $oBook->{Author} , "\n";
 print "\n";
 
 ## Get list all worksheets in the file


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