java poi 解析excel_Java用POI解析excel并获取所有单元格数据

1.导入POI相关jar包

org.apache.poi jar

2.代码示例

public List getAllExcel(File file, String tableName, String fname,

String enterpriseId, String reportId, String projectId)

throws FileNotFoundException, IOException, ClassNotFoundException,

InstantiationException, IllegalAccessException,

NoSuchMethodException, SecurityException, IllegalArgumentException,

InvocationTargetException, ParseException {

List listt = new ArrayList();

try {

FileInputStream fis = new FileInputStream(file);

Workbook workbook = null;

if (fname.toLowerCase().endsWith("xlsx")) {

workbook = new XSSFWorkbook(fis);

} else if (fname.toLowerCase().endsWith("xls")) {

workbook = new HSSFWorkbook(new POIFSFileSystem(fis));

}

int numberOfSheets = workbook.getNumberOfSheets();

for (int i = 0; i < numberOfSheets; i++) {

Sheet sheet = workbook.getSheetAt(i);

for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) { // 获取每行

XSSFRow row = (XSSFRow) sheet.getRow(j);

if(row!=null){

List list = new ArrayList();

for (int k = 0; k < sheet.getRow(0).getPhysicalNumberOfCells(); k++) { // 获取每个单元格

Cell cell = row.getCell(k);

if (cell == null) {

list.add("");

continue;

}

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

list.add(cell.getRichStringCellValue().getString());

break;

case Cell.CELL_TYPE_NUMERIC:

if (DateUtil.isCellDateFormatted(cell)) {

list.add(cell.getDateCellValue());

} else {

list.add(cell.getNumericCellValue());

}

break;

case Cell.CELL_TYPE_BOOLEAN:

list.add(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

list.add(cell.getCellFormula());

break;

default:

list.add("");

break;

}

}

listt.add(getByReflect(tableName, list, enterpriseId,reportId, projectId));

}

}

}

fis.close();

} catch (IOException e) {

e.printStackTrace();

}

return listt;

}