Java 如何将 Excel 单元格中的数字字符串作为字符串（不是数字）读取？

How can I read numeric strings in Excel cells as string (not numbers)?

提问by joycollector

1. I have excel file with such contents:

   • A1: SomeString

   • A2: 2

   All fields are set to String format.

2. When I read the file in java using POI, it tells that A2 is in numeric cell format.

3. The problem is that the value in A2 can be 2 or 2.0 (and I want to be able to distinguish them) so I can't just use .toString().

1. 我有这样的内容的excel文件：

   • A1：一些字符串

   • A2：2

   所有字段都设置为字符串格式。

2. 当我使用 POI 在 java 中读取文件时，它告诉 A2 是数字单元格格式。

3. 问题是 A2 中的值可以是 2 或 2.0（我希望能够区分它们）所以我不能只使用.toString().

What can I do to read the value as string?

我该怎么做才能将值读取为字符串？

采纳答案by wil

I had same problem. I did cell.setCellType(Cell.CELL_TYPE_STRING);before reading the string value, which solved the problem regardless of how the user formatted the cell.

我有同样的问题。我cell.setCellType(Cell.CELL_TYPE_STRING);在读取字符串值之前这样做了，无论用户如何格式化单元格，它都解决了问题。

回答by WolfmanDragon

cast to an int then do a .toString(). It is ugly but it works.

转换为 int 然后执行.toString(). 这是丑陋的，但它的工作原理。

回答by Turismo

We had the same problem and forced our users to format the cells as 'text' beforeentering the value. That way Excel correctly stores even numbers as text. If the format is changed afterwards Excel only changes the way the value is displayed but does not change the way the value is stored unless the value is entered again (e.g. by pressing return when in the cell).

我们遇到了同样的问题，并强迫我们的用户在输入值之前将单元格格式化为“文本” 。这样 Excel 正确地将偶数存储为文本。如果随后更改格式，Excel 只会更改值的显示方式，但不会更改值的存储方式，除非再次输入该值（例如，在单元格中按回车键）。

Whether or not Excel correctly stored the value as text is indicated by the little green triangle that Excel displays in the left upper corner of the cell if it thinks the cell contains a number but is formated as text.

Excel 是否将值正确存储为文本由 Excel 显示在单元格左上角的小绿色三角形指示，如果它认为单元格包含数字但格式为文本。

回答by datatoo

Do you control the excel worksheet in anyway? Is there a template the users have for giving you the input? If so, you can have code format the input cells for you.

无论如何你控制excel工作表吗？是否有用户提供输入的模板？如果是这样，您可以让代码为您设置输入单元格的格式。

回答by Simon D

It looks like this can't be done in the current version of POI, based on the fact that this bug:

基于此错误的事实，在当前版本的 POI 中似乎无法完成此操作：

https://issues.apache.org/bugzilla/show_bug.cgi?id=46136

https://issues.apache.org/bugzilla/show_bug.cgi?id=46136

is still outstanding.

仍然很出色。

回答by Asif Shahzad

When we read the MS Excel's numeric cell value using Apache POI library, it read it as numeric. But sometime we want it to read as string (e.g. phone numbers, etc.). This is how I did it:

当我们使用 Apache POI 库读取 MS Excel 的数值单元格值时，它会将其读取为数值。但有时我们希望它读取为字符串（例如电话号码等）。我是这样做的：

1. Insert a new column with first cell =CONCATENATE("!",D2). I assume D2 is cell id of your phone-number column. Drag new cell up to end.

2. Now if you read the cell using POI, it will read the formula instead of calculated value. Now do following:

3. Add another column

4. Select complete column created in step 1. and choose Edit->COPY

5. Go to top cell of column created in step 3. and Select Edit->Paste Special

6. In the opened window, Select "Values" radio button

7. Select "OK"

8. Now read using POI API ... after reading in Java ... just remove the first character i.e. "!"

1. 插入一个带有第一个单元格 =CONCATENATE("!",D2) 的新列。我假设 D2 是您的电话号码列的单元格 ID。将新单元格向上拖动到结尾。

2. 现在，如果您使用 POI 读取单元格，它将读取公式而不是计算值。现在执行以下操作：

3. 添加另一列

4. 选择在步骤 1 中创建的完整列。然后选择 Edit->COPY

5. 转到第 3 步中创建的列的顶部单元格。然后选择“编辑”->“选择性粘贴”

6. 在打开的窗口中，选择“值”单选按钮

7. 选择“确定”

8. 现在使用 POI API 阅读……在用 Java 阅读之后……只需删除第一个字符，即“！”

回答by iTake

Try:

尝试：

new java.text.DecimalFormat("0").format( cell.getNumericCellValue() )

Should format the number correctly.

应该正确格式化数字。

回答by Gagravarr

I don't think we had this class back when you asked the question, but today there is an easy answer.

当你问这个问题时，我认为我们没有回到这门课，但今天有一个简单的答案。

What you want to do is use the DataFormatter class. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back.

您想要做的是使用DataFormatter 类。您将这个单元格传递给它，它会尽最大努力返回一个字符串，其中包含 Excel 将为您显示的该单元格内容。如果你传递给它一个字符串单元格，你会得到字符串。如果您将一个应用了格式规则的数字单元格传递给它，它将根据它们格式化数字并将字符串返回给您。

For your case, I'd assume that the numeric cells have an integer formatting rule applied to them. If you ask DataFormatter to format those cells, it'll give you back a string with the integer string in it.

对于您的情况，我假设数字单元格应用了整数格式规则。如果您要求 DataFormatter 格式化这些单元格，它会返回一个包含整数字符串的字符串。

Also, note that lots of people suggest doing cell.setCellType(Cell.CELL_TYPE_STRING), but the Apache POI JavaDocs quite clearly state that you shouldn't do this! Doing the setCellTypecall will loose formatting, as the javadocs explainthe only way to convert to a String with formatting remaining is to use the DataFormatter class.

另外，请注意，很多人建议这样做cell.setCellType(Cell.CELL_TYPE_STRING)，但是Apache POI JavaDocs 非常明确地指出您不应该这样做！执行setCellType调用会丢失格式，因为javadocs 解释了转换为 String 并保留格式的唯一方法是使用DataFormatter 类。

回答by Mark Holmes

I also have had a similar issue on a data set of thousands of numbers and I think that I have found a simple way to solve. I needed to get the apostrophe inserted before a number so that a separate DB import always sees the numbers as text. Before this the number 8 would be imported as 8.0.

我在数千个数字的数据集上也遇到了类似的问题，我认为我找到了一种简单的方法来解决。我需要在数字之前插入撇号，以便单独的数据库导入始终将数字视为文本。在此之前，数字 8 将作为 8.0 导入。

Solution:

解决方案：

• Keep all the formatting as General.
• Here I am assuming numbers are stored in Column A starting at Row 1.
• Put in the ' in Column B and copy down as many rows as needed. Nothing appears in the worksheet but clicking on the cell you can see the apostophe in the Formula bar.
• In Column C: =B1&A1.
• Select all the Cells in Column C and do a Paste Special into Column D using the Values option.

• 保持所有格式为常规。
• 在这里，我假设数字存储在 A 列中，从第 1 行开始。
• 放入 B 列中的 ' 并根据需要复制尽可能多的行。工作表中没有任何显示，但单击单元格可以在公式栏中看到撇号。
• 在 C 列中：=B1&A1。
• 选择 C ​​列中的所有单元格，然后使用值选项将特殊粘贴到 D 列中。

Hey Presto all the numbers but stored as Text.

嘿 Presto 所有数字，但存储为文本。

回答by Mark Farnsworth

As long as the cell is in text format before the user types in the number, POI will allow you to obtain the value as a string. One key is that if there is a small green triangle in the upper left-hand corner of cell that is formatted as Text, you will be able to retrieve its value as a string (the green triangle appears whenever something that appears to be a number is coerced into a text format). If you have Text formatted cells that contain numbers, but POI will not let you fetch those values as strings, there are a few things you can do to the Spreadsheet data to allow that:

只要在用户输入数字之前单元格是文本格式，POI 将允许您以字符串形式获取值。一个关键是，如果单元格的左上角有一个格式化为文本的绿色小三角形，您将能够以字符串形式检索其值（每当出现数字时，就会出现绿色三角形被强制转换为文本格式）。如果您有包含数字的文本格式单元格，但 POI 不允许您将这些值作为字符串获取，您可以对电子表格数据执行一些操作以允许：

• Double click on the cell so that the editing cursor is present inside the cell, then click on Enter (which can be done only one cell at a time).
• Use the Excel 2007 text conversion function (which can be done on multiple cells at once).
• Cut out the offending values to another location, reformat the spreadsheet cells as text, then repaste the previously cut out values as Unformatted Valuesback into the proper area.

• 双击单元格，使编辑光标出现在单元格内，然后单击 Enter（一次只能完成一个单元格）。
• 使用 Excel 2007 文本转换功能（可以一次在多个单元格上完成）。
• 将有问题的值剪切到另一个位置，将电子表格单元格重新格式化为文本，然后将先前剪切的值作为未格式化的值重新粘贴回适当的区域。

One final thing that you can do is that if you are using POI to obtain data from an Excel 2007 spreadsheet, you can the Cell class 'getRawValue()' method. This does not care what the format is. It will simply return a string with the raw data.

您可以做的最后一件事是，如果您使用 POI 从 Excel 2007 电子表格中获取数据，则可以使用 Cell 类的“getRawValue()”方法。这并不关心格式是什么。它将简单地返回一个带有原始数据的字符串。