在java中使用Apache POI从数字单元格中读取整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25771412/
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
Read integer from numeric cell using Apache POI in java
提问by Parameswar
I have an application which reads xls sheet using apache poi. When the cell has numeric value, i read it by row.getCell(i).getNumericValue(). But it returns floating point digit. like if the cell value is 1, it returns 1.0. Can i convert it to int ? Any helpwould be appreciated. I tried Integer.parseInt(value)- but it throws NumberFormat exception.Any help is appreciated.Here is the pseudo code:
我有一个使用 apache poi 读取 xls 表的应用程序。当单元格具有数值时，我通过 row.getCell(i).getNumericValue() 读取它。但它返回浮点数。就像如果单元格值为 1，它返回 1.0。我可以将其转换为 int 吗？任何帮助，将不胜感激。我试过 Integer.parseInt(value)- 但它抛出 NumberFormat 异常。感谢任何帮助。这是伪代码：
FileInputStream file = new FileInputStream(new File("C:\test.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while(cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch(cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
String value= String.valueOf(cell.getNumericCellValue());
int intVal = Integer.parseInt(value)-->>throws Exception
采纳答案by VISHWANATH N P
You can read int value as string apache poi using simple steps
您可以使用简单的步骤将 int 值读取为字符串 apache poi
First count rows in sheets using below method
首先使用以下方法计算工作表中的行数
private int getRowCount(Sheet currentSheet) {
int rowCount = 0;
Iterator<Row> rowIterator = currentSheet.iterator();
while(rowIterator.hasNext()) {
Row row = rowIterator.next();
if(row == null || row.getCell(0) == null || row.getCell(0).getStringCellValue().trim().equals("") || row.getCell(0).toString().trim().equals("")
|| row.getCell(0).getCellType()==Cell.CELL_TYPE_BLANK){
break;
}
else
rowCount=rowCount + 1;
}
return rowCount;
}
Then use below code in your method
然后在您的方法中使用以下代码
Workbook workbook = WorkbookFactory.create(new File("c:/test.xls");
Sheet marksSheet = (Sheet) workbook.getSheet("Sheet1");
int zoneLastCount = 0;
if(marksSheet !=null ) {
zoneLastCount = getRowCount(marksSheet);
}
int zone = zoneLastCount-1;
int column=1
for(int i = 0; i < zone; i++) {
Row currentrow = marksSheet.getRow(i);
double year = Double.parseDouble(currentrow.getCell(columnno).toString());
int year1 = (int)year;
String str = String.valueOf(year1);
}
回答by Gagravarr
Numbers in Excel are (except for a few edge cases) stored as floating point numbers. Floating point numbers in Java, when formatted as a string, are printed with a trailing decimal point, as you're seeing
Excel 中的数字（除了少数边缘情况）存储为浮点数。如您所见，Java 中的浮点数在格式化为字符串时会打印一个尾随小数点
Assuming what you really wanted was "give me a string that looks like what Excel shows for this cell", then do notcall cell.toString(). This will not give you what you want in most cases
假设您真正想要的是“给我一个类似于 Excel 为该单元格显示的字符串”，然后不要调用 cell.toString()。在大多数情况下，这不会给你你想要的
Instead, you need to use the DataFormatter class, which provides methods which read the Excel format rules applied to a cell, then re-creates (as best it can) those in Java
相反，您需要使用DataFormatter 类，该类提供读取应用于单元格的 Excel 格式规则的方法，然后在 Java 中重新创建（尽其所能）这些规则
Your code should be:
你的代码应该是：
Workbook wb = WorkbookFactory.create(new File("c:/test.xls");
DataFormatter fmt = new DataFormatter();
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
Cell cell = row.getcell(0, Row.RETURN_BLANK_AS_NULL);
if(cell!=null) {
String value = fmt.formatCellValue(cell);
if (! value.trim().isEmpty()) {
System.out.println("Cell as string is " + value);
}
}
}
You may notice I've also fixed a bunch of other stuff as well....!
你可能会注意到我还修复了一些其他的东西......！
回答by Ilia Nedoluzhko
Someone might find this hack useful:
有人可能会发现这个 hack 很有用：
cell.setCellType(CellType.STRING);
int value = Integer.parseInt(cell.getStringCellValue());
Remember you alter the cell type here, so be sure this does not cause any side effect. In a single-threaded app you can just read the type before and restore it after.
请记住，您在此处更改了单元格类型，因此请确保这不会引起任何副作用。在单线程应用程序中，您可以只读取之前的类型并在之后恢复它。
回答by Sani Shrestha
You can just type cast floatto intlike :
您只需键入 castfloat即可int：
String value = String.valueOf((int)cell.getNumericCellValue());
回答by Mehdiway
Cell cell = row.getCell(cellCpt);
String cellContent = "";
if (cell != null) {
cell.setCellType(CellType.STRING);
cellContent = fmt.formatCellValue(cell);
}
回答by Mohamed Maalej
Try doing the following (to obtain a long):
尝试执行以下操作（以获得长）：
long value = (long) currentCell.getNumericValue();
回答by Mallikharjuna
// to extract the exact numerical value either integer/double
DataFormatter fmt = new DataFormatter();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
//DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
fetchedRow.add(dateFormat.format(date));
} else {
fetchedRow.add(fmt.formatCellValue(cell));
}
rowEmpty = false;
break;
case STRING:
fetchedRow.add(cell.toString());
rowEmpty = false;
break;
case BOOLEAN:
fetchedRow.add(cell.toString());
rowEmpty = false;
break;
case FORMULA:
fetchedRow.add(Double.toString(cell.getNumericCellValue()));
rowEmpty = false;
break;
case BLANK:
fetchedRow.add("");
break;
}
}
if (!rowEmpty) {
allRows.add(fetchedRow.toArray(new String[0]));
count++;
}
if (count >= limit) {
break;
}
}
return allRows;
}
}
eaxmple to read limitted no.of rows on and use DataFormatter to give exact numeric value either integer/ double value. This will work.
eaxmple 读取有限的行数并使用 DataFormatter 给出精确的数值，无论是整数/双精度值。这将起作用。
回答by M T
Below implementation worked for me:
下面的实现对我有用：
private Object getValue(Cell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case Cell.CELL_TYPE_NUMERIC:
return String.valueOf((int) cell.getNumericCellValue());
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_ERROR:
return cell.getErrorCellValue();
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case Cell.CELL_TYPE_BLANK:
return null;
}
return null;
}
