Add support for automatically adjusting serialized times and dates to account for the zone

2 files changed, 9 insertions, 16 deletions

diff --git a/lib/axlsx/workbook/worksheet/date_time_converter.rb b/lib/axlsx/workbook/worksheet/date_time_converter.rb
index a755a8e8..44f9fec4 100644
--- a/lib/axlsx/workbook/worksheet/date_time_converter.rb
+++ b/lib/axlsx/workbook/worksheet/date_time_converter.rb
@@ -10,7 +10,8 @@ module Axlsx
     # @return [Numeric]
     def self.date_to_serial(date)
       epoch = Axlsx::Workbook::date1904 ? Date.new(1904) : Date.new(1899, 12, 30)
-      (date - epoch).to_f
+      offset_date = date.respond_to?(:utc_offset) ? date + date.utc_offset.seconds : date
+      (offset_date - epoch).to_f
     end
 
     # The time_to_serial methond converts a Time object its excel serialized form.
@@ -19,11 +20,11 @@ module Axlsx
     def self.time_to_serial(time)
       # Using hardcoded offsets here as some operating systems will not except
       # a 'negative' offset from the ruby epoch.
-      epoch1900 = -2209161600 # Time.utc(1899, 12, 30).to_i
-      epoch1904 = -2082844800 # Time.utc(1904, 1, 1).to_i
-      seconds_per_day = 86400 # 60*60*24
+      epoch1900 = -2209161600.0 # Time.utc(1899, 12, 30).to_i
+      epoch1904 = -2082844800.0 # Time.utc(1904, 1, 1).to_i
+      seconds_per_day = 86400.0 # 60*60*24
       epoch = Axlsx::Workbook::date1904 ? epoch1904 : epoch1900
-      (time.to_f - epoch)/seconds_per_day
+      (time.utc_offset + time.to_f - epoch)/seconds_per_day
     end
   end
 end
diff --git a/test/workbook/worksheet/tc_date_time_converter.rb b/test/workbook/worksheet/tc_date_time_converter.rb
index 09c1c507..1bdee04b 100644
--- a/test/workbook/worksheet/tc_date_time_converter.rb
+++ b/test/workbook/worksheet/tc_date_time_converter.rb
@@ -113,20 +113,12 @@ class TestDateTimeConverter < Test::Unit::TestCase
   def test_timezone
 
     utc = Time.utc 2012 # January 1st, 2012 at 0:00 UTC
-
-    # JRuby makes no assumption on time zone. randym
-    #local = begin
-    #  Time.new 2012, 1, 1, 1, 0, 0, 3600 # January 1st, 2012 at 1:00 GMT+1
-    #rescue ArgumentError
-    #  Time.parse "2012-01-01 01:00:00 +0100"
-    #end
-
-    local = Time.parse "2012-01-01 01:00:00 +0100"
+    local = Time.parse "2012-01-01 09:00:00 +0900"
 
     assert_equal local, utc
-    assert_equal Axlsx::DateTimeConverter::time_to_serial(local), Axlsx::DateTimeConverter::time_to_serial(utc)
+    assert_equal Axlsx::DateTimeConverter::time_to_serial(local) - local.utc_offset.to_f/86400,  Axlsx::DateTimeConverter::time_to_serial(utc)
     Axlsx::Workbook.date1904 = true
-    assert_equal Axlsx::DateTimeConverter::time_to_serial(local), Axlsx::DateTimeConverter::time_to_serial(utc)
+    assert_equal Axlsx::DateTimeConverter::time_to_serial(local) - local.utc_offset.to_f/86400, Axlsx::DateTimeConverter::time_to_serial(utc)
   end
 
 end