如何在Ruby中将MS Excel日期从浮点数转换为日期格式？

Question

我正在尝试使用roo gem在ruby脚本中解析XLSX文件。

在Excel中，日期以DDD.ttttt的格式存储为浮点数或整数，从1900-01-00（不是01）开始计数。因此，要转换一个日期，比如40396，你需要取1900-01-00+ 40396，你应该得到2010-10-15，但我得到的是2010-08-08。

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

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

我是在 Windows 7 上运行 ruby 1.9.3-mri 和最新的 active_support gem (3.2.1)，我的计算是否有误或者 active support 存在 bug？

编辑：

我是在 Excel 中查看错误的旧文件 - 我的脚本/控制台正在提取正确的数据 - 因此我感到困惑 - 我除了使用正确的文件之外什么都做对了！！！该死的熬夜！

感谢所有回复的人，我将保留这个问题，以便某人需要关于如何使用 ruby 转换 Excel 中的日期的信息。

另外，对于其他遇到此问题的人来说 - spreadsheet gem 目前不支持正确读取 XLSX 文件（v0.7.1），因此我正在使用 roo 进行读取，并使用 axlsx 进行写入。

- konung

Answer 1

你在日期编号上有一个一位偏差的错误 - 这是由于Lotus 1-2-3中的一个bug，而Excel和其他电子表格程序在30多年间一直保持了与之兼容性。最初，第1天应该是1900年1月1日（这将使得第0天等于1899年12月31日）。但是Lotus错误地认为1900年是闰年，因此如果你使用Lotus的数字进行计算，并正确地将1900年视为平年，则所有1900年3月1日之前的日期编号都会偏高1天。第1天变成了1899年12月31日，第0天向前移动到30日。因此，基于Lotus的电子表格中的日期运算纪元实际上是1899年12月30日星期六。（现代Excel和一些其他电子表格扩展了Lotus bug-compatibility以显示1900年2月实际上有29天，因此它们将标记第0天为“12月31日”，同时也同意那是个星期六！但是其他基于Lotus的电子表格不会这样做，Ruby当然也不会。）即使考虑了这个错误，你所提供的例子仍然是错误的：Lotus日期编号40,396是2010年8月6日而不是10月15日。我已经在Excel、LibreOffice和Google sheets中确认了这个对应关系，它们都是一致的。你一定是把例子搞混了。以下是一种进行转换的方法：

Time.utc(1899,12,30) + 40396.days #=> 2010-08-06 00:00:00 UTC

或者，您可以利用另一种已知的对应关系。 Ruby（以及POSIX系统通常）的零时刻是格林尼治标准时间1970年1月1日午夜时刻。1970年1月1日是莲花日第25,569天。只要记得在协调世界时进行计算，您也可以这样做：

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

在任何情况下，您可能都想为纪元日期声明一个符号常量（表示1899-12-30的Time对象或POSIX“第0天”值25569）。

如果您不需要active_support/core_ext/integer/time进行其他操作，并且不想仅为此加载它，则可以将那些调用.days替换为乘以86400（每天的秒数）。

- Mark Reed

---

兼容性可追溯至1900年1月1日，因为Excel的日历也将1900年视为闰年。 - phoog

很好，@phoog，既然这个问题涉及到特定的Excel，但我也提到了其他电子表格，它们并没有扩展其兼容性到那么远。当然，Ruby也没有。因此，实际上，第0天仍然是1899年12月30日。 - Mark Reed

请忽略整个内容 - 我很困惑 - 因为在我的脚本中我正在提取正确的文件，但是在 Excel 中我打开了一个带有错误日期的旧文件。!!!!! 仍然感谢您的回复。 - konung

Answer 2

"Excel将日期和时间存储为表示自1900年1月0日以来的天数的数字，加上24小时内的小数部分：dddddd.tttttt。这被称为序列日期或序列日期时间。"（请参见日期时间参考）

如果您的列包含日期时间而不仅仅是日期，则以下代码非常有用：

"

 dt = DateTime.new(1899, 12, 30) + excel_value.to_f

请记住，Excel工作表中有两种日期模式，基于1900年和基于1904年。通常，在Mac上创建的电子表格默认启用后者。如果您发现日期总是相差4年，请使用其他基准日期：

 dt = DateTime.new(1904, 1, 1) + excel_value.to_f

您可以为任何电子表格启用/禁用1904日期模式，但是如果在添加数据后更改设置，则电子表格中的日期将会偏移4年。通常情况下，您应该始终使用1900日期模式，因为大多数Excel用户都是基于Windows操作系统的。

---

注意：使用此方法的一个问题是可能会发生+/- 1秒的四舍五入。对于我导入的日期来说，“足够接近”，但要记住这一点。更好的解决方案可能是使用小数秒的四舍五入来解决这个问题。

- kgx

Answer 3

您的计算结果不正确。您是如何得出2010-10-15的预期结果的？

在Excel中，40396代表2010-08-06（当然，不使用1904年日历）。为了证明这一点，在Excel单元格中输入40396，并将格式设置为yyyy-mm-dd。

或者：

40396 / 365.2422 = 110.6 (years -- 1900 + 110 = 2010)
0.6 * 12 = 7.2 (months -- January = 1; 1 + 7 = 8; 8 = August)
0.2 * 30 = 6 (days)

Excel的日历错误地包括了1900年2月29日，这导致了您的2010年8月8日的结果与实际相差一天；至于第二天的差异原因我不确定。

- phoog

---

Mark Reed解释了1900-02-29“错误”的原因：与Lotus兼容（它也有这个错误）。对于您代码的最后一行：并不是每个月都有30天，对吧？但为了简单起见，这没关系。 - Cadoiz

@Cadoiz 是的，微软关于这个选择背后历史的账户目前可以在https://learn.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year 上找到。至于计算，虽然可能使用更多精度，但由于我们只涉及天数而不是时间，因此不应该必要。如果您需要将此逻辑应用于具有时间组件（即非整数值）的值，则可以跳过月份部分以计算自年初以来的（非整数）天数，然后使用月份长度的查找表来计算月份和日期。 - phoog