java混淆了Excel中的日期和时间

Question

我有Java时间（自1970年1月1日UTC起为毫秒），希望将该时间写入csv文件，以便Excel能够正确解释和格式化它。据我所知，excel使用“序列日期时间”作为格式，这是一个浮点数，其中整数部分给出自1900年1月1日起的天数，小数部分给出一天的分数

我无法理解时区和夏令时在这方面的处理

This page 表示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

请注意，excel在格式化序列日期时不会更改为DST。所以现在不是挂钟时间

长话短说：

我应该如何将Java time转换为Excel，使其正常工作

Answer 1

我怀疑Excel没有真正考虑时区。我怀疑这只是把它当作一个“当地时间”，在这里，每个可能的日期/时间都是有效的。（我相信，用乔达时间的说法，这是一个“本地瞬间”——尽管我不知道它的使用范围有多广。）

我怀疑没有办法在时间上代表一个特定的瞬间，相反，你应该：

• 选择你想要表示为当地时间的任何日期/时间（例如“2012年3月25日凌晨3点”）
• 将其放入设置为使用UTC的Calendar中
• 从calendar.getTime().getTime()取毫秒
• 减去1900-01-01T00:00:00Z的“Excel历元”值（同样，通过设置为UTC的日历获取）
• 除以“毫秒/天”

现在还有一个oddity with Excel in terms of its handling of dates before March 1st 1900，但希望它不会咬到你

Answer 2

将浮点“序列日期/时间”转换为“自1970年1月1日起的米尔斯”

注意：从1900年到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()));