poi读取execl文件
1.读取 execl 文件转为对象
通常读取execl 文件 转为对象 中,列 和 对象的属性对应通常为注解或者写死,此方法能把 对应属性的操作 由方法参数传入
当然还有很多可以细致修改,优化的地方
嘎
/**
*
* @param filePath
* @param function 自定义 function方法 ,对应excel列 与 对象属性
* @return
* @param <T>
*/
public static <T>List<T> readExcelByRow(String filePath, Function<List<String>, T> function) {
Assert.hasText(filePath);
List<T> resultList = new ArrayList<>();
try {
FileInputStream excelFile = new FileInputStream(filePath);
Workbook workbook = new XSSFWorkbook(excelFile);
Sheet sheet = workbook.getSheetAt(0);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<String> columnNames = new ArrayList<>();
Iterator<Cell> cellIterator = sheet.getRow(0).cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
columnNames.add(cell.getStringCellValue());
}
// 跳过首行表头
for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
//获取Excel当前行的第一位,若未null则不处理 ---> 防止poi读取Excel多读取没有数据的一行,导致非空字段为空。
Cell cellNow = row.getCell(0);
if (cellNow == null) {
break;
}
List<String> columnResult = new ArrayList<>();
for (int columnIndex = 0; columnIndex < columnNames.size(); columnIndex++) {
Cell currentCell = row.getCell(columnIndex);
String columnName = columnNames.get(columnIndex);
String value = "";
if (currentCell == null){
value = "";
}else if (currentCell.getCellType() == CellType.STRING) {
value = currentCell.getStringCellValue();
}else if (currentCell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(currentCell) ) {
Date dateCellValue = currentCell.getDateCellValue();
String format = simpleDateFormat.format(dateCellValue);
value = format;
}else if (currentCell.getCellType() == CellType.NUMERIC){
String rawValue = ((XSSFCell) currentCell).getRawValue();
value = rawValue;
}else if (currentCell.getCellType() == CellType.BLANK){
value = "";
}
columnResult.add(value);
}
resultList.add(function.apply(columnResult));
}
} catch (IOException e) {
e.printStackTrace();
}
return resultList;
}
- 测试方法
public static void main(String[] args) {
List<ExcelEntity> list = ExcelUtils.readExcelByRow("C:\\Users\\74074\\Desktop\\demo.xlsx",
item ->{
ExcelEntity excelEntity = new ExcelEntity();
excelEntity.setId(Long.valueOf(item.get(0)));
excelEntity.setName(item.get(1));
excelEntity.setSex(item.get(2));
return excelEntity;
}
);
list.forEach(System.out::println);
}
- 运行截图
- execl 文件