场景说明

在实际开发中，经常需要解析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;

1、pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.3</version>
</dependency>

2、文件模板

导入模板

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 +
				'}';
	}
}

3、Controller方法

@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);
}

}

4、Service方法

public interface ForlanStudentService {
	String doImport(MultipartFile param);
}

5、主要实现逻辑

a、主方法

@Override
public String doImport(MultipartFile param) {
	String result = "导入成功";
	try (InputStream inputStream = param.getInputStream()) {
		// 解析Excel对象流转成需要的对象
		List<ForlanStudent> forlanStudentList = processExcel(inputStream);
		// 最终入库数据
		List<ForlanStudent> insertData = new ArrayList<>();
		
		// 校验数据，并填充符合的数据
		List<ForlanStudentErrorExcelModule> 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<ForlanStudent> processExcel(InputStream inputStream) throws Exception {
	List<ForlanStudent> forlanStudentList = new ArrayList<>();

	Integer maxRows = 100;
	// 导入模板表头
	List<String> 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<String> 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