excel.rb in roo-1.3.6

- old
+ new

@@ -4,10 +4,30 @@
   true
 rescue LoadError => e
   false
 end
 
+# The Spreadsheet library has a bug in handling Excel 
+# base dates so if the file is a 1904 base date then 
+# dates are off by a day. 1900 base dates work fine
+module Spreadsheet
+  module Excel
+    class Row < Spreadsheet::Row
+      def _date data # :nodoc:
+        return data if data.is_a?(Date)
+        date = @worksheet.date_base + data.to_i
+        if LEAP_ERROR > @worksheet.date_base
+          date -= 1
+        end
+        date
+      end
+      public :_datetime
+    end
+  end
+end
+
+
 # ruby-spreadsheet has a font object so we're extending it 
 # with our own functionality but still providing full access
 # to the user for other font information
 module ExcelFontExtensions
   def bold?(*args)
@@ -53,15 +73,11 @@
       @filename = filename
       unless File.file?(@filename)
         raise IOError, "file #{@filename} does not exist"
       end
       @workbook = Spreadsheet.open(filename)
-      @default_sheet = nil
-      # no need to set default_sheet if there is only one sheet in the document
-      if self.sheets.size == 1
-        @default_sheet = self.sheets.first
-      end
+      @default_sheet = self.sheets.first
     ensure
       #if ENV["roo_local"] != "thomas-p"
       FileUtils::rm_r(@tmpdir)
       #end
     end
@@ -126,42 +142,10 @@
       puts "Error in sheet #{sheet}, row #{row}, col #{col}"
       raise
     end
   end
 
-  # returns the first non empty column
-  def first_column(sheet=nil)
-    sheet = @default_sheet unless sheet
-    return @first_column[sheet] if @first_column[sheet]
-    fr, lr, fc, lc = get_firsts_lasts(sheet)
-    fc
-  end
-
-  # returns the last non empty column
-  def last_column(sheet=nil)
-    sheet = @default_sheet unless sheet
-    return @last_column[sheet] if @last_column[sheet]
-    fr, lr, fc, lc = get_firsts_lasts(sheet)
-    lc
-  end
-
-  # returns the first non empty row
-  def first_row(sheet=nil)
-    sheet = @default_sheet unless sheet
-    return @first_row[sheet] if @first_row[sheet]
-    fr, lr, fc, lc = get_firsts_lasts(sheet)
-    fr
-  end
-
-  # returns the last non empty row
-  def last_row(sheet=nil)
-    sheet = @default_sheet unless sheet
-    return @last_row[sheet] if @last_row[sheet]
-    fr, lr, fc, lc = get_firsts_lasts(sheet)
-    lr
-  end
-
   # returns NO formula in excel spreadsheets
   def formula(row,col,sheet=nil)
     raise EXCEL_NO_FORMULAS
   end
 
@@ -190,47 +174,11 @@
     read_cells(sheet) unless @cells_read[sheet]
     @cell[sheet].inspect
   end
 
   private
-  # determine the first and last boundaries
-  def get_firsts_lasts(sheet=nil)
-    
-    # 2008-09-14 BEGINf
-    fr=lr=fc=lc=nil
-    sheet = @default_sheet unless sheet
-    if ! @cells_read[sheet]
-      read_cells(sheet)
-    end
-    if @cell[sheet] # nur wenn ueberhaupt Zellen belegt sind
-      @cell[sheet].each {|cellitem|
-        key = cellitem.first
-        y,x = key
 
-        if cellitem[1].class != String or
-            (cellitem[1].class == String and cellitem[1] != "")
-          fr = y unless fr
-          fr = y if y < fr  
-      
-          lr = y unless lr
-          lr = y if y > lr 
-      
-          fc = x unless fc
-          fc = x if x < fc
-      
-          lc = x unless lc
-          lc = x if x > lc 
-        end
-      }
-    end
-    @first_row[sheet]    = fr
-    @last_row[sheet]     = lr
-    @first_column[sheet] = fc
-    @last_column[sheet]  = lc
-    return fr, lr, fc, lc
-  end
-
   # converts name of a sheet to index (0,1,2,..)
   def sheet_no(name)
     return name-1 if name.kind_of?(Fixnum)
     i = 0
     @workbook.worksheets.each do |worksheet|
@@ -353,11 +301,11 @@
     row_index=1
     worksheet.each(0) do |row| 
       (0..row.size).each do |cell_index|
         cell = row.at(cell_index)
         next if cell.nil?  #skip empty cells
-        next if cell.class == Spreadsheet::Formula
+        next if cell.class == Spreadsheet::Formula && cell.value.nil? # skip empty formla cells
         if date_or_time?(row, cell_index)
           vt, v = read_cell_date_or_time(row, cell_index)
         else
           vt, v = read_cell(row, cell_index)
         end
@@ -369,27 +317,36 @@
       end #row
       row_index += 1
     end # worksheet
     @cells_read[sheet] = true
   end
-  
+
+  # Get the contents of a cell, accounting for the
+  # way formula stores the value
+  def read_cell_content(row, idx)
+    cell = row.at(idx)
+    cell = cell.value if cell.class == Spreadsheet::Formula
+    cell
+  end
+    
   # Test the cell to see if it's a valid date/time. 
   def date_or_time?(row, idx)
     format = row.format(idx)
     if format.date_or_time?
-      cell = row.at(idx)
+      cell = read_cell_content(row, idx)
       true if Float(cell) > 0 rescue false
     else
       false
     end  
   end
   private :date_or_time?
   
   # Read the date-time cell and convert to, 
   # the date-time values for Roo
   def read_cell_date_or_time(row, idx)
-    cell = row.at(idx).to_s.to_f
+    cell = read_cell_content(row, idx)
+    cell = cell.to_s.to_f
     if cell < 1.0
       value_type = :time
       f = cell*24.0*60.0*60.0
       secs = f.round
       h = (secs / 3600.0).floor
@@ -397,29 +354,37 @@
       m = (secs / 60.0).floor
       secs = secs - 60*m
       s = secs
       value = h*3600+m*60+s
     else
-      datetime = row.datetime(idx)
+      if row.at(idx).class == Spreadsheet::Formula
+        datetime = row._datetime(cell)
+      else
+        datetime = row.datetime(idx)
+      end    
       if datetime.hour != 0 or
          datetime.min != 0 or
          datetime.sec != 0 
         value_type = :datetime
         value = datetime
       else
         value_type = :date
-        value = row.date(idx)
+        if row.at(idx).class == Spreadsheet::Formula
+          value = row._date(cell)
+        else
+          value = row.date(idx)
+        end    
         value = sprintf("%04d-%02d-%02d",value.year,value.month,value.day)
       end
     end  
     return value_type, value
   end
   private :read_cell_date_or_time
   
   # Read the cell and based on the class, 
   # return the values for Roo
   def read_cell(row, idx)
-    cell = row.at(idx)
+    cell = read_cell_content(row, idx)
     case cell
     when Float, Integer, Fixnum, Bignum 
       value_type = :float
       value = cell.to_f
     when String, TrueClass, FalseClass