将excel中日期的数字表示转换为java中的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19028192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting Number representation of Date in excel to Date in java
提问by aizaz
I have date column in excel, but when I'm reading in my java application I'm getting value as number
我在 excel 中有日期列，但是当我在我的 Java 应用程序中阅读时，我得到的值是数字
Example
例子
Excel Date
Excel 日期
1/1/2013
Am getting as
我越来越像
41275.00
How to convert number to date in my Java application?
如何在我的 Java 应用程序中将数字转换为日期？
回答by Jeff Storey
Apache POI has some utilities for that http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html, notably http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html#getJavaDate(double)
Apache POI 有一些实用程序http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html，特别是http://poi.apache.org/apidocs/org/apache/ poi/ss/usermodel/DateUtil.html#getJavaDate(double)
Note Excel stores dates as the number of days (plus fractional days) since 1900 (and in some cases it can be from 1904). See http://support.microsoft.com/kb/180162.
注意 Excel 将日期存储为自 1900 年以来的天数（加上小数天数）（在某些情况下，它可以是 1904 年以来）。请参阅http://support.microsoft.com/kb/180162。
回答by cemal
Here is a minimal working example how to convert an Excel date to a Java date:
这是一个如何将 Excel 日期转换为 Java 日期的最小工作示例：
Date javaDate= DateUtil.getJavaDate((double) 41275.00);
System.out.println(new SimpleDateFormat("MM/dd/yyyy").format(javaDate));
which returns
返回
01/01/2013
You also need to import the following packages:
您还需要导入以下包：
java.text.SimpleDateFormat
java.util.Date
回答by u6856342
Excel's serialized dates are the number of days since 1/1/1900. In order to figure out the date again, we have to add the serial number worth of days.
Excel 的序列化日期是自 1/1/1900 以来的天数。为了再次计算出日期，我们必须加上相当于天数的序列号。
for Java 8 without any dependency
对于没有任何依赖的 Java 8
```
``
/*
1900-1-0 0
1900-1-1 1
1900-1-2 2
1900-1-3 3
*/
int days = 43323;
LocalDate start = LocalDate.of(1900, 1, 1);
LocalDate today = LocalDate.of(2018, 8, 11);
// days to date
LocalDate date = start.plusDays(days).minusDays(2);
System.out.println(date);
// date to days
long days1 = ChronoUnit.DAYS.between(start, today) + 2;
System.out.println(days1);
```
``
回答by Basil Bourque
tl;dr
tl;博士
Use modern java.timeclasses.
使用现代java.time类。
LocalDate // Represent a date-only vaule, without time-of-day and without time zone.
.of( 1899 , Month.DECEMBER , 30 ) // Specify epoch reference date used by *some* versions of Excel. Beware: Some versions use a 1904 epoch reference. Returns a `LocalDate` object.
.plusDays( // Add a number of days.
(long) Double.parseDouble( "41275.00" ) // Get a number of whole days from your input string.
) // Returns another instance of a `LocalDate`, per Immutable Objects pattern, rather than altering the original.
.toString() // Generate text representing the value of this `LocalDate` in standard ISO 8601 format.
2013-01-01
2013-01-01
java.time
时间
The modern solution uses the java.timeclasses that supplanted the terrible legacy date-time classes bundled with the earliest versions of Java.
现代解决方案使用java.time类取代了与最早版本的 Java 捆绑在一起的可怕的遗留日期时间类。
Epoch reference date: 1899-12-30
纪元参考日期：1899-12-30
According to this documentation, that value from Microsoft Excelis the number of days since the epoch referenceof 1900-01-01 in UTC. Internally, the actual reference date is December 30, 1899as documented on this Wikipedia page.
根据此文档，Microsoft Excel中的该值是自UTC时代参考1900-01-01以来的天数。在内部，实际参考日期是 1899 年 12 月 30 日，如本维基百科页面所述。
Beware, some versions (old versions for macOS?) of Excel use a different epoch in 1904.
请注意，某些版本（macOS 的旧版本？）Excel在 1904 使用不同的时代。
Establish the epoch reference somewhere in your code.
在代码中的某处建立纪元引用。
final static public LocalDate EXCEL_EPOCH_REFERENCE =
LocalDate.of( 1899 , Month.DECEMBER , 30 )
; // Beware: Some versions of Excel use a 1904 epoch reference.
Do the math
算一算
Parse your input string as a BigDecimalfor accuracy (versus floating-point types that trade away accuracy for faster execution).
将您的输入字符串解析为 aBigDecimal以提高准确性（相对于牺牲准确性以加快执行速度的浮点类型）。
BigDecimal countFromEpoch = new BigDecimal( "41275.00" );
Add the number of whole days to the epoch reference date.
将整天数添加到纪元参考日期。
long days = countFromEpoch.longValue(); // Extract the number of whole days, dropping the fraction.
LocalDate localDate = EXCEL_EPOCH_REFERENCE.plusDays( days );
localDate.toString(): 2013-01-01
localDate.toString(): 2013-01-01
About java.time
关于java.time
The java.timeframework is built into Java 8 and later. These classes supplant the troublesome old legacydate-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
该java.time框架是建立在Java 8和更高版本。这些类取代了麻烦的旧的遗留日期时间类，例如java.util.Date, Calendar, & SimpleDateFormat。
The Joda-Timeproject, now in maintenance mode, advises migration to the java.timeclasses.
现在处于维护模式的Joda-Time项目建议迁移到java.time类。
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
要了解更多信息，请参阅Oracle 教程。并在 Stack Overflow 上搜索许多示例和解释。规范是JSR 310。
You may exchange java.timeobjects directly with your database. Use a JDBC drivercompliant with JDBC 4.2or later. No need for strings, no need for java.sql.*classes.
您可以直接与您的数据库交换java.time对象。使用符合JDBC 4.2或更高版本的JDBC 驱动程序。不需要字符串，不需要类。java.sql.*
Where to obtain the java.time classes?
从哪里获得 java.time 类？
- Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
- Java SE 6and Java SE 7
- Most of the java.timefunctionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
- Android
- Later versions of Android bundle implementations of the java.timeclasses.
- For earlier Android (<26), the ThreeTenABPproject adapts ThreeTen-Backport(mentioned above). See How to use ThreeTenABP….
- Java SE 8、Java SE 9、Java SE 10、Java SE 11及更高版本 - 标准 Java API 的一部分，具有捆绑实现。
- Java 9 添加了一些小功能和修复。
- Java SE 6和Java SE 7
- 大部分java.time功能在ThreeTen-Backport中向后移植到 Java 6 & 7 。
- 安卓
- 更高版本的 Android 捆绑实现java.time类。
- 对于早期的 Android（<26），ThreeTenABP项目采用了ThreeTen-Backport（上面提到过）。请参阅如何使用ThreeTenABP ...。
The ThreeTen-Extraproject extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
该ThreeTen-额外项目与其他类扩展java.time。该项目是未来可能添加到 java.time 的试验场。你可能在这里找到一些有用的类，比如Interval，YearWeek，YearQuarter，和更多。
