当获得使用Apache-POI库单元的内容,我同时获得"不能从一个文本单元和QUOT数值;和的,反向。如何解决呢? [英] 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?
问题描述
我意识到这个问题是有点混乱,但我不知道怎么回事,以字呢。总之,这里是原来的code:
私人无效READFILE(字符串excelFileName)抛出FileNotFoundException异常,IOException异常{
XSSFWorkbook工作簿=新XSSFWorkbook(新的FileInputStream(excelFileName));
如果(workbook.getNumberOfSheets()→1){
的System.out.println(请确保只有一个表在Excel工作簿);
}
XSSFSheet片= workbook.getSheetAt(0);
INT numOfPhysRows = sheet.getPhysicalNumberOfRows();
XSSFRow排;
XSSFCell NUM;
为(中间体Y = 1; Y&下; numOfPhysRows; Y ++){//于第2行开始自第1应类别名称
行= sheet.getRow(Y);
poNum = row.getCell(1);
项目=新的项目(的Integer.parseInt(poNum.getStringCellValue());
itemList.add(项目);
ÿ++;
}
}私人诠释poiConvertFromStringtoInt(XSSFCell细胞){
INT X =的Integer.parseInt(Double.toString(cell.getNumericCellValue()));
返回X;
}
我收到以下错误:
异常线程mainjava.lang.IllegalStateException:无法从文本单元得到一个数值
在org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:781)
在org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:199)
即使我改变它获得或者使用字符串 XSSFCell.getStringCellValue()
甚至 XFFSCell.getRichTextValue
,我得到上述错误消息的反向(和我并确保使用最终使一个int 的Integer.parseInt(XSSFCell.getStringCellValue()
)。
错误然后读取:
异常线程mainjava.lang.IllegalStateException:不能从数字单元获得的文本值
在org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:781)
在org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:199)
我知道一个事实,即Excel的小号preadsheet列实际上是一个串。因为它在那里始终使用相同的格式和格式化每列第一最多需要大量的处理时间上传否则我不能改变Excel工作表。
有什么建议?
[解决]这是我想出了解决方案code来自@ Wivani的帮助:
私人长期poiGetCellValue(XSSFCell细胞){
长×;
如果(cell.getCellType()== 0)
X =(长)cell.getNumericCellValue();
否则如果(cell.getCellType()== 1)
X =的Long.parseLong(cell.getStringCellValue());
其他
X = -1;
返回X;
}
以此为参考开关(cell.getCellType()){
案例Cell.CELL_TYPE_STRING:
的System.out.println(cell.getRichStringCellValue()的getString());
打破;
案例Cell.CELL_TYPE_NUMERIC:
如果(DateUtil.isCellDateFormatted(单元)){
的System.out.println(cell.getDateCellValue());
}其他{
的System.out.println(cell.getNumericCellValue());
}
打破;
案例Cell.CELL_TYPE_BOOLEAN:
的System.out.println(cell.getBooleanCellValue());
打破;
案例Cell.CELL_TYPE_FORMULA:
的System.out.println(cell.getCellFormula());
打破;
默认:
的System.out.println();
}
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()
).
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.
Any suggestions?
[Solution] Here is the solution code I came up with from @Wivani's help:
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;
}
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();
}
这篇关于当获得使用Apache-POI库单元的内容,我同时获得&QUOT;不能从一个文本单元和QUOT数值;和的,反向。如何解决呢?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!