POI DataFormatter returns 2 digits year instead of

问题:

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

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

回答1:

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.

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

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

Example:

Excel:

Code:

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

Result having java.util.Locale.GERMANY set:

Short Date
12.08.2018
Formatted Date
Sonntag, August 12, 2018

Result having java.util.Locale.US set:

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

Result having java.util.Locale.UK set:

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