java 使用Apache POI读取excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15760724/
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 excel file using Apache POI
提问by user2235454
I have created this code to read the contents of excel files using Apache POI. I am using eclipse as editor but when i ran the code i have problem in the line that I have in bold. What's the problem? The content of excel is the following:
我创建了这段代码来使用 Apache POI 读取 excel 文件的内容。我使用 eclipse 作为编辑器，但是当我运行代码时，我在粗体行中遇到了问题。有什么问题？excel的内容如下：
Emp ID Name Salary
1.0 john 2000000.0
2.0 dean 4200000.0
3.0 sam 2800000.0
4.0 cass 600000.0
import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ExcelRead {
public static void main(String[] args) throws Exception {
File excel = new File ("C:\Users\Efi\Documents\test.xls");
FileInputStream fis = new FileInputStream(excel);
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet ws = wb.getSheet("Input");
int rowNum = ws.getLastRowNum()+1;
int colNum = ws.getRow(0).getLastCellNum();
String[][] data = new String[rowNum][colNum];
for (int i=0; i<rowNum; i++){
HSSFRow row = ws.getRow(i);
for (int j=0; j<colNum; j++){
HSSFCell cell = row.getCell(j);
String value = cellToString(cell);
data[i][j] = value;
System.out.println("The value is" + value);
}
}
}
public static String cellToString (HSSFCell cell){
int type;
Object result;
type = cell.getCellType();
switch(type) {
case 0://numeric value in excel
result = cell.getNumericCellValue();
break;
case 1: //string value in excel
result = cell.getStringCellValue();
break;
case 2: //boolean value in excel
result = cell.getBooleanCellValue ();
break;
default:
***throw new RunTimeException("There are not support for this type of
cell");***
}
return result.toString();
}
}
回答by rgettman
There are additional cell typesbesides the ones you are capturing in your switchstatement. You have cases for 0(CELL_TYPE_NUMERIC), 1(CELL_TYPE_STRING), and 2, but 2is CELL_TYPE_FORMULA. Here are the additional possible values:
除了您在语句中捕获的单元格类型之外，还有其他单元格类型switch。您有0( CELL_TYPE_NUMERIC)、1( CELL_TYPE_STRING) 和 的情况2，但是2是CELL_TYPE_FORMULA。以下是其他可能的值：
- 3:
CELL_TYPE_BLANK - 4:
CELL_TYPE_BOOLEAN - 5:
CELL_TYPE_ERROR
- 3：
CELL_TYPE_BLANK - 4：
CELL_TYPE_BOOLEAN - 5：
CELL_TYPE_ERROR
Use the Cellconstants for the cell type in your switch statement instead of integer literals, and use all 6 of them to capture all possible cases.
Cell在 switch 语句中使用单元格类型的常量而不是整数文字，并使用所有 6 个常量来捕获所有可能的情况。
And as @Vash has already suggested, include the actual cell typein your RuntimeExceptionmessage.
正如@Vash 已经建议的那样，type在您的RuntimeException消息中包含实际的单元格。
回答by noego
Check this librarythat I've created for reading both XLSX, XLS and CSV files pretty easily. It uses Apache POI for processing excel files and converts excel rows into a list of Java beans based on your configuration.
检查我为读取 XLSX、XLS 和 CSV 文件而创建的这个库。它使用 Apache POI 处理 excel 文件，并根据您的配置将 excel 行转换为 Java bean 列表。
Here is an example:
下面是一个例子：
RowConverter<Country> converter = (row) -> new Country(row[0], row[1]);
ExcelReader<Country> reader = ExcelReader.builder(Country.class)
.converter(converter)
.withHeader()
.csvDelimiter(';')
.sheets(1)
.build();
List<Country> list;
list = reader.read("src/test/resources/CountryCodes.xlsx");
list = reader.read("src/test/resources/CountryCodes.xls");
list = reader.read("src/test/resources/CountryCodes.csv");
With following excel and bean files:
使用以下 excel 和 bean 文件：
public static class Country {
public String shortCode;
public String name;
public Country(String shortCode, String name) {
this.shortCode = shortCode;
this.name = name;
}
}
Excel:
电子表格：
Code Country
ad Andorra
ae United Arab Emirates
af Afghanistan
ag Antigua and Barbuda
...
回答by Betsy
Using XSSFWorkbook and XSSFSheet did not help me read .xls, but I used this code and it helps me read the .xls and xlsx files:
使用 XSSFWorkbook 和 XSSFSheet 并不能帮助我阅读 .xls，但我使用了这段代码，它可以帮助我阅读 .xls 和 xlsx 文件：
public static void readExcelFile(File file) throws IOException, InvalidFormatException {
Workbook workbook = WorkbookFactory.create(new File(file.toString()));
Integer sheet = workbook.getNumberOfSheets();
DataFormatter dataFormatter = new DataFormatter();
for (int i = 0; i < sheet; i++) {
Sheet s = workbook.getSheetAt(i);
Iterator<Row> rowIterator = s.rowIterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
printCellValue(cell);
// both work perfect
// printCellValue(cell);
/*String cellValue = dataFormatter.formatCellValue(cell);
System.out.print(cellValue + "\t");*/
}
System.out.println();
}
}
}
public static void printCellValue(Cell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.print(cell.getDateCellValue());
} else {
System.out.print(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_FORMULA:
System.out.print(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
System.out.print(" ");
break;
default:
System.out.print("");
}
System.out.print("\t");
}
回答by Damian Leszczyński - Vash
You should amend that RuntimeException with information about what type is not supported with your switch statement. Then you will be able to add support for it, so no exception will be thrown.
您应该使用有关 switch 语句不支持什么类型的信息来修改 RuntimeException。然后你就可以添加对它的支持，所以不会抛出异常。
So to see the picture of what your program is doing instead of
因此，要查看您的程序正在执行的操作的图片，而不是
throw new RunTimeException("There are not support for this type of cell");
throw new RunTimeException("There are not support for this type of cell");
you should add
你应该添加
throw new RunTimeException("There are not support for type with id ["+type+"] of cell");
throw new RunTimeException("There are not support for type with id ["+type+"] of cell");
This will only, inform you what do you miss. How to handle this situation is up to you.
这只会告诉你你错过了什么。如何处理这种情况取决于您。
