需求
1.导出Excel模板，第一个sheet为用户基本信息，默认只有表头，用户手动填写后续用来导入人员信息。第二个sheet为组织架构信息，默认从数据库查询组织信息，方便用户查询组织编码。
2.导入Excel，将人员信息入库。
导出效果
依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency>
表格模板
注意，前面有个点{.name} {.code}
填充模板并导出
倒数第二行代码是指定sheet，本需求只填第二个sheet的数据。可以根据自己需求指定想要填充的sheet。
/*** 导出Excel模板** @param response response* @throws Exception Exception*/@ApiOperation(value = "用户Excel模板导出", notes = "用户Excel模板导出")@RequestMapping(value = "/export", method = RequestMethod.POST)public void export(HttpServletResponse response) throws Exception {// 注意Map的key必须和模板保持一致,举例是name 和codeList<Map<String, String>> excelList = 查询要填充的列表;// 设置响应类型response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");// 设置编码格式response.setCharacterEncoding("utf-8");long currentTime=System.currentTimeMillis();// 设置URLEncoder.encode 防止中文乱码String fileName = URLEncoder.encode("人员组织架构模板-" + currentTime, "UTF-8").replaceAll("\\+", "%20");// 设置响应头response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 使用模板填充表格，这里将表格预制到代码resources下面了，可以根据需求预制到其他地方，绝对路径和相对路径都可。Resource resource = new ClassPathResource("excelTmp/personTmp.xlsx");String templateFileName = resource.getFile().getPath();// 生成工作簿对象ExcelWriterBuilder workBookWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName);// 创建工作表对象ExcelWriterSheetBuilder sheet = workBookWriter.sheet(1);sheet.doFill(excelList);}
导入Exce解析入库
/*** 人员导入** @return BaseResponse* @throws Exception Exception*/@ApiOperation(value = "人员导入", notes = "人员导入")@RequestMapping(value = "personImport", method = RequestMethod.POST)public BaseResponse personImport(MultipartFile file) throws Exception {// 1.解析ExcelUserListener userListener = new UserListener();EasyExcel.read(file.getInputStream(), UserSheet.class, userListener).sheet(0).doRead();List<UserSheet> userList = userListener.getUserList();// TODO 需要修改 2.校验参数BaseResponse<String> checkResp = checkParam(userList);if (checkResp != null) {return checkResp;}// TODO 需要修改 3.数据插入 这里可以根据自己需求去拼接对象insert到指定表中//insertPersonData(userList);return BaseResponse.getResponse(true, BaseResponse.MSG_UPDATE_SUCCESS, "", 1);}
UserListener.java
public class UserListener extends AnalysisEventListener<UserSheet> {@Getterprivate List<UserSheet> userList = new ArrayList<UserSheet>();public UserListener() {super();userList.clear();}/*** 每一条数据解析都会调用*/@Overridepublic void invoke(UserSheet user, AnalysisContext context) {userList.add(user);}/*** 所有数据解析完成都会调用*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {userList.forEach(System.out::println);}}
UserSheet.java
@Data@NoArgsConstructorpublic class UserSheet {@ExcelProperty(value = "姓名", index = 0)@ColumnWidth(10)private String userName;@ExcelProperty(value = "编码", index = 1)@ColumnWidth(20)private String userCode;@ExcelProperty(value = "性别", index = 2)@ColumnWidth(10)private String gender;@ExcelProperty(value = "出生日期", index = 3)@ColumnWidth(20)private String birthday;@ExcelProperty(value = "手机号", index = 4)@ColumnWidth(30)@ContentStyle(wrapped = BooleanEnum.TRUE)private String phone;@ExcelProperty(value = "所属组织编码", index = 5)@ColumnWidth(30)@ContentStyle(wrapped = BooleanEnum.TRUE)private String organizationCode;}
