如何在Ruby中将MS excel日期从float转换为日期格式？ [英] How to convert MS excel date from float to date format in Ruby?

问题描述

在excel日期以DDDDD.ttttt格式的浮点数或整数形式存储，从 1900-01-00（00 no 01）。所以为了转换一个日期，如40396 - 你会采取 1900-01-00 + 40396 ，你应该得到2010-10-15，但我正在得到2010 -08-08。

我正在使用active_support / time进行如下计算：

  Time.new（1900-01-01）+ 40396.days 
  

<我正在做我的计算错误或有积极支持的错误？

我正在运行ruby 1.9.3-mri在Windows 7 +最新的active_support gem 3.2.1）

编辑

我正在看旧文件Excel错误的数据 - 我的脚本/控制台正在拉取正确的数据 - 因此我的困惑 - 我做的一切正确，除了使用正确的文件！感谢所有的打火机！

感谢大家回复，我会在这里保留问题，以防万一有人需要有关如何使用ruby从excel转换日期的信息。 >

对于任何其他运行这个 - 电子表格宝石的人也不会支持在这个时候（v 0.7.1）正确读取XLSX文件 - 所以我使用roo阅读和axlsx

解决方案

您的日期编号中有一个错误的错误 - 由于Lotus 1- 2-3 Excel和其他电子表格程序已经认真维护了30多年的兼容性。

原来，第1天的目的是为了在1900年1月1日（正如你所说，这将使得第0天等于1900年1月0日或12月31日，1899）。但莲花错误地认为1900年是一个闰年，所以日数是一个一个。将这些数字用正确计算1900作为常年的日历，12月31日变为第1天，第0天转回30日。因此，基于Lotus的电子表格中的日期算术的时代是1899年12月30日（星期六）。（现代Excel和其他一些电子表格扩展了Lotus bug，足以继续标注该日期1900年1月1日，同意它是星期六，但是其他基于Lotus的电子表格并不是这样，而且Ruby也不是。）

然而，即使允许出现此错误，您所述的示例是不正确的：莲花日40396是2010年8月6日，而不是10月15日。我已经在Excel，LibreOffice和Google表格中确认了这一信函，所有这一切同意。你必须在某个地方交叉示例。

无论如何，Ruby的 Time 类不支持前一个日期的算术到1900年，所以使用它直接与1899-12-30基准日是有问题的。任何时候你想在1970年前处理Ruby之前的日期，你最好使用 DateTime ，它支持第二个算术，因此与ActiveSupport一起工作正常帮助者喜欢 .days ：

  require'date'
 DateTime.new（1899,12,30）+ 40396.days＃=>周五，06八月2010 00:00:00 +0000 
  

您可以拨打＃ to_time ，如果你想要一个 Time 对象。

或者，你可以利用另一个已知的通信。 Ruby（和一般POSIX系统）的时间零点是1970年1月1日UTC时间午夜。 1970年1月1日是莲花日25,569。 时间在最近的日期没有困难，所以只要你记得在UTC中进行计算，你可以这么做：

  Time.at（（40396  -  25569）.days）.utc＃=> 2010-08-06 00:00:00 UTC 
  

在任一种情况下，您可能要声明一个纪元日期的符号常数（ DateTime 对象或值25,569）。

您可以替换这些调用到 .days 乘以86,400，如果你不需要ActiveSupport的其他任何东西，不想加载它只是为了这个。

Trying to parse and XLSX file using roo gem in a ruby script.

In excel dates are stored as floats or integers in the format DDDDD.ttttt, counting from 1900-01-00 (00 no 01). So in order to convert a date such as 40396 - you would take 1900-01-00 + 40396 and you should get 2010-10-15, but I'm getting 2010-08-08.

I'm using active_support/time to do calculation like so:

Time.new("1900-01-01") + 40396.days

Am I doing my calculation wrong or is there a bug in active support?

I'm running ruby 1.9.3-mri on Windows 7 + latest active_support gem (3.2.1)

EDIT

I was looking at the older file in Excel with the wrong data - my script / console were pulling the right data - hence my confusion - I was doing everything right, except for using the right file!!!! Damn the all-nighters!

Thanks to everyone replying, I will keep the question here in case somebody needs info on how to convert dates from excel using ruby.

Also for anyone else running into this - spreadsheet gem DOES NOT support reading XLSX files at this point (v 0.7.1) properly - so I'm using roo for reading, and axlsx for writing.

解决方案

You have an off-by-one error in your day numbering - due to a bug in Lotus 1-2-3 that Excel and other spreadsheet programs have carefully maintained compatibility with for 30+ years.

Originally, day 1 was intended to be January 1, 1900 (which would, as you stated, make day 0 equal to "January 0, 1900", or December 31, 1899). But Lotus incorrectly considered 1900 to be a leap year, so the day numbers are off by one. Using those numbers with a calendar that correctly counts 1900 as a common year, December 31st becomes day 1 and day 0 shifts back to the 30th. So the epoch for date arithmetic in Lotus-based spreadsheets is Saturday, December 30th, 1899. (Modern Excel and some other spreadsheets extend the Lotus bug far enough to continue to label that date "January 1st, 1900" while agreeing that it was a Saturday, but other Lotus-based spreadsheets don't, and Ruby certainly doesn't either.)

Even allowing for this error, however, your stated example is incorrect: Lotus day number 40,396 is August 6th, 2010, not October 15th. I have confirmed this correspondence in Excel, LibreOffice, and Google sheets, all of which agree. You must have crossed examples somewhere.

In any case, Ruby's Time class doesn't support arithmetic on dates prior to 1900, so using it directly with the 1899-12-30 basis date is problematic. Any time you want to deal with dates prior to 1970 in Ruby, you're better off using DateTime, which supports second-based arithmetic and therefore works fine in conjunction with the ActiveSupport helpers like .days:

require 'date'
DateTime.new(1899,12,30) + 40396.days # => Fri, 06 Aug 2010 00:00:00 +0000

You can call #to_time on the result if you want a Time object instead.

Alternatively, you could take advantage of another known correspondence. Time zero for Ruby (and POSIX systems in general) is the moment January 1, 1970, at midnight UTC. January 1, 1970 is Lotus day 25,569. Time has no difficulty with such recent dates, so as long as you remember to do your calculations in UTC, you can just do this:

Time.at( (40396 - 25569).days ).utc # => 2010-08-06 00:00:00 UTC

In either case, you probably want to declare a symbolic constant for the epoch date (either the DateTime object or the value 25,569).

You can replace those calls to .days with multiplication by 86,400 if you don't need ActiveSupport for anything else, and don't want to load it just for this.

这篇关于如何在Ruby中将MS excel日期从float转换为日期格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！