Apache POI 日期解析一秒钟 [英] Apache POI Date Parsing One Second Off
问题描述
我正在解析一个包含日期的 Excel 电子表格.与 Excel 中显示的结果相比，POI 的结果相差 1 秒.
Excel中无格式数据为:43261.5027743056Excel 中的单元格格式为:mm/dd/yyyy hh:mm:ssExcel 中的字段显示为:6/10/2018 12:04:00 PM
POI 解析器(v 4.0.1 和 4.1.0)将其解析为:
- 值:43261.502774305598
- 格式:mm/dd/yyyy\hh:mm:ss
- 结果:2018 年 6 月 10 日下午 12:03:59
这是我的代码:
私有最终 DataFormatter 格式化程序；案件编号:String n = value.toString();如果(this.formatString ！= null){thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);}否则 thisStr = n;休息;我做错了什么吗?
问题不是二进制浮点问题.这也存在，但不应该影响秒数.
问题是您的值 43261.5027743056 并不是真正准确的日期时间 06/10/2018 12:04:00 而是 06/10/201812:03:59.700.所以它是 06/10/2018 12:03:59 加上 700 毫秒.如果您在 Excel 中使用格式 DD/MM/YYYY hh:mm:ss.000 格式化单元格，您会看到这一点.
对于这些值，Excel 的日期格式和 apache poi 的 DataFormatter 之间存在差异，后者使用 Java 的日期格式.当 Excel 显示日期时间值 06/10/2018 12:03:59,700 没有毫秒时，它会在内部四舍五入到秒.所以 06/10/2018 12:03:59.700 显示为 06/10/2018 12:04:00.Java 的日期格式器不会四舍五入，只是不显示毫秒.所以 06/10/2018 12:03:59.700 显示为 06/10/2018 12:03:59.
Apache poi 的
代码:
import java.io.FileInputStream;导入 org.apache.poi.util.LocaleUtil;导入 org.apache.poi.ss.usermodel.*;导入 org.apache.poi.ss.formula.ConditionalFormattingEvaluator;导入 java.util.Date;类 ExcelParseCellValues {public static void main(String[] args) 抛出异常 {工作簿工作簿 = WorkbookFactory.create(new FileInputStream("Excel.xlsx"));DataFormatter dataFormatter = new DataFormatter() {@覆盖public String formatCellValue(单元格，FormulaEvaluator 评估器，ConditionalFormattingEvaluator cfEvaluator){CellType cellType = cell.getCellType();if (cellType == CellType.FORMULA) {如果(评估器 == 空){返回 cell.getCellFormula();}cellType = evaluator.evaluateFormulaCell(cell);}if (cellType == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell, cfEvaluator)) {//我们有一个日期CellStyle 样式 = cell.getCellStyle();String dataFormatString = style.getDataFormatString();if (!dataFormatString.matches(".*(s\\.0{1,3}).*")) {//格式字符串不显示毫秒boolean use1904Windowing = false;if ( cell != null && cell.getSheet().getWorkbook() instanceof Date1904Support)use1904Windowing = ((Date1904Support)cell.getSheet().getWorkbook()).isDate1904();布尔roundSeconds = true;//我们舍入秒Date date = DateUtil.getJavaDate(cell.getNumericCellValue(), use1904Windowing, LocaleUtil.getUserTimeZone(), roundSeconds);double value = DateUtil.getExcelDate(date);return super.formatRawCellContents(value, style.getDataFormat(), dataFormatString, use1904Windowing);}}return super.formatCellValue(cell, evaluator, cfEvaluator);}};CreationHelper creationHelper = workbook.getCreationHelper();FormulaEvaluator formulaEvaluator = creationHelper.createFormulaEvaluator();Sheet sheet = workbook.getSheetAt(0);for(行行:工作表){对于(单元格单元格:行){String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);System.out.print(cellValue + "\t");}System.out.println();}workbook.close();}}结果:
值说明 浮点值 DD/MM/YYYY hh:mm:ss.000 DD/MM/YYYY hh:mm:ss您的示例值 43261,5027743056 06/10/2018 12:03:59.700 06/10/2018 12:04:00确切日期时间 12:04 43261,5027777778 06/10/2018 12:04:00.000 06/10/2018 12:04:00正好负 500 毫秒 43261,5027719907 06/10/2018 12:03:59.500 06/10/2018 12:04:00精确加 500 毫秒 43261,5027835648 06/10/2018 12:04:00.500 06/10/2018 12:04:01正好负 501 毫秒 43261,5027719792 06/10/2018 12:03:59.499 06/10/2018 12:03:59精确加 501 毫秒 43261,5027835764 06/10/2018 12:04:00.501 06/10/2018 12:04:01I'm parsing an Excel spreadsheet with a date in it. The results from POI are off by 1 second compared to what's displayed in Excel.
The unformatted data in Excel is: 43261.5027743056 The cell in Excel has a format of: mm/dd/yyyy hh:mm:ss The field in Excel displays as: 6/10/2018 12:04:00 PM
The POI parser (v 4.0.1 and 4.1.0 both) parse it as:
- Value: 43261.502774305598
- Format: mm/dd/yyyy\ hh:mm:ss
- Result: 6/10/2018 12:03:59 PM
Here's my code:
private final DataFormatter formatter;
case NUMBER:
String n = value.toString();
if (this.formatString != null) {
thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
}
else thisStr = n;
break;
Am I doing something wrong?
The problem is not the binary floating point problem. This also exists but it should not impact seconds of time.
The problem is that your value 43261.5027743056 is not really exact the date time 06/10/2018 12:04:00 but 06/10/2018 12:03:59.700. So it is 06/10/2018 12:03:59 plus 700 milliseconds. You could see this if you would formatting the cell using the format DD/MM/YYYY hh:mm:ss.000 in Excel.
For such values there is a discrepancy between Excel's date formatting and apache poi's DataFormatter, which uses Java's date format. When Excel shows the date time value 06/10/2018 12:03:59,700 without milliseconds, then it rounds to seconds internally. So 06/10/2018 12:03:59.700 is shown as 06/10/2018 12:04:00. Java's date formatters don't round but simply don't show the milliseconds. So 06/10/2018 12:03:59.700 is shown as 06/10/2018 12:03:59.
Apache poi's DateUtil provides methods which rounds seconds. But those methods seems not be used in DataFormatter.
As workaround we could override formatCellValue of DataFormatter to do so.
Complete example:
Excel:
Code:
import java.io.FileInputStream;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.formula.ConditionalFormattingEvaluator;
import java.util.Date;
class ExcelParseCellValues {
public static void main(String[] args) throws Exception {
Workbook workbook = WorkbookFactory.create(new FileInputStream("Excel.xlsx"));
DataFormatter dataFormatter = new DataFormatter() {
@Override
public String formatCellValue(Cell cell, FormulaEvaluator evaluator, ConditionalFormattingEvaluator cfEvaluator) {
CellType cellType = cell.getCellType();
if (cellType == CellType.FORMULA) {
if (evaluator == null) {
return cell.getCellFormula();
}
cellType = evaluator.evaluateFormulaCell(cell);
}
if (cellType == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell, cfEvaluator)) { //we have a date
CellStyle style = cell.getCellStyle();
String dataFormatString = style.getDataFormatString();
if (!dataFormatString.matches(".*(s\\.0{1,3}).*")) { //the format string does not show milliseconds
boolean use1904Windowing = false;
if ( cell != null && cell.getSheet().getWorkbook() instanceof Date1904Support)
use1904Windowing = ((Date1904Support)cell.getSheet().getWorkbook()).isDate1904();
boolean roundSeconds = true; //we round seconds
Date date = DateUtil.getJavaDate(cell.getNumericCellValue(), use1904Windowing, LocaleUtil.getUserTimeZone(), roundSeconds);
double value = DateUtil.getExcelDate(date);
return super.formatRawCellContents(value, style.getDataFormat(), dataFormatString, use1904Windowing);
}
}
return super.formatCellValue(cell, evaluator, cfEvaluator);
}
};
CreationHelper creationHelper = workbook.getCreationHelper();
FormulaEvaluator formulaEvaluator = creationHelper.createFormulaEvaluator();
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
System.out.print(cellValue + "\t");
}
System.out.println();
}
workbook.close();
}
}
Result:
Description of value Floatingpoint value DD/MM/YYYY hh:mm:ss.000 DD/MM/YYYY hh:mm:ss
Your example value 43261,5027743056 06/10/2018 12:03:59.700 06/10/2018 12:04:00
Exact Datetime 12:04 43261,5027777778 06/10/2018 12:04:00.000 06/10/2018 12:04:00
Exact minus 500 ms 43261,5027719907 06/10/2018 12:03:59.500 06/10/2018 12:04:00
Exact plus 500 ms 43261,5027835648 06/10/2018 12:04:00.500 06/10/2018 12:04:01
Exact minus 501 ms 43261,5027719792 06/10/2018 12:03:59.499 06/10/2018 12:03:59
Exact plus 501 ms 43261,5027835764 06/10/2018 12:04:00.501 06/10/2018 12:04:01
这篇关于Apache POI 日期解析一秒钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
