From 65ec495117d22df16ef1c3fc94bdcfe0f2d32d30 Mon Sep 17 00:00:00 2001 From: Randy Morgan Date: Thu, 23 Feb 2012 09:44:33 +0900 Subject: renaming for clarity, a bit of docs and some patches to spec for AWSOME date/time converter as negative date/time does not parse in some environments under 1.8.7 --- lib/axlsx/workbook/workbook.rb | 2 +- lib/axlsx/workbook/worksheet/converter.rb | 21 ------ .../workbook/worksheet/date_time_converter.rb | 25 ++++++++ test/workbook/worksheet/tc_converter.rb | 69 -------------------- test/workbook/worksheet/tc_date_time_converter.rb | 75 ++++++++++++++++++++++ 5 files changed, 101 insertions(+), 91 deletions(-) delete mode 100644 lib/axlsx/workbook/worksheet/converter.rb create mode 100644 lib/axlsx/workbook/worksheet/date_time_converter.rb delete mode 100644 test/workbook/worksheet/tc_converter.rb create mode 100644 test/workbook/worksheet/tc_date_time_converter.rb diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index c19c3521..8a6eb629 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -1,7 +1,7 @@ # -*- coding: utf-8 -*- module Axlsx -require 'axlsx/workbook/worksheet/converter.rb' +require 'axlsx/workbook/worksheet/date_time_converter.rb' require 'axlsx/workbook/worksheet/cell.rb' require 'axlsx/workbook/worksheet/row.rb' require 'axlsx/workbook/worksheet/worksheet.rb' diff --git a/lib/axlsx/workbook/worksheet/converter.rb b/lib/axlsx/workbook/worksheet/converter.rb deleted file mode 100644 index 8df08f34..00000000 --- a/lib/axlsx/workbook/worksheet/converter.rb +++ /dev/null @@ -1,21 +0,0 @@ -# encoding: UTF-8 -require "date" - -module Axlsx - class Converter - def date_to_serial(date, date1904=false) - epoc = date1904 ? Date.new(1904) : Date.new(1899, 12, 30) - (date-epoc).to_f - end - - def time_to_serial(time, date1904=false) - # Using hardcoded offsets here as some operating systems will not except - # a 'negative' offset from the ruby epoc. - epoc1900 = -2209161600 # Time.utc(1899, 12, 30).to_i - epoc1904 = -2082844800 # Time.utc(1904, 1, 1).to_i - seconds_per_day = 86400 # 60*60*24 - epoc = date1904 ? epoc1904 : epoc1900 - (time.to_f - epoc)/seconds_per_day - end - end -end diff --git a/lib/axlsx/workbook/worksheet/date_time_converter.rb b/lib/axlsx/workbook/worksheet/date_time_converter.rb new file mode 100644 index 00000000..ee6d4a8a --- /dev/null +++ b/lib/axlsx/workbook/worksheet/date_time_converter.rb @@ -0,0 +1,25 @@ +# encoding: UTF-8 +require "date" + +module Axlsx + # The DateTimeConverter class converts both data and time types to their apprpriate excel serializations + class DateTimeConverter + + # The date_to_serial method converts dates to their excel serialized forms + # @param [Date] date the date to be serialized + def date_to_serial(date) + epoc = Axlsx::Workbook::date1904 ? Date.new(1904) : Date.new(1899, 12, 30) + (date-epoc).to_f + end + + def time_to_serial(time) + # Using hardcoded offsets here as some operating systems will not except + # a 'negative' offset from the ruby epoc. + epoc1900 = -2209161600 # Time.utc(1899, 12, 30).to_i + epoc1904 = -2082844800 # Time.utc(1904, 1, 1).to_i + seconds_per_day = 86400 # 60*60*24 + epoc = Axlsx::Workbook::date1904 ? epoc1904 : epoc1900 + (time.to_f - epoc)/seconds_per_day + end + end +end diff --git a/test/workbook/worksheet/tc_converter.rb b/test/workbook/worksheet/tc_converter.rb deleted file mode 100644 index 3919382a..00000000 --- a/test/workbook/worksheet/tc_converter.rb +++ /dev/null @@ -1,69 +0,0 @@ -require 'test/unit' -require 'axlsx.rb' - -class TestConverter < Test::Unit::TestCase - def setup - @converter = Axlsx::Converter.new - @margin_of_error = 0.000_001 - end - - def test_date_to_serial_1900 - { # examples taken straight from the spec - "1893-08-05" => -2338.0, - "1900-01-01" => 2.0, - "1910-02-03" => 3687.0, - "2006-02-01" => 38749.0, - "9999-12-31" => 2958465.0, - }.each do |date_string, expected| - serial = @converter.date_to_serial Date.parse(date_string) - assert_equal serial, expected - end - end - - def test_date_to_serial_1904 - { # examples taken straight from the spec - "1893-08-05" => -3800.0, - "1904-01-01" => 0.0, - "1910-02-03" => 2225.0, - "2006-02-01" => 37287.0, - "9999-12-31" => 2957003.0, - }.each do |date_string, expected| - serial = @converter.date_to_serial Date.parse(date_string), true - assert_equal serial, expected - end - end - - def test_time_to_serial_1900 - { # examples taken straight from the spec - "1893-08-05T00:00:01Z" => -2337.999989, - "1899-12-28T18:00:00Z" => -1.25, - "1910-02-03T10:05:54Z" => 3687.4207639, - "1900-01-01T12:00:00Z" => 2.5, # wrongly indicated as 1.5 in the spec! - "9999-12-31T23:59:59Z" => 2958465.9999884, - }.each do |time_string, expected| - serial = @converter.time_to_serial Time.parse(time_string) - assert_in_delta serial, expected, @margin_of_error - end - end - - def test_time_to_serial_1904 - { # examples taken straight from the spec - "1893-08-05T00:00:01Z" => -3799.999989, - "1910-02-03T10:05:54Z" => 2225.4207639, - "1904-01-01T12:00:00Z" => 0.5000000, - "9999-12-31T23:59:59Z" => 2957003.9999884, - }.each do |time_string, expected| - serial = @converter.time_to_serial Time.parse(time_string), true - assert_in_delta serial, expected, @margin_of_error - end - end - - def test_timezone - utc = Time.utc 2012 # January 1st, 2012 at 0:00 UTC - local = Time.new 2012, 1, 1, 1, 0, 0, 3600 # January 1st, 2012 at 1:00 GMT+1 - assert_equal local, utc - assert_equal @converter.time_to_serial(local), @converter.time_to_serial(utc) - assert_equal @converter.time_to_serial(local, true), @converter.time_to_serial(utc, true) - end - -end diff --git a/test/workbook/worksheet/tc_date_time_converter.rb b/test/workbook/worksheet/tc_date_time_converter.rb new file mode 100644 index 00000000..a039282a --- /dev/null +++ b/test/workbook/worksheet/tc_date_time_converter.rb @@ -0,0 +1,75 @@ +# -*- coding: utf-8 -*- +require 'test/unit' +require 'axlsx.rb' + +class TestDateTimeConverter < Test::Unit::TestCase + def setup + @converter = Axlsx::DateTimeConverter.new + @margin_of_error = 0.000_001 + end + + def test_date_to_serial_1900 + Axlsx::Workbook.date1904 = false + { # examples taken straight from the spec + # "1893-08-05" => -2338.0, # ruby 1.8.7 cannot parse negative dates in some environments + "1900-01-01" => 2.0, + "1910-02-03" => 3687.0, + "2006-02-01" => 38749.0, + "9999-12-31" => 2958465.0, + }.each do |date_string, expected| + serial = @converter.date_to_serial Date.parse(date_string) + assert_equal serial, expected + end + end + + def test_date_to_serial_1904 + Axlsx::Workbook.date1904 = true + { # examples taken straight from the spec + #　"1893-08-05" => -3800.0, # ruby 1.8.7 cannot parse negative dates in some environments + "1904-01-01" => 0.0, + "1910-02-03" => 2225.0, + "2006-02-01" => 37287.0, + "9999-12-31" => 2957003.0, + }.each do |date_string, expected| + serial = @converter.date_to_serial Date.parse(date_string) + assert_equal serial, expected + end + end + + def test_time_to_serial_1900 + Axlsx::Workbook.date1904 = false + { # examples taken straight from the spec + # "1893-08-05T00:00:01Z" => -2337.999989, # ruby 1.8.7 cannot parse negative dates in some environments + # "1899-12-28T18:00:00Z" => -1.25, # ruby 1.8.7 cannot parse negative dates in some environments + "1910-02-03T10:05:54Z" => 3687.4207639, + "1900-01-01T12:00:00Z" => 2.5, # wrongly indicated as 1.5 in the spec! + "9999-12-31T23:59:59Z" => 2958465.9999884, + }.each do |time_string, expected| + serial = @converter.time_to_serial Time.parse(time_string) + assert_in_delta serial, expected, @margin_of_error + end + end + + def test_time_to_serial_1904 + Axlsx::Workbook.date1904 = true + { # examples taken straight from the spec + # "1893-08-05T00:00:01Z" => -3799.999989, # ruby 1.8.7 cannot parse negative dates in some environments + "1910-02-03T10:05:54Z" => 2225.4207639, + "1904-01-01T12:00:00Z" => 0.5000000, + "9999-12-31T23:59:59Z" => 2957003.9999884, + }.each do |time_string, expected| + serial = @converter.time_to_serial Time.parse(time_string) + assert_in_delta serial, expected, @margin_of_error + end + end + + def test_timezone + utc = Time.utc 2012 # January 1st, 2012 at 0:00 UTC + local = Time.new 2012, 1, 1, 1, 0, 0, 3600 # January 1st, 2012 at 1:00 GMT+1 + assert_equal local, utc + assert_equal @converter.time_to_serial(local), @converter.time_to_serial(utc) + Axlsx::Workbook.date1904 = true + assert_equal @converter.time_to_serial(local), @converter.time_to_serial(utc) + end + +end -- cgit v1.2.3