POI 4 中的 Excel 文件读取(一码支持Excel 2003/2007/2010)
此文件会随着时间不断更新
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
package com.bsoft.report.util;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
public class ReadExcel {
private static final Logger log = LoggerFactory.getLogger(ReadExcel.class);
public static Map<String, Map<String, String>> read(String filePath) throws IOException {
File excelFile = new File(filePath);
FileInputStream is = new FileInputStream(excelFile);
Workbook workBook = WorkbookFactory.create(is);
int numberOfSheets = workBook.getNumberOfSheets();
Map<String, Map<String, String>> map = new HashMap<>(numberOfSheets);
for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
Map<String, String> coordinate = new HashMap<>();
Sheet sheet = workBook.getSheetAt(sheetIndex);
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
Cell cell = row.getCell(cellIndex);
coordinate.put(coordinate(rowIndex, cellIndex), getCellValue(cell));
}
}
map.put(sheet.getSheetName(), coordinate);
log.info("{} 加载完成", sheet.getSheetName());
}
workBook.close();
is.close();
return map;
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
switch (cell.getCellType()) {
case NUMERIC:
case STRING:
case FORMULA:
case BOOLEAN:
DataFormatter dataFormatter = new DataFormatter();
cellValue = dataFormatter.formatCellValue(cell);
break;
case BLANK:
cellValue = "";
break;
case ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
public static String coordinate(Integer x, Integer y) {
Integer[] integers = {x, y};
return Arrays.toString(integers);
}
}