场景说明
在实际开发中，经常需要解析Excel数据来插入数据库，而且通常会有一些要求，比如：全部校验成功才入库、校验成功入库，校验失败返回提示（总数、成功数、失败数、失败每行明细、导出失败文件明细…）
数据库表
CREATE TABLE `forlan_student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
<dependency>
<groupId>com.alibabagroupId>
<artifactId>easyexcelartifactId>
<version>2.2.3version>
dependency>
导入模板
public class ForlanStudentExcelModule {
@ExcelProperty(value = "姓名", index = 0)
private String name;
@ExcelProperty(value = "年龄", index = 1)
private Integer age;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
错误失败模板
@HeadStyle(horizontalAlignment = HorizontalAlignment.LEFT)
@ContentStyle(horizontalAlignment = HorizontalAlignment.LEFT)
public class ForlanStudentErrorExcelModule {
@ColumnWidth(20)
@ExcelProperty(value = "失败原因", index = 0)
private String excelOneLineErrorMsg;
@ColumnWidth(10)
@ExcelProperty(value = "姓名", index = 1)
private String name;
@ColumnWidth(10)
@ExcelProperty(value = "年龄", index = 2)
private Integer age;
public String getExcelOneLineErrorMsg() {
return excelOneLineErrorMsg;
}
public void setExcelOneLineErrorMsg(String excelOneLineErrorMsg) {
this.excelOneLineErrorMsg = excelOneLineErrorMsg;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "ForlanStudentErrorExcelModule{" +
"excelOneLineErrorMsg='" + excelOneLineErrorMsg + '\'' +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
@RestController
public class ExcelController {
@Autowired
private ForlanStudentService forlanStudentService;
@RequestMapping("/excel/import")
public String importFromExcel(@RequestParam(value = "file") MultipartFile param) {
// 校验文件类型
String fileName = param.getOriginalFilename();
if (StringUtils.isEmpty(fileName) || !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {
return "文件后缀需为.xlsx或.xls";
}
return forlanStudentService.doImport(param);
}
}
public interface ForlanStudentService {
String doImport(MultipartFile param);
}
a、主方法
@Override
public String doImport(MultipartFile param) {
String result = "导入成功";
try (InputStream inputStream = param.getInputStream()) {
// 解析Excel对象流转成需要的对象
List forlanStudentList = processExcel(inputStream);
// 最终入库数据
List insertData = new ArrayList<>();
// 校验数据，并填充符合的数据
List forlanStudentErrorExcelModule = checkDataAndFill(forlanStudentList, insertData);
if (!CollectionUtils.isEmpty(forlanStudentErrorExcelModule)) {
// 要求全部校验通过的话，这里可以直接return
// 需要的话，转成JSON返回，好看些
result = forlanStudentErrorExcelModule.toString();
// 可以生成错误文件，返回错误文件路径
// result = generateExceptionFile(forlanStudentErrorExcelModule);
}
if(!CollectionUtils.isEmpty(insertData)){
// 数据入库，根据自己需要写
forlanStudentDao.insertBatch(insertData);
}
} catch (Exception e) {
e.printStackTrace();
return e.getMessage();
}
return result;
}
b、解析Excel数据转为List对象
private List processExcel(InputStream inputStream) throws Exception {
List forlanStudentList = new ArrayList<>();
Integer maxRows = 100;
// 导入模板表头
List chineseHeader = Arrays.asList("姓名", "年龄");
// 0是表头
final int headerRows = 0;
try (Workbook workbook = WorkbookFactory.create(inputStream)) {
Sheet sheet = workbook.getSheetAt(0);
int totalRow = sheet.getLastRowNum();
if (totalRow == 0) {
throw new Exception("文件内容为空");
} else if (totalRow - headerRows > maxRows) {
throw new Exception(String.format("单次导入数据不能超过%s条", maxRows));
}
// 遍历每行
for (int rowIndex = 0; rowIndex <= totalRow; rowIndex++) {
Row currentRow = sheet.getRow(rowIndex);
if (currentRow == null) {
continue;
}
// 读取数据行
List cellList = new ArrayList<>();
for (int columnIndex = 0; columnIndex <= 1; columnIndex++) {
Cell currentCell = currentRow.getCell(columnIndex);
cellList.add(formatCellValue(currentCell));
}
// 校验模板是否正确
if (rowIndex <= headerRows) {
if (rowIndex == 0 && !cellList.equals(chineseHeader)) {
throw new Exception("文件模板错误");
}
continue;
}
if (null != cellList && !cellList.isEmpty()) {
ForlanStudent forlanStudent = new ForlanStudent();
forlanStudent.setName(cellList.get(0));
forlanStudent.setAge(Integer.valueOf(cellList.get(1)));
forlanStudentList.add(forlanStudent);
}
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception(e.getMessage());
}
return forlanStudentList;
}
public static String formatCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
double d = cell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
return new SimpleDateFormat("yyyy/MM/dd HH:mm:ss\"").format(date);
} else {
// 强制将数字转字符串
DecimalFormat format = new DecimalFormat("0.00");
Number value = cell.getNumericCellValue();
String phone = format.format(value).replace(".00", "");
return String.valueOf(phone);
}
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
try {
return cell.getStringCellValue() == null ? "" : cell.getStringCellValue().trim();
} catch (Exception e) {
return cell.toString() == null ? "" : cell.toString().trim();
}
}
}
c、校验数据，并填充入库数据、错误数据行
private List checkDataAndFill(List forlanStudentList, List insertData) {
List errorExcelModules = new ArrayList<>();
// 校验数据，支持拓展功能，比如，统计总量、成功数、失败数...
forlanStudentList.forEach(p -> {
if (StringUtils.isBlank(p.getName()) || null == p.getAge()) {
ForlanStudentErrorExcelModule forlanStudentErrorExcelModule = new ForlanStudentErrorExcelModule();
BeanUtils.copyProperties(p, forlanStudentErrorExcelModule);
forlanStudentErrorExcelModule.setExcelOneLineErrorMsg("请填写必填项");
errorExcelModules.add(forlanStudentErrorExcelModule);
return;
}
if (p.getAge() < 0) {
ForlanStudentErrorExcelModule forlanStudentErrorExcelModule = new ForlanStudentErrorExcelModule();
BeanUtils.copyProperties(p, forlanStudentErrorExcelModule);
forlanStudentErrorExcelModule.setExcelOneLineErrorMsg("年龄不能小于0");
errorExcelModules.add(forlanStudentErrorExcelModule);
return;
}
// 如果没有跳过，说明符合入库
ForlanStudent forlanStudent = new ForlanStudent();
BeanUtils.copyProperties(p, forlanStudent);
insertData.add(forlanStudent);
});
return errorExcelModules;
}
d、错误数据行原因生成文件
private String generateExceptionFile(List forlanStudentErrorExcelModuleList) {
File file = new File("导入文件校验失败原因.xlsx");
ExcelWriter excelWriter = EasyExcel.write(file).build();
WriteSheet errorDataSheet = EasyExcel.writerSheet("导入失败原因").head(ForlanStudentErrorExcelModule.class).build();
excelWriter.write(forlanStudentErrorExcelModuleList, errorDataSheet);
excelWriter.finish();
// 可以上传到OOS或者七牛云...然后然后路径
return file.getPath();
}
e、复制对象内容
public class BeanUtils {
public static Map beanCopierMap = new HashMap();
public static void copyListProperties(List source, List desc, Class descClazz) {
for (Object o : source) {
try {
Object d = descClazz.newInstance();
copyProperties(o, d);
desc.add(d);
} catch (InstantiationException e) {
throw new RuntimeException(e);
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
}
}
public static void copyProperties(Object source, Object target) {
if (source != null) {
String beanKey = generateKey(source.getClass(), target.getClass());
if (!beanCopierMap.containsKey(beanKey)) {
BeanCopier copier = BeanCopier.create(source.getClass(), target.getClass(), false);
beanCopierMap.put(beanKey, copier);
}
beanCopierMap.get(beanKey).copy(source, target, null);
}
}
private static String generateKey(Class cls1, Class cls2) {
return cls1.toString() + cls2.toString();
}
}
以上代码，校验文件格式、文件模板、导入数据限制、文本内容校验，支持全部校验成功才入库、部分校验成功入库，校验失败返回失败原因，导出失败原因