时间戳

首页 > 解决方案 > Apache POI Date Parsing One Second Off

apache-poi - Apache POI Date Parsing One Second Off

问题描述

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:

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?

标签： apache-poi

解决方案

问题不是二进制浮点问题。这也存在，但它不应该影响几秒钟的时间。

问题是您的值43261.5027743056并不是真正准确的日期时间06/10/2018 12:04:00，而是06/10/2018 12:03:59.700. 所以它是06/10/2018 12:03:59加700毫秒。如果您使用DD/MM/YYYY hh:mm:ss.000.Excel

Excel对于此类值， ' 日期格式和使用' 日期格式apache poi的 ' 之间存在差异。当显示不带毫秒的日期时间值时，它会在内部四舍五入到秒。所以显示为。的日期格式化程序不四舍五入，但根本不显示毫秒。所以显示为。DataFormatterJavaExcel06/10/2018 12:03:59,70006/10/2018 12:03:59.70006/10/2018 12:04:00Java06/10/2018 12:03:59.70006/10/2018 12:03:59

Apache poi的DateUtil提供了对秒进行舍入的方法。但那些方法似乎没有用在DataFormatter.

formatCellValue作为解决方法，我们可以覆盖DataFormatter这样做。

完整示例：

电子表格：

在此处输入图像描述

代码：

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();

 }
}

结果：

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

推荐阅读