1. Overview

1.概述

A Microsoft Excel cell can have different types like string, numeric, boolean, and formula.

一个Microsoft Excel单元格可以有不同的类型，如字符串、数字、布尔值和公式。

In this quick tutorial, we’ll show how to read the cell value as a string – regardless of the cell type – with Apache POI.

在这个快速教程中，我们将展示如何用Apache POI将单元格值读成字符串–无论单元格类型如何。

2. Apache POI

2.阿帕奇POI

To begin with, we first need to add the poi dependency to our project pom.xml file:

首先，我们首先需要将poi依赖性添加到我们的项目pom.xml文件中。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.0</version>
</dependency>

Apache POI uses the Workbook interface to represent an Excel file. It also uses Sheet, Row, and Cell interfaces to model different levels of elements in an Excel file. At the Cell level, we can use its getCellType() method to get the cell type. Apache POI supports the following cell types:

Apache POI使用Workbook 接口来表示一个Excel文件。它还使用 Sheet, Row, 和 Cell 接口来模拟 Excel 文件中不同级别的元素。在Cell层，我们可以使用其getCellType()方法来获得单元格类型。Apache POI支持以下单元类型。

BLANK
BOOLEAN
ERROR
FORMULA
NUMERIC
STRING

If we want to display the Excel file content on the screen, we would like to get the string representation of a cell, instead of its raw value. Therefore, for cells that are not of type STRING, we need to convert their data into string values.

如果我们想在屏幕上显示Excel文件的内容，我们希望得到一个单元格的字符串表示，而不是其原始值。因此，对于不是STRING类型的单元格，我们需要将其数据转换成字符串值。

3. Get Cell String Value

3.获取单元格字符串值

We can use DataFormatter to fetch the string value of an Excel cell. It can get a formatted string representation of the value stored in a cell. For example, if a cell’s numeric value is 1.234, and the format rule of this cell is two decimal points, we’ll get string representation “1.23”:

我们可以使用DataFormatter来获取Excel单元格的字符串值。它可以获得存储在单元格中的数值的格式化字符串表示。例如，如果一个单元格的数值是1.234，而这个单元格的格式规则是两个小数点，我们将得到字符串表示 “1.23”。

Cell cell = // a numeric cell with value of 1.234 and format rule "0.00"

DataFormatter formatter = new DataFormatter();
String strValue = formatter.formatCellValue(cell);

assertEquals("1.23", strValue);

Therefore, the result of DataFormatter.formatCellValue() is the display string exactly as it appears in Excel.

因此，DataFormatter.formatCellValue()的结果是显示字符串，与它在Excel中的显示完全一致。

4. Get String Value of a Formula Cell

4.获取公式单元格的字符串值

If the cell’s type is FORMULA, the previous method will return the original formula string, instead of the calculated formula value. Therefore, to get the string representation of the formula value, we need to use FormulaEvaluator to evaluate the formula:

如果单元格的类型是FORMULA，前面的方法将返回原始的公式字符串，而不是计算的公式值。因此，为了获得公式值的字符串表示，我们需要使用FormulaEvaluator来评估公式。

Workbook workbook = // existing Workbook setup
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

Cell cell = // a formula cell with value of "SUM(1,2)"

DataFormatter formatter = new DataFormatter();
String strValue = formatter.formatCellValue(cell, evaluator);

assertEquals("3", strValue);

This method is general to all cell types. If the cell type is FORMULA, we’ll evaluate it using the given FormulaEvaluator. Otherwise, we’ll return the string representation without any evaluations.

这个方法适用于所有的单元格类型。如果单元格类型是FORMULA，我们将使用给定的FormulaEvaluator来评估它。否则，我们将返回没有任何评估的字符串表示。

5. Summary

5.摘要

In this quick article, we showed how to get the string representation of an Excel cell, regardless of its type. As always, the source code for the article is available over on GitHub.

在这篇快速文章中，我们展示了如何获得Excel单元格的字符串表示，无论其类型如何。一如既往，该文章的源代码可在GitHub上获取。