POI DataFormatter返回2位数字的年份，而不是日期单元格的4位数字的年份 [英] POI DataFormatter returns 2 digits year instead of 4 digits year for date cells

问题描述

Excel单元格的日期格式为m/d/yyyy，并显示4位数字的年份.但是，POI会以另一格式m/d/yy返回日期.单元格样式返回相同的格式，年份为cell.getCellStyle().getDataFormatString() 2位.

Excel cell has date format m/d/yyyy and show date with 4 digits year. However POI returns date in another format m/d/yy. The cell style returns the same format with 2 digits year cell.getCellStyle().getDataFormatString().

是否可以使用Excel在POI中获得与我看到的格式相同的格式?

Is it possible to get the same format as I see in Excel with POI?

推荐答案

在 Excel Cell中样式问题我已经表明，如果Excel中使用默认日期格式(Short Date)格式化的日期仅在文件中存储了格式ID 0xE(14)，而没有格式模式存储在任何地方.因此Short Date在Excel中的显示方式取决于系统的区域设置.

In Excel Cell Style issue I have shown that if a date in Excel which is formatted using the default date format (Short Date) only has the format id 0xE (14) stored in the file and there is not a format pattern stored anywhere. So how Short Date will be displayed in Excel depends on the locale settings of the system.

该答案中还显示了如何在不使用apache poi的DataFormatter的情况下解决此问题.

How working around this issue without using apache poi's DataFormatter is also shown in that answer.

使用apache poi的DataFormatter，我们可以通过自定义DataFormatter来解决此问题.

Using apache poi's DataFormatter we can work around this issue with customizing the DataFormatter.

示例:

Excel:

代码:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.LocaleUtil;

import java.io.FileInputStream;

class ExcelDataformatterCustomized {

 public static void main(String[] args) throws Exception {

  Workbook wb  = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  LocaleUtil.setUserLocale(java.util.Locale.GERMANY);
  //LocaleUtil.setUserLocale(java.util.Locale.US);
  //LocaleUtil.setUserLocale(java.util.Locale.UK);

  DataFormatter df = new DataFormatter();
  FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

  if (LocaleUtil.getUserLocale().equals(java.util.Locale.GERMANY)) {
   df.addFormat("m/d/yy", new java.text.SimpleDateFormat("dd.MM.yyyy"));
  } else if (LocaleUtil.getUserLocale().equals(java.util.Locale.US)) {
   df.addFormat("m/d/yy", new java.text.SimpleDateFormat("M/d/yyyy"));
  } else if (LocaleUtil.getUserLocale().equals(java.util.Locale.UK)) {
   df.addFormat("m/d/yy", new java.text.SimpleDateFormat("dd/MM/yyyy"));
  }

  Sheet sheet = wb.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {

     String value = df.formatCellValue(cell, evaluator);
     System.out.println(value);

   }
  }
  wb.close();
 }
}

设置了java.util.Locale.GERMANY的结果:

Short Date
12.08.2018
Formatted Date
Sonntag, August 12, 2018

设置了java.util.Locale.US的结果:

Short Date
8/12/2018
Formatted Date
Sunday, August 12, 2018

设置了java.util.Locale.UK的结果:

Short Date
12/08/2018
Formatted Date
Sunday, August 12, 2018

这篇关于POI DataFormatter返回2位数字的年份，而不是日期单元格的4位数字的年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！