Java实现Excel批量导入数据库

场景说明

在实际开发中，经常需要解析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
2
3
4
5
6

1、pom.xml

<dependency>
    <groupId>com.alibabagroupId>
    <artifactId>easyexcelartifactId>
    <version>2.2.3version>
dependency>
1
2
3
4
5

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;
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

错误失败模板

@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 +
				'}';
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

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";
	}
	if (param.getSize() > 10L * 1024L * 1024L) {
		return RespHandler.failure("文件大小不超过10M");
	}
	return forlanStudentService.doImport(param);
}

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4、Service方法

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

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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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.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();
		}
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

c、校验数据，并填充入库数据、错误数据行

private List<ForlanStudentErrorExcelModule> checkDataAndFill(List<ForlanStudent> forlanStudentList, List<ForlanStudent> insertData) {
	List<ForlanStudentErrorExcelModule> 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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

d、错误数据行原因生成文件

private String generateExceptionFile(List<ForlanStudentErrorExcelModule> 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();
}
1
2
3
4
5
6
7
8
9

e、复制对象内容

public class BeanUtils {

	public static Map<String, BeanCopier> beanCopierMap = new HashMap<String, BeanCopier>();

	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();
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

---

总结

以上代码，校验文件格式、文件模板、导入数据限制、文本内容校验，支持全部校验成功才入库、部分校验成功入库，校验失败返回失败原因，导出失败原因