blocks|key|338031|text|强制转换为整数，然后执行.toString()。它是丑陋的，但它是有效的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|338032|entityMap^0|C|B|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

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

blocks|key|340574|text|我们也遇到了同样的问题，迫使我们的用户在输入值之前将单元格格式化为'text‘。这样Excel才能正确地将偶数存储为文本。如果之后更改了格式，Excel只会更改值的显示方式，但不会更改值的存储方式，除非再次输入值(例如，在单元格中按return键)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|340575|如果Excel认为单元格包含数字但格式为文本，则Excel将在单元格的左上角显示绿色小三角形，以确定Excel是否将该值正确地存储为文本。|340576|entityMap^0|13|2E|0|0|1X|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

We had the same problem and forced our users to format the cells as 'text' before entering 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).

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.

blocks|key|337059|text|您是否在任何情况下控制excel工作表？用户有没有为你提供输入的模板？如果是这样，您可以让代码为您设置输入单元格的格式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|337060|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|337062|text|我也有同样的问题。我在读取字符串值之前执行了cell.setCellType(Cell.CELL_TYPE_STRING);，这解决了无论用户如何格式化单元格的问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|337063|entityMap^0|M|14|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

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

blocks|key|335643|text|基于这个bug的事实，在当前版本的POI中似乎不能做到这一点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|335644|https://issues.apache.org/bugzilla/show_bug.cgi?id=46136|offset|length|335645|仍然是杰出的。|335646|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1K|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

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

<a href="https://issues.apache.org/bugzilla/show_bug.cgi?id=46136" rel="nofollow">https://issues.apache.org/bugzilla/show_bug.cgi?id=46136</a>

is still outstanding.

blocks|key|337074|text|当我们使用Apache+POI库读取MS+Excel的数值单元格值时，它将其读取为数值。但有时我们希望将其读取为字符串(例如，电话号码等)。我是这样做的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|337075|337076|插入第一个单元格=CONCATENATE("!"，D2)的新列。我假设D2是您的电话号码列的单元格id。将新单元格向上拖动到末尾。|ordered-list-item|337077|Now如果您使用相关点读取单元格，它将读取公式而不是计算值。现在执行以下操作：|337078|Add+another+column+|337079|选择在步骤1中创建的完整列。选择编辑->COPY|337080|转到在步骤3创建的列的顶部单元格。选择编辑->粘贴特殊|337081|在打开的窗口中，选择"Values“单选|337082|+"OK"|337083|Now+read+using+POI+|337084|+...在Java中阅读之后...只需删除第一个字符，即"!"|337085|337086|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|13|8|@]|9|@]|A|$]]|$1|L|3|M|5|E|7|14|8|@]|9|@]|A|$]]|$1|N|3|O|5|E|7|15|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|16|8|@]|9|@]|A|$]]|$1|R|3|S|5|E|7|17|8|@]|9|@]|A|$]]|$1|T|3|U|5|E|7|18|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|X|$]]

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:

<ol>
<li>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.</li>
<li>Now if you read the cell using POI, it will read the formula instead of calculated value. Now do following:</li>
<li>Add another column</li>
<li>Select complete column created in step 1. and choose Edit->COPY</li>
<li>Go to top cell of column created in step 3. and Select Edit->Paste Special</li>
<li>In the opened window, Select "Values" radio button</li>
<li>Select "OK"</li>
<li>Now read using POI API ... after reading in Java ... just remove the first character i.e. "!"</li>
</ol>

blocks|key|337088|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|337089|new+java.text.DecimalFormat("0").format(+cell.getNumericCellValue()+)|code-block|syntax|javascript|337090|应正确设置数字格式。|337091|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try:

<pre><code>new java.text.DecimalFormat("0").format( cell.getNumericCellValue() )
</code></pre>

Should format the number correctly.

blocks|key|340654|text|当你问这个问题的时候，我不认为我们有这门课，但是今天有一个简单的答案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|340655|您想要做的是使用DataFormatter+class。您将其传递给一个单元格，它会尽力返回一个字符串，其中包含Excel将为该单元格显示的内容。如果你向它传递一个字符串单元格，你将会得到字符串。如果您向它传递一个应用了格式规则的数字单元格，它将根据这些规则格式化数字并返回给您字符串。|offset|length|340656|对于您的例子，我假设数值单元格应用了整数格式规则。如果您要求DataFormatter对这些单元格进行格式化，它将返回一个包含整数字符串的字符串。|340657|此外，请注意，许多人建议使用cell.setCellType(Cell.CELL_TYPE_STRING)，但Apache+POI+JavaDocs+quite+clearly+state+that+you+shouldn't+do+this！执行setCellType调用将丢失格式，因为javadocs+explain转换为剩余格式的字符串的唯一方法是使用DataFormatter+class。|style|CODE|340658|使用这个类的一个简单示例：|340659|DataFormatter+dataFormatter+=+new+DataFormatter();
String+formattedCellStr+=+dataFormatter.formatCellValue(cell);|code-block|syntax|javascript|340660|entityMap|0|LINK|mutability|MUTABLE|url|http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html|1|https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%2528int%2529|2|https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%2528int%2529|3^0|0|8|J|0|0|0|E|13|3G|B|1J|1U|1|41|G|2|50|J|3|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@]|9|@$D|17|E|18|1|19]]|A|$]]|$1|F|3|G|5|6|7|1A|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1B|8|@$D|1C|E|1D|J|K]|$D|1E|E|1F|J|K]]|9|@$D|1G|E|1H|1|1I]|$D|1J|E|1K|1|1L]|$D|1M|E|1N|1|1O]]|A|$]]|$1|L|3|M|5|6|7|1P|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1Q|8|@]|9|@]|A|$Q|R]]|$1|S|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]|10|$5|V|W|X|A|$Y|11]]|12|$5|V|W|X|A|$Y|13]]|14|$5|V|W|X|A|$Y|Z]]]]

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 <a href="http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html" rel="nofollow noreferrer">DataFormatter class</a>. 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.
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.
Also, note that lots of people suggest doing <code>cell.setCellType(Cell.CELL_TYPE_STRING)</code>, but the <a href="https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29" rel="nofollow noreferrer">Apache POI JavaDocs quite clearly state that you shouldn't do this</a>! Doing the <code>setCellType</code> call will loose formatting, as the <a href="https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29" rel="nofollow noreferrer">javadocs explain</a> the only way to convert to a String with formatting remaining is to use the <a href="http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html" rel="nofollow noreferrer">DataFormatter class</a>.
A simple example of using this class:
<pre class="lang-java prettyprint-override"><code>DataFormatter dataFormatter = new DataFormatter();
String formattedCellStr = dataFormatter.formatCellValue(cell);
</code></pre>

blocks|key|3317147|text|我在一个包含数千个数字的数据集上也遇到了类似的问题，我想我已经找到了一个简单的解决方法。我需要在数字之前插入撇号，以便单独的DB导入始终将数字视为文本。在此之前，数字8将被导入为8.0。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3317148|解决方案：|3317149|3317150|将所有格式保持为常规。|unordered-list-item|3317151|在这里，我假设数字存储在列A中，从第1行开始。|3317152|在列B中输入‘，并根据需要向下复制任意多行。工作表中没有显示任何内容，但单击单元格，您可以在公式栏中看到倒数第二行。C栏中的|3317153|：=B1&A1。|3317154|选择C列中的所有单元格，然后使用值选项将特殊内容粘贴到D列中。|3317155|3317156|嘿，复制所有的数字，但以文本形式存储。|3317157|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|X|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|Z|8|@]|9|@]|A|$]]|$1|L|3|M|5|G|7|10|8|@]|9|@]|A|$]]|$1|N|3|O|5|G|7|11|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

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.

Solution:

<ul>
<li>Keep all the formatting as General.</li>
<li>Here I am assuming numbers are stored in Column A starting at Row 1. </li>
<li>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. </li>
<li>In Column C: =B1&amp;A1. </li>
<li>Select all the Cells in Column C and do a Paste Special into Column D using the Values option.</li>
</ul>

Hey Presto all the numbers but stored as Text.

blocks|key|3104344|text|只要在用户键入数字之前单元格是文本格式，POI就允许您以字符串形式获取值。一个关键是，如果在单元格的左上角有一个格式化为文本的绿色小三角形，您将能够以字符串的形式检索它的值(无论何时将一些看似数字的东西强制转换为文本格式，都会出现绿色三角形)。如果您有包含数字的文本格式单元格，但POI不允许您将这些值作为字符串提取，则可以对电子表格数据执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3104345|3104346|双击单元格，使编辑光标出现在单元格内，然后单击Enter+(一次只能输入一个单元格)。|unordered-list-item|3104347|使用Excel2007文本转换功能(可以一次对多个单元格执行此操作)。|3104348|将有问题的值剪切到另一个位置，将电子表格单元格重新格式化为文本，然后将以前剪切出来的值重新粘贴为未格式化的值，重新粘贴回适当的区域。|3104349|3104350|您可以做的最后一件事是，如果您正在使用POI从Excel2007电子表格中获取数据，则可以使用Cell类的“getRawValue()”方法。这并不关心格式是什么。它将简单地返回一个包含原始数据的字符串。|3104351|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|P|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|S|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

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: 

<ul>
<li>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).</li>
<li>Use the Excel 2007 text conversion function (which can be done on multiple cells at once).</li>
<li>Cut out the offending values to another location, reformat the spreadsheet cells as text, then repaste the previously cut out values as Unformatted Values back into the proper area.</li>
</ul>

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.

blocks|key|335871|text|是的，这非常好用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|335872|推荐：|335873|++++++++DataFormatter+dataFormatter+=+new+DataFormatter();
++++++++String+value+=+dataFormatter.formatCellValue(cell);|code-block|syntax|javascript|335874|旧的：|335875|cell.setCellType(Cell.CELL_TYPE_STRING);|335876|即使您在从具有公式的cell中检索值时遇到问题，这种方法仍然有效。|offset|length|style|CODE|335877|entityMap^0|0|0|0|0|0|A|4|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|Z|8|@$O|10|P|11|Q|R]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Yes, this works perfectly

recommended:

<pre><code> DataFormatter dataFormatter = new DataFormatter();
 String value = dataFormatter.formatCellValue(cell);
</code></pre>

old:

<pre><code>cell.setCellType(Cell.CELL_TYPE_STRING);
</code></pre>

even if you have a problem with retrieving a value from <code>cell</code> having formula, still this works.

blocks|key|3104417|text|下面的代码适用于任何类型的单元格。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3104418|InputStream+inp+=getClass().getResourceAsStream("filename.xls"));
Workbook+wb+=+WorkbookFactory.create(inp);
DataFormatter+objDefaultFormat+=+new+DataFormatter();
FormulaEvaluator+objFormulaEvaluator+=+new+HSSFFormulaEvaluator((HSSFWorkbook)+wb);

Sheet+sheet=+wb.getSheetAt(0);
Iterator<Row>+objIterator+=+sheet.rowIterator();

while(objIterator.hasNext()){

++++Row+row+=+objIterator.next();
++++Cell+cellValue+=+row.getCell(0);
++++objFormulaEvaluator.evaluate(cellValue);+//+This+will+evaluate+the+cell,+And+any+type+of+cell+will+return+string+value
++++String+cellValueStr+=+objDefaultFormat.formatCellValue(cellValue,objFormulaEvaluator);

}|code-block|syntax|javascript|3104419|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The below code worked for me for any type of cell.

<pre><code>InputStream inp =getClass().getResourceAsStream("filename.xls"));
Workbook wb = WorkbookFactory.create(inp);
DataFormatter objDefaultFormat = new DataFormatter();
FormulaEvaluator objFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);

Sheet sheet= wb.getSheetAt(0);
Iterator&lt;Row&gt; objIterator = sheet.rowIterator();

while(objIterator.hasNext()){

 Row row = objIterator.next();
 Cell cellValue = row.getCell(0);
 objFormulaEvaluator.evaluate(cellValue); // This will evaluate the cell, And any type of cell will return string value
 String cellValueStr = objDefaultFormat.formatCellValue(cellValue,objFormulaEvaluator);

}
</code></pre>

blocks|key|338289|text|如果单元格类型为numeric，则getStringCellValue返回NumberFormatException。如果不想将单元格类型更改为字符串，可以这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|338290|String+rsdata+=+"";
try+{
++++rsdata+=+cell.getStringValue();
}+catch+(NumberFormatException+ex)+{
++++rsdata+=+cell.getNumericValue()+%2B+"";
}|code-block|syntax|javascript|338291|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

getStringCellValue returns NumberFormatException if the cell type is numeric. If you don't want to change the cell type to string, you can do this.

<pre><code>String rsdata = "";
try {
 rsdata = cell.getStringValue();
} catch (NumberFormatException ex) {
 rsdata = cell.getNumericValue() + "";
}
</code></pre>

blocks|key|337265|text|cell.setCellType(Cell.CELL_TYPE_STRING)；在我身上运行良好|type|unstyled|depth|inlineStyleRanges|entityRanges|data|337266|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

cell.setCellType(Cell.CELL_TYPE_STRING); is working fine for me

blocks|key|3317339|text|当不希望修改单元格的类型时，我建议使用以下方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3317340|if(cell.getCellType()+==+Cell.CELL_TYPE_NUMERIC)+{
++++String+str+=+NumberToTextConverter.toText(cell.getNumericCellValue())
}|code-block|syntax|javascript|3317341|NumberToTextConverter可以使用Excel的规则正确地将双精度值转换为文本，而不会损失精度。|3317342|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I would recommend the following approach when modifying cell's type is undesirable:

<pre><code>if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
 String str = NumberToTextConverter.toText(cell.getNumericCellValue())
}
</code></pre>

NumberToTextConverter can correctly convert double value to a text using Excel's rules without precision loss.

blocks|key|340813|text|正如在Poi的JavaDocs+(https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%2528int%2529)中已经提到的，不要使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|340814|cell.setCellType(Cell.CELL_TYPE_STRING);|code-block|syntax|javascript|340815|但请使用：|340816|DataFormatter+df+=+new+DataFormatter();
String+value+=+df.formatCellValue(cell);|340817|有关http://massapi.com/class/da/DataFormatter.html的更多示例|340818|entityMap|0|LINK|mutability|MUTABLE|url|https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%2528int%2529|1|http://massapi.com/class/da/DataFormatter.html^0|H|2H|0|0|0|0|0|2|1A|1|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|14|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|15|8|@]|9|@$A|16|B|17|1|18]]|C|$]]|$1|O|3|-4|5|6|7|19|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]|W|$5|R|S|T|C|$U|X]]]]

As already mentioned in the Poi's JavaDocs (<a href="https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29" rel="noreferrer">https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29</a>) don't use: 

<pre><code>cell.setCellType(Cell.CELL_TYPE_STRING);
</code></pre>

but use:

<pre><code>DataFormatter df = new DataFormatter();
String value = df.formatCellValue(cell);
</code></pre>

More examples on <a href="http://massapi.com/class/da/DataFormatter.html" rel="noreferrer">http://massapi.com/class/da/DataFormatter.html</a>

blocks|key|3104559|text|其中许多答案都参考了旧的POI文档和类。在最新的POI3.16中，Cell+with+the+int+types已被弃用|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3104560|Cell.CELL_TYPE_STRING|code-block|syntax|javascript|3104561|​|3104562|📷|atomic|3104563|3104564|相反，可以使用CellType+enum。|3104565|CellType.STRING+|3104566|只需确保使用poi依赖项以及poi-ooxml依赖项更新您的pom到新的3.16版本，否则您将继续得到异常。此版本的一个优点是，您可以在创建单元格时指定单元格类型，从而消除了前面答案中描述的所有额外步骤：|3104567|titleRowCell+=+currentReportRow.createCell(currentReportColumnIndex,+CellType.STRING);|3104568|entityMap|0|LINK|mutability|MUTABLE|url|https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html|1|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/1ad6960b7caac566f26e0347db32cf35.png|imageAlt|2|https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/CellType.html^0|X|N|0|0|0|0|0|1|1|0|0|7|D|2|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@$A|1D|B|1E|1|1F]]|C|$]]|$1|D|3|E|5|F|7|1G|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|1H|8|@]|9|@]|C|$]]|$1|K|3|L|5|M|7|1I|8|@]|9|@$A|1J|B|1K|1|1L]]|C|$]]|$1|N|3|J|5|6|7|1M|8|@]|9|@]|C|$]]|$1|O|3|P|5|6|7|1N|8|@]|9|@$A|1O|B|1P|1|1Q]]|C|$]]|$1|Q|3|R|5|F|7|1R|8|@]|9|@]|C|$G|H]]|$1|S|3|T|5|6|7|1S|8|@]|9|@]|C|$]]|$1|U|3|V|5|F|7|1T|8|@]|9|@]|C|$G|H]]|$1|W|3|-4|5|6|7|1U|8|@]|9|@]|C|$]]]|X|$Y|$5|Z|10|11|C|$12|13]]|14|$5|15|10|16|C|$17|18|19|-4]]|1A|$5|Z|10|11|C|$12|1B]]]]

Many of these answers reference old POI documentation and classes. In the newest POI 3.16, <a href="https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html" rel="nofollow noreferrer">Cell with the int types</a> has been deprecated 

<pre><code>Cell.CELL_TYPE_STRING
</code></pre>

<a href="https://i.stack.imgur.com/PInzh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PInzh.png" alt="enter image description here"></a>

Instead the <a href="https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/CellType.html" rel="nofollow noreferrer">CellType enum</a> can be used.

<pre><code>CellType.STRING 
</code></pre>

Just be sure to update your pom with the poi dependency as well as the poi-ooxml dependency to the new 3.16 version otherwise you will continue to get exceptions. One advantage with this version is that you can specify the cell type at the time the cell is created eliminating all the extra steps described in previous answers:

<pre><code>titleRowCell = currentReportRow.createCell(currentReportColumnIndex, CellType.STRING);
</code></pre>

blocks|key|3317418|text|这对我来说是完美的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3317419|Double+legacyRow+=+row.getCell(col).getNumericCellValue();
String+legacyRowStr+=+legacyRow.toString();
if(legacyRowStr.contains(".0")){
++++legacyRowStr+=+legacyRowStr.substring(0,+legacyRowStr.length()-2);
}|code-block|syntax|javascript|3317420|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This worked perfect for me.

<pre><code>Double legacyRow = row.getCell(col).getNumericCellValue();
String legacyRowStr = legacyRow.toString();
if(legacyRowStr.contains(".0")){
 legacyRowStr = legacyRowStr.substring(0, legacyRowStr.length()-2);
}
</code></pre>

blocks|key|337331|text|我更愿意走wil的答案或Vinayak+Dornala的路线，不幸的是，他们对我的表现影响很大。我选择了隐式类型转换的HACKY解决方案：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|337332|for+(Row+row+:+sheet){
String+strValue+=+(row.getCell(numericColumn)%2B"");+//+hack
...|code-block|syntax|javascript|337333|我不建议你这样做，对于我的情况，它是有效的，因为系统如何工作的性质，并且我有一个可靠的文件源。|337334|脚注:+numericColumn是一个整数，它是通过读取处理的文件头而生成的。|337335|entityMap^0|1N|5|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|V|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

I would much rather go the route of the wil's answer or Vinayak Dornala, unfortunately they effected my performance far to much.
I went for a HACKY solution of implicit casting:

<pre><code>for (Row row : sheet){
String strValue = (row.getCell(numericColumn)+""); // hack
...
</code></pre>

I don't suggest you do this, for my situation it worked because of the nature of how the system worked and I had a reliable file source.

Footnote:
 numericColumn
Is an int which is generated from reading the header of the file processed.

blocks|key|3317476|text|public+class+Excellib+{
public+String+getExceldata(String+sheetname,int+rownum,int+cellnum,+boolean+isString)+{
++++String+retVal=null;
++++try+{
++++++++FileInputStream+fis=new+FileInputStream("E:\\Sample-Automation-Workspace\\SampleTestDataDriven\\Registration.xlsx");
++++++++Workbook+wb=WorkbookFactory.create(fis);
++++++++Sheet+s=wb.getSheet(sheetname);
++++++++Row+r=s.getRow(rownum);
++++++++Cell+c=r.getCell(cellnum);
++++++++if(c.getCellType()+==+Cell.CELL_TYPE_STRING)
++++++++retVal=c.getStringCellValue();
++++++++else+{
++++++++++++retVal+=+String.valueOf(c.getNumericCellValue());
++++++++}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3317477|我试过了，它对我很管用|unstyled|3317478|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>public class Excellib {
public String getExceldata(String sheetname,int rownum,int cellnum, boolean isString) {
 String retVal=null;
 try {
 FileInputStream fis=new FileInputStream("E:\\Sample-Automation-Workspace\\SampleTestDataDriven\\Registration.xlsx");
 Workbook wb=WorkbookFactory.create(fis);
 Sheet s=wb.getSheet(sheetname);
 Row r=s.getRow(rownum);
 Cell c=r.getCell(cellnum);
 if(c.getCellType() == Cell.CELL_TYPE_STRING)
 retVal=c.getStringCellValue();
 else {
 retVal = String.valueOf(c.getNumericCellValue());
 }
</code></pre>

I Tried This and It worked For me

blocks|key|3104663|text|有一个现成的包装器(可以应用一些额外的优化)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3104664|3104665|它支持数字和字符串单元格|unordered-list-item|3104666|3104667|自动识别和处理|3104668|公式|3104669|3104670|3104671|避免一些样板文件|3104672|3104673|公共最终类单元{私有最终静态DataFormatter格式器=+DataFormatter()；私有XSSFCell+mCell；公共单元(@NotNull+XSSFCell单元){+mCell+=单元；if+(isFormula())+{+XSSFWorkbook+book+=+mCell.getSheet().getWorkbook()；FormulaEvaluator+evaluator+=+new+mCell+=+(XSSFCell)+evaluator.evaluateInCell(mCell)；}}+/**+*+Get+content+*/+public+final+getInt()+{+return+(+int+)+getLong()；}+public+final+long+getLong()+{+return+Math.round(+getDouble()+)；}+public+final+double+getDouble(){+return+mCell.getNumericCellValue()；}+public+final+String+getString()+{+if+(!isString())+{+return+FORMATTER.formatCellValue(mCell)；}+return+mCell.getStringCellValue()；}+/**+*获取属性*/公共最终布尔isNumber()+{+if+(isFormula())+{+return+mCell.getCachedFormulaResultType().equals(CellType.NUMERIC)；}+return+mCell.getCellType().equals(CellType.NUMERIC)；}公共最终布尔isString()+{+if+(isFormula())+{+return+mCell.getCachedFormulaResultType().equals(CellType.STRING)；}+return+mCell.getCellType().equals(CellType.STRING)；}+public+final+boolean+isFormula()+{+return+mCell.getCellType().equals(CellType.FORMULA)；}+/**+*调试信息*/+@覆盖公共字符串toString()+{+return+getString()；}+}|3104674|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|V|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|X|8|@]|9|@]|A|$]]|$1|I|3|J|5|E|7|Y|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|E|7|11|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|13|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|S|$]]

There is a ready-to-use wrapper
(some additional optimizations can be applied)
<ul>
<li>it supports numeric and String cells
</li>
<li>formulas are recognized and handled automatically
</li>
<li>avoid some boilerplate
<pre><code> public final class Cell {

 private final static DataFormatter FORMATTER = new DataFormatter();

 private XSSFCell mCell;

 public Cell(@NotNull XSSFCell cell) {
 mCell = cell;

 if (isFormula()) {
 XSSFWorkbook book = mCell.getSheet().getWorkbook();
 FormulaEvaluator evaluator = book.getCreationHelper().createFormulaEvaluator();
 mCell = (XSSFCell) evaluator.evaluateInCell(mCell);
 }
 }

 /**
 * Get content
 */
 public final int getInt() {
 return (int) getLong();
 }

 public final long getLong() {
 return Math.round(getDouble());
 }

 public final double getDouble() {
 return mCell.getNumericCellValue();
 }

 public final String getString() {
 if (!isString()) {
 return FORMATTER.formatCellValue(mCell);
 }
 return mCell.getStringCellValue();
 }

 /**
 * Get properties
 */
 public final boolean isNumber() {
 if (isFormula()) {
 return mCell.getCachedFormulaResultType().equals(CellType.NUMERIC);
 }
 return mCell.getCellType().equals(CellType.NUMERIC);
 }

 public final boolean isString() {
 if (isFormula()) {
 return mCell.getCachedFormulaResultType().equals(CellType.STRING);
 }
 return mCell.getCellType().equals(CellType.STRING);
 }

 public final boolean isFormula() {
 return mCell.getCellType().equals(CellType.FORMULA);
 }

 /**
 * Debug info
 */
 @Override
 public String toString() {
 return getString();
 }
 }
</code></pre>
</li>
</ul>

<ol>
<li>I have excel file with such contents: 

<ul>
<li>A1: SomeString</li>
<li>A2: 2</li>
</ul>

All fields are set to String format.</li>
<li>When I read the file in java using POI, it tells that A2 is in numeric cell format.</li>
<li>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 <code>.toString()</code>.</li>
</ol>

What can I do to read the value as string?

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

Java

我有一个包含以下内容的excel文件：- A1: SomeString- A2: 2所有字段均设置为字符串格式。当我使用POI读取java中的文件时，它告诉我A2是数字单元格式。问题是A2中的值可以是2或2.0 (我希望能够区分它们)，所以我不能只使用.toString().我能做些什么来读取值为字符串？

问如何将Excel单元格中的数字字符串读取为字符串(而不是数字)？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将Excel单元格中的数字字符串读取为字符串(而不是数字)？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将Excel单元格中的数字字符串读取为字符串(而不是数字)？
EN