Java 获取单元格值作为它在excel中的显示方式

Get the cell value as how it was presented in excel

提问by Bnrdo

I am currently working on a project that reads an excel file using Apache POI.

我目前正在研究一个使用 Apache POI 读取 excel 文件的项目。

My task seems to be simple, I just need to get the cell value as it was display in the excel file. I am aware of performing a switch statement based on the cell type of a cell. But if the data is something like

我的任务似乎很简单，我只需要获取显示在 excel 文件中的单元格值。我知道根据单元格的单元格类型执行 switch 语句。但是如果数据是这样的

9,000.00

9,000.00

POI gives me 9000.0when I do getNumericCellValue(). When I force the cell to be a string type and do getStringCellValue()it then gives me 9000. What I need is the data as how it was presented in excel.

9000.0当我这样做时，POI 会给我getNumericCellValue()。当我强制单元格为字符串类型并执行getStringCellValue()此操作时，会给我9000. 我需要的是数据在 excel 中的显示方式。

I found some post telling to use DataFormatclass but as per my understanding, it requires your code to be aware of the format that the cell has. In my case, I am not aware of the format that the cell might have.

我发现一些帖子告诉使用DataFormat类，但根据我的理解，它要求您的代码了解单元格的格式。就我而言，我不知道单元格可能具有的格式。

So, how can I retrieve the cell value as how it was presented in excel?

那么，如何检索单元格值，就像它在 excel 中的显示方式一样？

采纳答案by Gagravarr

Excel stores some cells as strings, but most as numbers with special formatting rules applied to them. What you'll need to do is have those formatting rules run against the numeric cells, to produce strings that look like they do in Excel.

Excel 将一些单元格存储为字符串，但大多数存储为应用了特殊格式规则的数字。您需要做的是针对数字单元格运行这些格式规则，以生成与 Excel 中类似的字符串。

Luckily, Apache POI has a class to do just that - DataFormatter

幸运的是，Apache POI 有一个类可以做到这一点 - DataFormatter

All you need to do is something like:

您需要做的就是：

 Workbook wb = WorkbookFactory.create(new File("myfile.xls"));
 DataFormatter df = new DataFormatter();

 Sheet s = wb.getSheetAt(0);
 Row r1 = s.getRow(0);
 Cell cA1 = r1.getCell(0);

 String asItLooksInExcel = df.formatCellValue(cA1);

Doesn't matter what the cell type is, DataFormatter will format it as best it can for you, using the rules applied in Excel

无论单元格类型是什么，DataFormatter 都会使用 Excel 中应用的规则尽可能为您设置格式

回答by newuser

Use the CellTypeyou can check every thing

使用CellType你可以检查每一件事

if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC)

// the cellValue is Numeric

// cellValue 是数字

if (cellValue.getCellType() == Cell.CELL_TYPE_STRING)

// the cellValue is a String

// cellValue 是一个字符串

date is also given as numeric, at that time check the given cell is date or not by using dateUtil

日期也以数字形式给出，当时使用dateUtil检查给定单元格是否为日期

if (DateUtil.isCellDateFormatted(cellData))

after you can convert the cellvalue into the date

在您可以将单元格值转换为日期之后

回答by Patrice Rochemont

In fact, it's never possible to obtain exactly the formatted cell value with the locale defined when the cell was written. it's due to there locale observer andthe fact that the internal locale excel prefix is never reused when formatting afterwards;

事实上，永远不可能使用写入单元格时定义的语言环境准确获得格式化的单元格值。这是因为有语言环境观察者，并且在之后格式化时，内部语言环境 excel 前缀永远不会被重用；

Analysis for POI 3.17 (may change since looking how the component is internally done)

POI 3.17 的分析（可能会因为查看组件的内部完成方式而改变）

for instance : the dateConverted format of the cell style (from CellStyle.getDataFormatString()) for Locale.US with the format dd MMM yyyy hh:mm:ss is :
"[$-0409]dd MMM yyyy hh:mm:ss;@" where the local excel internal prefix = [$-0409]

例如：格式为 dd MMM yyyy hh:mm:ss 的 Locale.US 单元格样式的 dateConverted 格式（来自 CellStyle.getDataFormatString()）是：
"[$-0409]dd MMM yyyy hh:mm:ss; @" 其中本地excel内部前缀= [$-0409]

It's obtained from DateFormatConverter.localPrefixes private static map.

它是从 DateFormatConverter.localPrefixes 私有静态映射获得的。

here is some code to work around this issue :

这里有一些代码可以解决这个问题：

/**
 * Missing method in POI to enable the visualisation asIs of an cell with a
 * different locale in a xls document.
 *
 * @param style
 *            the cell style localized.
 * @return the Locale found using internal locationPrefixes.
 */
private final Locale extractLocaleFromDateCellStyle(final CellStyle style) {

    final String reOpenedFormat = style.getDataFormatString();

    LOGGER.info("Data Format of CellStyle : " + reOpenedFormat);
    Locale locale = getLocaleFromPrefixes(extractPrefixeFromPattern(reOpenedFormat));
    LOGGER.info("Found locale : " + locale);
    return locale;
}

/**
 * Extracts the internal prefix that represent the local of the style.
 *
 * @param pattern
 *            the DataFormatString of the cell style.
 * @return the prefix found.
 */
private final String extractPrefixeFromPattern(final String pattern) {

    Pattern regex = Pattern.compile(REGEX_PREFIX_PATTERN);
    Matcher match = regex.matcher(pattern);

    if (match.find()) {
        LOGGER.info("Found prefix: " + match.group(1));
        // return only the prefix
        return match.group(1);
    }
    return null;
}

/**
 * Reverse obtain the locale from the internal prefix from
 * <code>DateFormatConverter.localePrefixes</code> private static field.
 * <p>
 * Uses reflection API.
 *
 * @param prefixes
 *            the prefixes
 * @return the local corresponding tho the prefixes.
 */
public static Locale getLocaleFromPrefixes(final String prefixes) {

    try {

        @SuppressWarnings("unchecked")
        Map<String, String> map = getStaticPrivateInternalMapLocalePrefix();

        String localPrefix = null;

        // find the language_Country value matching the internal excel
        // prefix.
        for (Map.Entry<String, String> entry : map.entrySet()) {

            LOGGER.info("value : " + entry.getValue() + ", key :"
                    + entry.getKey());

            if (entry.getValue().equals(prefixes)
                    && !StringUtils.isBlank(entry.getKey())) {
                localPrefix = entry.getKey();
                break;
            }
        }

        // Generate a Locale with language, uppercase(country) info.
        LOGGER.info(localPrefix);
        if (localPrefix.indexOf('_') > 0) {
            String[] languageCountry = localPrefix.split("_");
            return new Locale(languageCountry[0],
                    StringUtils.defaultString(languageCountry[1]
                            .toUpperCase()));
        }

        // nothing found.
        return null;

        // factorized the multiples exceptions.
    } catch (Exception e) {
        throw new UnsupportedOperationException(e);
    }

}

/**
 * gets the internal code map for locale used by Excel.
 * 
 * @return the internal map.
 * @throws NoSuchFieldException
 *             if the private field name changes.
 * @throws IllegalAccessException
 *             if the accessible is restricted.
 */
private static Map<String, String> getStaticPrivateInternalMapLocalePrefix()
        throws NoSuchFieldException, IllegalAccessException {
    // REFLECTION
    Class<?> clazz = DateFormatConverter.class;
    Field fieldlocalPrefixes = (Field) clazz
            .getDeclaredField(DATE_CONVERTER_PRIVATE_PREFIXES_MAP);

    // change from private to public.
    fieldlocalPrefixes.setAccessible(true);

    @SuppressWarnings("unchecked")
    Map<String, String> map = (Map<String, String>) fieldlocalPrefixes
            .get(clazz);

    LOGGER.info("MAP localPrefixes : " + map);
    return map;
}

Thus, the following simple code should do the trick. Note the code is not fully tested with against null values and depends on the version of POI you use until they changed there LOCALE OBSERVER MADNESS:)

因此，下面的简单代码应该可以解决问题。请注意，代码并未针对空值进行全面测试，并且取决于您使用的 POI 版本，直到它们在那里更改LOCALE OBSERVER MADNESS:)

    ....
    final CellStyle cellStyle = reopenedCell.getCellStyle();

    Locale locale = extractLocaleFromDateCellStyle(cellStyle);
    LOGGER.info("FOUND LOCAL : " + locale);

    // use the same local from the cell style during writing.
    DataFormatter df = new DataFormatter(locale);

    String reOpenValue = df.formatCellValue(reopenedCell);

Regards.

问候。