Excel单元格样式问题 [英] Excel Cell Style issue

问题描述

我使用以下代码从 XLSX 文件中获取日期值.这对于某些 XLSX 文件来说非常有效，但它没有给出 XLSX 文件中的确切日期格式.此问题针对某些文件.

例如，我有这样的日期 21/01/2016 (dd/mm/yyyy)，但阅读后，它给出的日期为 01/21/16(mm/日/年)

有没有其他办法获得cellstyle?

是 XLSX 文件的问题吗?

String dateFmt = cell.getCellStyle().getDataFormatString();如果(DateUtil.isCellDateFormatted(单元格)){double val = cell.getNumericCellValue();日期日期 = DateUtil.getJavaDate(val);String dateFmt = cell.getCellStyle().getDataFormatString();System.out.println("dateFmt"+dateFmt);value = new CellDateFormatter(dateFmt).format(date);System.out.println("日期"+值);}

解决方案

如果包含日期的单元格被格式化为默认日期格式(Short Date)，那么只有格式id 0xE (14) 存储在文件中.请参阅

在英国的英国 Windows 系统中，默认为 DD/MM/YYYY.

但是如果您在系统设置中将Short Date的设置更改为JJJJ-MM-TT，那么这种格式也会在Excel中显示为numFmtId="14".

因此，要了解 Excel 如何使用 numFmtId="14" 准确显示日期，您需要知道 Region and Language 中的确切 Windows 系统设置.

因此，如果不知道系统的区域设置，apache POI 也无法知道应该如何显示，因为该文件不包含有关此的信息.所以它将假设 en-us 语言环境.这导致日期为 m/d/yy.

您可以检查是否使用格式 id 14，如果使用，请定义您自己的默认日期格式.

 if (DateUtil.isCellDateFormatted(cell)) {日期日期 = cell.getDateCellValue();System.out.println(日期);字符串 dateFmt = "";if (cell.getCellStyle().getDataFormat() == 14) {//没有显式格式的默认短日期dateFmt = "日/月/年";//默认日期格式} else {//具有显式格式的其他数据格式dateFmt = cell.getCellStyle().getDataFormatString();}System.out.println("dateFmt" + dateFmt);String value = new CellDateFormatter(dateFmt).format(date);System.out.println("日期" + 值);}

需要明确的是:这一切仅适用于 numFmtId="14" cell.getCellStyle().getDataFormat() == 14.所有其他日期格式将具有显式数据格式字符串 cell.getCellStyle().getDataFormatString()，因此 POI 可以像在 Excel 中一样显示它们.

<小时>

见 POI DataFormatter 为日期单元格返回 2 位数年份而不是 4 位数年份，了解如何使用 DataFormatter 解决此问题.

I am using below code to get date value from XLSX file. This is working absolutely fine for some XLSX files, but it is not giving exact date format which is in XLSX file. This issue is for some files.

For example, I have date like this 21/01/2016 (dd/mm/yyyy), but after reading, it gives date as 01/21/16(mm/dd/yy)

Is there any other way to get cellstyle?

Is it issue of XLSX file?

String dateFmt = cell.getCellStyle().getDataFormatString();
                
if (DateUtil.isCellDateFormatted(cell)) {
    double val = cell.getNumericCellValue();
    Date date = DateUtil.getJavaDate(val);

    String dateFmt = cell.getCellStyle().getDataFormatString();
                                    
    System.out.println("dateFmt "+dateFmt);

    value = new CellDateFormatter(dateFmt).format(date);
                                    
    System.out.println("Date "+value);
                                
}

解决方案

If the cell, which is containing the date, is formatted as the default date format (Short Date), then only the format id 0xE (14) is stored in the file. See https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html. The *.xlsx file contains only

<xf numFmtId="14" ... applyNumberFormat="1"/>

in styles.xml. There is no special formatCode saved for this numFmtId.

So how this will be displayed in Excel depends on the locale settings of the system.

For example with my German Windows system the numFmtId="14" will be displayed as TT.MM.JJJJ as is set in Region and Language settings:

In English Great Britain Windows systems this will be DD/MM/YYYY per default.

But if you change the setting for Short Date in the system settings, to JJJJ-MM-TT for example, then this format will also displayed in Excel with the numFmtId="14".

So to know how exactly Excel will display the date with the numFmtId="14", one needs to know the exact Windows system settings in Region and Language.

So also apache POI can't know how this should be displayed without knowing the locale settings of the system, since the file contains no infomation about this. So it will assume en-us locale. This leads to m/d/yy for date.

You could check if the format id 14 is used and if so define your own default date format.

   if (DateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();

    System.out.println(date);

    String dateFmt = "";

    if (cell.getCellStyle().getDataFormat() == 14) { //default short date without explicit formatting
     dateFmt = "dd/mm/yyyy"; //default date format for this
    } else { //other data formats with explicit formatting
     dateFmt = cell.getCellStyle().getDataFormatString();
    }

    System.out.println("dateFmt " + dateFmt);

    String value = new CellDateFormatter(dateFmt).format(date);

    System.out.println("Date " + value);

   }

To be clear: This all is only with numFmtId="14" cell.getCellStyle().getDataFormat() == 14. All other date formats will have explicit data format strings cell.getCellStyle().getDataFormatString() and so POI can display them exactly like in Excel.

---

See POI DataFormatter returns 2 digits year instead of 4 digits year for date cells for how to use DataFormatter to work around this issue.

这篇关于Excel单元格样式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！