Java 使用 Apache-POI 库获取单元格内容时，我得到“无法从文本单元格获取数值”和相反的结果。我如何解决它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6508203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When getting cell content using Apache-POI Library, I get both "Cannot get a numeric value from a text cell" and the reverse of that. How do I fix it?
提问by crstamps2
I realize the question is a little confusing, but I didn't know how else to word it. Anyway, here is the original code:
我意识到这个问题有点令人困惑，但我不知道如何表达。无论如何，这是原始代码：
private void readFile(String excelFileName) throws FileNotFoundException, IOException {
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelFileName));
if (workbook.getNumberOfSheets() > 1){
System.out.println("Please make sure there is only one sheet in the excel workbook.");
}
XSSFSheet sheet = workbook.getSheetAt(0);
int numOfPhysRows = sheet.getPhysicalNumberOfRows();
XSSFRow row;
XSSFCell num;
for(int y = 1;y < numOfPhysRows;y++){ //start at the 2nd row since 1st should be category names
row = sheet.getRow(y);
poNum = row.getCell(1);
item = new Item(Integer.parseInt(poNum.getStringCellValue());
itemList.add(item);
y++;
}
}
private int poiConvertFromStringtoInt(XSSFCell cell){
int x = Integer.parseInt(Double.toString(cell.getNumericCellValue()));
return x;
}
I am getting the following error:
我收到以下错误：
Exception in thread "main" java.lang.IllegalStateException: Cannot get a numeric value from a text cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:781)
at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:199)
Even if I change it to get either a string using XSSFCell.getStringCellValue()or even XFFSCell.getRichTextValue, I get the reverse of the above error message (and I am making sure to ultimately make it an int using Integer.parseInt(XSSFCell.getStringCellValue()).
即使我将其更改为使用XSSFCell.getStringCellValue()或 even获取字符串XFFSCell.getRichTextValue，我也会得到上述错误消息的反向信息（并且我确保最终使用 将其设为 int Integer.parseInt(XSSFCell.getStringCellValue()）。
The error then reads:
然后错误如下：
Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:781)
at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:199)
I know for a fact that the excel spreadsheet column is in fact a string. I can't change the excel sheet as it is uploaded else where always using the same format and formatting each column first takes up to much processing time.
我知道excel电子表格列实际上是一个字符串。我无法更改 Excel 工作表，因为它是在其他地方上传的，其中始终使用相同的格式，并且首先格式化每列会占用大量处理时间。
Any suggestions?
有什么建议？
[Solution] Here is the solution code I came up with from @Wivani's help:
[解决方案] 这是我从@Wivani 的帮助中得出的解决方案代码：
private long poiGetCellValue(XSSFCell cell){
long x;
if(cell.getCellType() == 0)
x = (long)cell.getNumericCellValue();
else if(cell.getCellType() == 1)
x = Long.parseLong(cell.getStringCellValue());
else
x = -1;
return x;
}
回答by Mayank
Use This as reference
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
}
回答by Mayank
Just use cell.setCellType(1); before reading cell value and get it as String always, after that you can use it in your own format(type).
只需使用 cell.setCellType(1); 在读取单元格值并始终将其作为字符串获取之前，之后您可以以自己的格式（类型）使用它。
Ravi
拉维
回答by JavaLover
Ravi's solution works : Just use cell.setCellType(1); before reading cell value and get it as String always, after that you can use it in your own format(type).
Ravi 的解决方案有效：只需使用 cell.setCellType(1); 在读取单元格值并始终将其作为字符串获取之前，之后您可以以自己的格式（类型）使用它。
回答by Thierry
You can get value as String using the format defined for this cell :
您可以使用为此单元格定义的格式以字符串形式获取值：
final DataFormatter df = new DataFormatter();
final XSSFCell cell = row.getCell(cellIndex);
String valueAsString = df.formatCellValue(cell);
Thanks to this answer.
感谢这个答案。
回答by Jobin Thomas
Documentation clearly says not to setCellType to 1 instead use the DataFormatter like how Thierry has explained:
文档清楚地说明不要将CellType设置为1，而是像Thierry解释的那样使用DataFormatter：
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType(int)
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType(int)
回答by Nirmal Dhara
Use the below code to read any data type from xcels using poi.
使用以下代码使用 poi 从 xcels 读取任何数据类型。
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author nirmal
*/
public class ReadWriteExcel {
public static void main(String ar[]) {
ReadWriteExcel rw = new ReadWriteExcel();
rw.readDataFromExcel();
}
Object[][] data = null;
public File getFile() throws FileNotFoundException {
File here = new File("test/com/javaant/ssg/tests/test/data.xlsx");
return new File(here.getAbsolutePath());
}
public Object[][] readDataFromExcel() {
final DataFormatter df = new DataFormatter();
try {
FileInputStream file = new FileInputStream(getFile());
//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
int rownum = 0;
int colnum = 0;
Row r=rowIterator.next();
int rowcount=sheet.getLastRowNum();
int colcount=r.getPhysicalNumberOfCells();
data = new Object[rowcount][colcount];
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
colnum = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
data[rownum][colnum] = df.formatCellValue(cell);
System.out.print(df.formatCellValue(cell));
colnum++;
System.out.println("-");
}
rownum++;
System.out.println("");
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
return data;
}
}
回答by Herve
I got also this bug with POI version 3.12final.
I think that the bug is registered there : https://bz.apache.org/bugzilla/show_bug.cgi?id=56702and I put a comment there with my analysis.
我在 POI 版本 3.12final 中也遇到了这个错误。
我认为这个错误是在那里注册的：https: //bz.apache.org/bugzilla/show_bug.cgi?id=56702，我在那里发表了我的分析评论。
Here is the workaround I used : The exception was risen by HSSFCell.getNumericCellValue which was called by DateUtil.isCellDateFormatted. DateUtil.isCellDateFormatted does 2 things :
1) check the value type of the cell by calling HSSFCell.getNumericCellValue and then DateUtil.isValidExcelDate(), which is almost pointless here I think.
2) check if the format of the cell is a date format
这是我使用的解决方法：异常是由 DateUtil.isCellDateFormatted 调用的 HSSFCell.getNumericCellValue 引发的。DateUtil.isCellDateFormatted 做两件事：
1) 通过调用 HSSFCell.getNumericCellValue 然后 DateUtil.isValidExcelDate() 检查单元格的值类型，我认为这在这里几乎毫无意义。
2）检查单元格的格式是否为日期格式
I copied the code of topic 2) above in a new function 'myIsADateFormat' and used it instead of DateUtil.isCellDateFormatted (that is quite dirty to copy library code, but it works...) :
我将上面主题 2) 的代码复制到一个新函数“myIsADateFormat”中，并使用它代替 DateUtil.isCellDateFormatted（复制库代码很脏，但它有效......）：
private boolean myIsADateFormat(Cell cell){
CellStyle style = cell.getCellStyle();
if(style == null) return false;
int formatNo = style.getDataFormat();
String formatString = style.getDataFormatString();
boolean result = DateUtil.isADateFormat(formatNo, formatString);
return result;
}
If you need to check the value type first, you can use this too :
如果您需要先检查值类型，您也可以使用它：
CellValue cellValue = evaluator.evaluate(cell);
int cellValueType = cellValue.getCellType();
if(cellValueType == Cell.CELL_TYPE_NUMERIC){
if(myIsADateFormat(cell){
....
}
}
