对Excel中的日期和时间感到困惑

Question

我有Java时间（自1970年1月1日UTC以来的ms），并希望将该时间写入csv文件，以便Excel可以正确解释和格式化它。我知道，excel 使用"串行日期时间"作为一种格式 - 这是一个浮点数，其中整数部分给出自 1/1/1900 以来的天数，小数部分给出一天的小数部分。

我无法理解时区和夏令时处理。

本页显示 excel 纪元 （1/1/1900） 基于本地（=创建 Excel 文件的计算机？）时区。这意味着，如果没有创建它的计算机时区的信息，串行日期不会指示唯一的时刻。不是我会选择的，但没关系。

现在接受这一点，我相信我可以通过以下 Java 代码将 Java 时间转换为 Excel 串行日期（注意：我在苏黎世，CET 时区）：

private static final long ONE_HOUR= 60L * 60 * 1000;
private static final long ONE_DAY = 24 * ONE_HOUR;
private static final long excelEpoch; 
static{
    Calendar cal;
    cal = Calendar.getInstance(TimeZone.getTimeZone("Europe/Zurich"));
    cal.set(Calendar.YEAR, 1900);
    cal.set(Calendar.DAY_OF_YEAR, 1);
    cal.set(Calendar.HOUR_OF_DAY, 0);
    cal.set(Calendar.MINUTE, 0);
    cal.set(Calendar.SECOND, 0);
    cal.set(Calendar.MILLISECOND, 0);
    excelEpoch = cal.getTimeInMillis();
}
private static String formatForExcel(long time){
    return ""+(time-excelEpoch)/(double)ONE_DAY;
}

使用它我可以打印几次：

public static void main(String[] args) {
    String sep = "t"; // csv field separator
    SimpleDateFormat fmt = new SimpleDateFormat("HH:mm:ss d/M/yyyy");       
    fmt.setTimeZone(TimeZone.getTimeZone("Europe/Zurich"));

    System.out.println("Time in ms since 1/1/1970 UTC"+ sep + "Time as string" + sep + "Excel serial" + sep + "Excel serial formatted by excel");
    long startTime = 1332630000000L; // 25/3/2012 00:00 CET , shortly before change from winter time to DST
    for (long t = startTime;  t < startTime + 4*ONE_HOUR; t+=ONE_HOUR) {
        System.out.println(t + sep + fmt.format(new Date(t)) + sep + formatForExcel(t) + sep + formatForExcel(t));
    }
}

哪个返回

Time in ms since 1/1/1970 UTC   Time as string  Excel serial    Excel serial formatted by excel
1332630000000   00:00:00 25/3/2012  40991.0 40991.0
1332633600000   01:00:00 25/3/2012  40991.041666666664  40991.041666666664
1332637200000   03:00:00 25/3/2012  40991.083333333336  40991.083333333336
1332640800000   04:00:00 25/3/2012  40991.125   40991.125

请注意，从冬令时到 DST 的更改发生在这些小时内（检查第二列，缺少第 2 小时）。

现在出现了混乱。如果我将其粘贴到 excel 中，并在最后一列中选择"设置单元格格式..."然后"时间"（任何格式），它打印：

Time in ms since 1/1/1970 UTC   Time as string  Excel serial    Excel serial formatted by excel
1332630000000   25.03.2012 00:00    40991   0:00:00
1332633600000   25.03.2012 01:00    40991.04167 1:00:00
1332637200000   25.03.2012 03:00    40991.08333 2:00:00
1332640800000   25.03.2012 04:00    40991.125   3:00:00

请注意，擅长格式化串行日期不会更改为 DST。所以这不是挂钟时间。

长话短说：

我应该如何将 Java 时间转换为 Excel 以便它正常工作？

Answer 1

我怀疑 Excel 并没有真正考虑时区。我怀疑它实际上只是将其视为每个可以想象的日期/时间都有效的"本地时间"。（我相信，在Joda Time的说法中是"本地即时"，尽管我不知道它被广泛使用了。

我怀疑没有办法代表特定的时刻，相反，你应该：

• 将您想要表示的任何日期/时间作为当地时间（例如"2012 年 3 月 25 日凌晨 3 点"）
• 将其放入设置为使用 UTC 的Calendar中
• 从calendar.getTime().getTime()拿米利斯
• 减去 1900-01-01T00：00：00Z 的"Excel 纪元"值（同样，通过设置为 UTC 的日历获取）
• 除以"每天的千米数"

现在，Excel 在处理 1900 年 3 月 1 日之前的日期方面也存在一个奇怪之处，但希望这不会让您感到困惑。

Answer 2

将浮点数"串行日期/时间"转换为"自 1970/1/1 以来的工厂"

注意：daysFrom1900到1970适用于谷歌电子表格日期，但可能需要对Excel进行轻微调整

                            int daysFrom1900to1970 =365*70 + 19; // Or maybe =365*70 + 17 But 19 worked.
                            int millisPerDay = 1000 * 60 * 60 * 24;
                            long millisSince1970 = (long) ((timeToSend.doubleValue() - daysFrom1900to1970) * millisPerDay );
                            Calendar dateTimeToSend = Calendar.getInstance();                               
                            dateTimeToSend.setTimeZone(TimeZone.getTimeZone("GMT"));
                            dateTimeToSend.setTimeInMillis(millisSince1970);
                            System.out.println("timeToSend:"+ new SimpleDateFormat("yyyy.MM.dd HH:mm:ss z").format(dateTimeToSend.getTime()));