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.单元格样式返回相同格式的 2 位数年份 cell.getCellStyle().getDataFormatString().

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

解决方案

In

代码:

import org.apache.poi.ss.usermodel.*;导入 org.apache.poi.util.LocaleUtil;导入 java.io.FileInputStream;类 ExcelDataformatterCustomized {public static void main(String[] args) 抛出异常 {工作簿 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 评估器 = wb.getCreationHelper().createFormulaEvaluator();如果 (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(行行:工作表){对于(单元格单元格:行){String value = df.formatCellValue(cell, evaluator);System.out.println(value);}}wb.close();}}

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

短日期12.08.2018格式化日期桑塔格，2018 年 8 月 12 日

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

短日期8/12/2018格式化日期2018 年 8 月 12 日，星期日

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

短日期12/08/2018格式化日期2018 年 8 月 12 日，星期日

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?

解决方案

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

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