POI解析Excel文件工具类
/**
* 读取excel数据
*/
public static List<Map<String, Object>> exportListFromExcel(File file, int sheetNum) throws IOException {
return exportListFromExcel(new FileInputStream(file), FilenameUtils.getExtension(file.getName()), sheetNum);
}
public static List<Map<String, Object>> exportListFromExcel(InputStream is, String extensionName, int sheetNum) throws IOException {
Workbook workbook = null;
if (extensionName.toLowerCase().equals(XLS)) {
workbook = new HSSFWorkbook(is);
} else if (extensionName.toLowerCase().equals(XLSX)) {
workbook = new XSSFWorkbook(is);
}
return readCell(workbook, sheetNum);
}
public static List<Map<String, Object>> readCell(Workbook workbook, int sheetNum) {
Sheet sheet = workbook.getSheetAt(sheetNum);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int sNum=1;
if(sheetNum==0) { //如果是片1
sNum=0;
}else if(sheetNum==1){//如果是片2
sNum=2;
}
for (int i=sNum; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
Map<String, Object> map = new HashMap<String, Object>();
for (Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
String key = cellRef.formatAsString();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
map.put(key, cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
map.put(key, cell.getDateCellValue());
} else {
DecimalFormat dfs = new DecimalFormat("0");
map.put(key, dfs.format(cell.getNumericCellValue()));
}
break;
case Cell.CELL_TYPE_BOOLEAN:
map.put(key, cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
map.put(key, cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
map.put(key, "");
}
}
list.add(map);
}
return list;
}
POM文件
<!-- poi -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- commons-io -->
<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>