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格式化程序；案件编号:字符串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 ，但 2018年6月10日12: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 {公共静态void main(String [] args)引发异常{工作簿工作簿= WorkbookFactory.create(new FileInputStream("Excel.xlsx"));DataFormatter dataFormatter =新的DataFormatter(){@Overridepublic String formatCellValue(单元格，FormulaEvaluator评估器，ConditionalFormattingEvaluator cfEvaluator){CellType cellType = cell.getCellType();如果(cellType == CellType.FORMULA){if(evaluator == null){返回cell.getCellFormula();}cellType = evaluator.evaluateFormulaCell(cell);}if(cellType == CellType.NUMERIC& DateUtil.isCellDateFormatted(cell，cfEvaluator)){//我们有一个日期CellStyle样式= cell.getCellStyle();字符串dataFormatString = style.getDataFormatString();if(！dataFormatString.matches(.*(s \\.0 {1,3}).*")){//格式字符串不显示毫秒boolean use1904Windowing = false;if(cell！= null&& cell.getSheet().getWorkbook()Date1904Support的实例)use1904Windowing =((Date1904Support)cell.getSheet().getWorkbook()).isDate1904();boolean roundSeconds = true;//我们四舍五入日期date = DateUtil.getJavaDate(cell.getNumericCellValue()，use1904Windowing，LocaleUtil.getUserTimeZone()，roundSeconds);双精度值= DateUtil.getExcelDate(date);返回super.formatRawCellContents(value，style.getDataFormat()，dataFormatString，use1904Windowing);}}返回super.formatCellValue(cell，evaluator，cfEvaluator);}};CreationHelper creationHelper = workbook.getCreationHelper();FormulaEvaluator FormulaEvaluator = creationHelper.createFormulaEvaluator();工作表工作表= workbook.getSheetAt(0);对于(行:工作表){对于(单元格单元格:行){字符串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 ms 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:01 I'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屋！
