一、使用的easyExcel依赖包
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.2</version></dependency>
二、创建实体类
package com.yqcloud.external.infra.dto.EasyExcelDTO;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.ContentStyle;import com.alibaba.excel.annotation.write.style.HeadStyle;import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;/***@description: todo*@author:郑稳振*@Date : /11/22:14:42*/@ColumnWidth(value = 30)//设置列宽@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//设置头行水平居中@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//设置内容行水平居中public class EasyUserDTO {@ExcelProperty(value = "email")//头行名称private String userEmail;@ExcelProperty(value = "loginName")private String userLoginName;@ExcelProperty(value = "name")private String userRealName;@ExcelProperty(value = "phone")private String userPhone;@ExcelProperty(value = "role")private String userRoleName;@ExcelProperty(value = "companyCode")private String userCompanyCode;@ExcelProperty(value = "departmentCode")private String userDepartmentCode;public String getUserEmail() {return userEmail;}public void setUserEmail(String userEmail) {this.userEmail = userEmail;}public String getUserLoginName() {return userLoginName;}public void setUserLoginName(String userLoginName) {this.userLoginName = userLoginName;}public String getUserRealName() {return userRealName;}public void setUserRealName(String userRealName) {this.userRealName = userRealName;}public String getUserPhone() {return userPhone;}public void setUserPhone(String userPhone) {this.userPhone = userPhone;}public String getUserRoleName() {return userRoleName;}public void setUserRoleName(String userRoleName) {this.userRoleName = userRoleName;}public String getUserCompanyCode() {return userCompanyCode;}public void setUserCompanyCode(String userCompanyCode) {this.userCompanyCode = userCompanyCode;}public String getUserDepartmentCode() {return userDepartmentCode;}public void setUserDepartmentCode(String userDepartmentCode) {this.userDepartmentCode = userDepartmentCode;}@Overridepublic String toString() {return "EasyUserDTO{" +"userEmail='" + userEmail + '\'' +", userLoginName='" + userLoginName + '\'' +", userRealName='" + userRealName + '\'' +", userPhone='" + userPhone + '\'' +", userRoleName='" + userRoleName + '\'' +", userCompanyCode='" + userCompanyCode + '\'' +", userDepartmentCode='" + userDepartmentCode + '\'' +'}';}}
/*** description:** @author 郑稳振* @date /9/1 15:07*/@Overridepublic MultipartFile check(Long tenantId, MultipartFile uploadFile, HttpServletResponse response) {try {OutputStream out = response.getOutputStream();String fileName = URLEncoder.encode("人员数据", "UTF-8");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + XLSX);List<EasyUserDTO> list= EasyExcelFactory.read(uploadFile.getInputStream()).head(EasyUserDTO.class).headRowNumber(2).sheet(0).doReadSync();Map<String,List<EasyUserDTO>> resultMap = checkUserData(list,tenantId);LOGGER.info("校验数据完成！");WriteSheet writeSheet0 = EasyExcelFactory.writerSheet(0, "人员信息").head(EasyUserDTO.class).build();WriteSheet writeSheet1 = EasyExcelFactory.writerSheet(1, "邮箱重复").head(EasyUserDTO.class).build();WriteSheet writeSheet2 = EasyExcelFactory.writerSheet(2, "登录名重复").head(EasyUserDTO.class).build();WriteSheet writeSheet3 = EasyExcelFactory.writerSheet(3, "手机号重复").head(EasyUserDTO.class).build();ExcelWriter excelWriter = EasyExcelFactory.write(out).excelType(ExcelTypeEnum.XLSX).build();excelWriter.write(list, writeSheet0);excelWriter.write(resultMap.get("email"), writeSheet1);excelWriter.write(resultMap.get("loginName"), writeSheet2);excelWriter.write(resultMap.get("phone"), writeSheet3);LOGGER.info("success! 人员数量{}",list.size());excelWriter.finish();out.flush();out.close();} catch (IOException e) {e.printStackTrace();}return null;}
