当使用Apache-POI库获取单元格内容时,我得到“无法从文本单元格中获取数值”和相反的情况。我该如何修复它?
2022-09-01 21:29:28
我意识到这个问题有点令人困惑,但我不知道如何措辞。无论如何,这是原始代码:
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;
}
我收到以下错误:
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)
即使我改变它以获得一个字符串使用甚至,我得到上述错误消息的相反(并且我确保最终使它成为一个int using)。XSSFCell.getStringCellValue()
XFFSCell.getRichTextValue
Integer.parseInt(XSSFCell.getStringCellValue()
然后,错误显示为:
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)
我知道一个事实,Excel电子表格列实际上是一个字符串。我无法更改Excel工作表,因为它是上传的,否则总是使用相同的格式,并且格式化每列首先需要花费很多处理时间。
有什么建议吗?
[解决方案]以下是我从@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;
}