EasyExcel教程

EasyExcel

概述

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

github地址：https://github.com/alibaba/easyexcel

开源项目不容易，如果觉得本项目对您的工作还是有帮助的话，请在帮忙在点个★Star。

EasyExcel控制表格注解

@ContentRowHeight(int):

设置 row 高度，不包含表头
标记在 类上
@ContentRowHeight(15) //设置行高

@HeadRowHeight(int)：

设置 表头 高度（与 @ContentRowHeight 相反）
标记在 类上
@HeadRowHeight(20)  //设置表头高度

@ColumnWidth(int)：

设置列宽
标记在属性上
@ColumnWidth(20)  //设置列宽

@ExcelProperty(value = String[], index = int):

设置表头信息
value: 表名称
index: 列号

@ExcelProperty(index = 0, value = "学生名字")

Excel导出

依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.0.0-beta5</version>
</dependency>

创建Excel实体类对象

@Data
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(20)
public class StudentCurrentExcel {
    @ExcelProperty(index = 0, value = "学生名字")
    private String studentName;

    @ExcelProperty(index = 1, value = "性别")
    private String gender;

    @ExcelProperty(index = 2, value = "年级/班级")
    private String deptCode;

    @ExcelProperty(index = 3, value = "通道名称")
    private String screenName;

    @ExcelProperty(index = 4, value = "通行方向")
    private String direction;

    @ExcelProperty(index = 5, value = "通行时间")
    private Date passageTime;

    @ExcelProperty(index = 6, value = "体温")
    private String temperate;

    @ExcelProperty(index = 7, value = "组织结构")
    private Integer deptId;
}

导出Controller

@UnAuthorized
@ApiOperation(value = "导出")
@GetMapping("/export")
public void export(HttpServletResponse response, HttpServletRequest request) throws IOException {
    //设置响应类型
    response.setContentType("application/vnd.ms-excel");
    //设置字符编码
    response.setCharacterEncoding("utf-8");
    //设置文件名字
    String fileName = "记录" + DateUtils.getDate("yyyy-MM-dd HH:mm") + ".xlsx";
    //设置响应头信息
    response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));

    List<StudentCurrent> all = this.studentCurrentService.findAll();

    List<StudentCurrentExcel> list = getNotPassData2(all);
    //写入文件数据
    EasyExcel.write(response.getOutputStream(), StudentCurrentExcel.class).sheet("记录").doWrite(list);
}

实体对象格式转换

/**
* 将实体类对象 转为Excel对象
* @param studentCurrentList
* @return
*/
private List<StudentCurrentExcel> getNotPassData2(List<StudentCurrent> studentCurrentList) {
    List<StudentCurrentExcel> list = new ArrayList<>();
    for (StudentCurrent s : studentCurrentList) {
        StudentCurrentExcel excel = new StudentCurrentExcel();
        list.add(excel);
    }
    return list;
}

Excel导入实现

实体类：

@Data
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(20)
public class UserExcel {

    @ExcelProperty(index = 0, value = "姓名")
    private String name;

    @ExcelProperty(index = 1, value = "性别")
    private String gender;

    @ExcelProperty(index = 2, value = "工号")
    @ExcelParamValidate(regular = "^[0-9]*$", msg = "工号必须为纯数字")
    @NumberFormat("#")
    private String number;

    @ExcelProperty(index = 3, value = "手机号")
    @NumberFormat("#")
    private String phone;

    @ExcelProperty(index = 4, value = "部门")
    private String deptPath;

    @ExcelProperty(index = 5, value = "身份证号")
    private String idCard;

    @ExcelProperty(index = 6, value = "录入设备")
    private String deviceList;

    @ExcelProperty(index = 7, value = "绑定班次")
    private String scheduleList;

    @ExcelProperty(index = 8, value = "职位")
    private String dutyName;
}

模板下载 控制器

/**
* 员工信息excel模板下载
* @param response
* @throws IOException
*/
@ApiOperation(value = "员工信息excel模板下载")
@GetMapping("/user/template/export")
public void downloadExcelTemplate(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String uuid2 = StringUtilsEx.getUUID2();
    String fileName="员工信息模板_"+ uuid2 +".xlsx";
    response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
    EasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("批量导入员工信息模板").doWrite(data());
}

信息模板

/**
 * 员工信息模板
 * @return
 */
private List<UserExcel> data() {
    List<UserExcel> list = new ArrayList<>();
    UserExcel data = new UserExcel();
    data.setName("张三");
    data.setGender("男");
    data.setPhone("15939421234");
    data.setNumber("20201025");
    data.setIdCard("410421*********");
    data.setDeptPath("销售部");
    data.setDeviceList("门口出,门口进");
    data.setScheduleList("上午班，下午班");
    data.setDutyName("工会");
    list.add(data);
    return list;
}

导入控制器

/**
* 批量导入员工信息
* @param file
* @param response
* @return
*/
@LogAnnotation("批量导入员工基本信息")
@ApiOperation(value = "批量导入员工，不能有图片")
@PostMapping("/user/import")
public JSONObject importStudents(MultipartFile file, HttpServletResponse response) {
    List<Object> objects = new ArrayList<>();
    String filename = file.getOriginalFilename();
    // 记录 导入日志
    ImportMsg importMsg=null;
    importMsg = new ImportMsg().setFileName(filename).setImportStartTime(new Date());
    importMsg.setType("导入员工基本信息");
    if (filename.toLowerCase().endsWith("xlsx")||filename.toLowerCase().endsWith("xls")){
            InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            throw new GlobalException(FAIL.CODE,FAIL.ZH_CODE+"：读取文件时出现异常",FAIL.ZH_CODE);
        }

        objects = EasyExcel.read(inputStream, UserExcel.class, new SyncReadListener()).headRowNumber(1).sheet().doReadSync();
        // 从execl中读取到用户信息
        List<UserExcelError> userExcelErrorList = iUserService.uploadUserNoPhotoAndNoTransaction(objects);
        if (userExcelErrorList.isEmpty()){
            importMsg.setImportErrorMsg("成功【"+objects.size()+"】条，失败【"+userExcelErrorList.size()+"】");
        }else {
            String errorFileName ="失败员工基本信息_"+  DateUtils.getDate("yyyyMMddHHmmss")+".xlsx";
            File file1 = new File(excelDir);
            if (!file1.exists()){
            file1.mkdirs();
        }
        EasyExcel.write(excelDir + "/"+errorFileName, UserExcelError.class).sheet("失败的信息").doWrite(userExcelErrorList);
        importMsg.setErrorFileName(errorFileName);
        importMsg.setImportErrorMsg("成功【"+(objects.size()-userExcelErrorList.size())+"】条，失败	【"+userExcelErrorList.size()+"】");
        }
    }else {
   		importMsg.setImportErrorMsg("请上传excel文件");
    }
    importMsg.setImportEndTime(new Date());
    iImportMsgService.save(importMsg);
    // 返回状态信息
    return success(importMsg);
}

信息模板

/**
 * 员工信息模板
 * @return
 */
private List<UserExcel> data() {
    List<UserExcel> list = new ArrayList<>();
    UserExcel data = new UserExcel();
    data.setName("张三");
    data.setGender("男");
    data.setPhone("15939421234");
    data.setNumber("20201025");
    data.setIdCard("410421*********");
    data.setDeptPath("销售部");
    data.setDeviceList("门口出,门口进");
    data.setScheduleList("上午班，下午班");
    data.setDutyName("工会");
    list.add(data);
    return list;
}

错误实体类

@JsonIgnoreProperties(value = {"hibernateLazyInitializer", "handler"})
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
@Entity
@Table(name ="t_import_error_msg")
@Where(clause="del_flag="+ BaseEntity.DEL_FLAG_NORMAL)
public class ImportMsg extends BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "type",columnDefinition = "varchar(255) comment '消息类型'")
    private String type;

    @Column(name = "file_name",columnDefinition = "varchar(255) comment '原始导入文件名'")
    private String fileName;

    @Column(name = "error_file_name",columnDefinition = "varchar(255) comment '导入错误的文件名，用于用户下载'")
    private String errorFileName;

    @JsonFormat(pattern = DATE_FORMAT, timezone = "GMT+8")
    @Column(name = "import_start_time",columnDefinition = "timestamp default now() comment '导入的开始时间'")
    private Date importStartTime;

    @JsonFormat(pattern = DATE_FORMAT, timezone = "GMT+8")
    @Column(name = "import_end_time",columnDefinition = "timestamp default now() comment '导入的结束时间'")
    private Date importEndTime;

    @Column(name = "import_error_msg",columnDefinition = "varchar(255) comment '导入错误的反馈信息'")
    private String importErrorMsg;

    @Column(name="state",columnDefinition = "varchar(8) comment '此信息的处理状态'")
    @Convert(converter = Constant.Convert.class)
    private Constant state= Constant.IMPORT_UNTREATED;
}

批量导入图片

目录

    @Value("${userfiles.excel}")
    private String excelDir;
    
    @Value("${userfiles.zip}")
    private String zipDir;

    @Value("${userfiles.unzip}")
    private String unZipDir;

Controller

@LogAnnotation("批量导入照片")
@ApiOperation(value = "导入zip图片压缩包")
@PostMapping("/user/zip/import")
public JSONObject importZip(MultipartFile file) {
    String filename = file.getOriginalFilename();
    ImportMsg importMsg=null;
    importMsg = new ImportMsg().setFileName(filename).setImportStartTime(new Date());
    importMsg.setType("导入员工图片信息");
    boolean zip = filename.endsWith("zip");
    if (!zip){
        importMsg.setImportErrorMsg("请上传zip压缩包");
    }else {
        String date = DateUtils.getDate("yyyyMMddHHmmss");
        String zipAllFilename = zipDir + "/" + date+".zip";
        FileUtils.uploadPhoto(file,zipAllFilename);
        String unZipAllFileDir= unZipDir + "/" + date;
        FileUtils.unZipFiles(zipAllFilename, unZipAllFileDir);
        List<File> files=new ArrayList<>();
        files = FileUtils.getFiles(unZipAllFileDir, files);

        List<PhotoExcelError> photoExcelErrors = iUserService.uploadUserPhotoNoTransaction(files);
        if (photoExcelErrors.isEmpty()){
            importMsg.setImportErrorMsg("成功【"+files.size()+"】条，失败【"+photoExcelErrors.size()+"】");
        }else {
            String s = "员工照片失败信息_"+date+".xlsx";
            EasyExcel.write(excelDir + "/" + s, PhotoExcelError.class).sheet("失败的信息").doWrite(photoExcelErrors);
            importMsg.setErrorFileName(s).setImportErrorMsg("成功【"+(files.size()-photoExcelErrors.size())+"】条，失败【"+photoExcelErrors.size()+"】");
        }
    }
    importMsg.setImportEndTime(new Date());
    iImportMsgService.save(importMsg);
    return success(importMsg.getImportErrorMsg());
}

Service

/**
 * 批量导入照片
 * @param files
 * @return
 */
List<PhotoExcelError> uploadPhotoNoTransaction(List<File> files);

ServiceImpl

/**
 * 批量导入照片
 * @param files
 * @return
 */
@Override
public List<PhotoExcelError> uploadPhotoNoTransaction(List<File> files) {
    List<PhotoExcelError> photoExcelErrors=new ArrayList<>();
    for(File file:files){
        String number = FileUtils.getFileNameWithoutExtension(file.getName());
        if(StringUtils.isBlank(number)){
            photoExcelErrors.add(createPhotoExcelError(file.getName(),"导入失败","照片名字为空或照片名字中含有小数点",""));
            continue;
        }
        //User user = findByUserId(Integer.valueOf(number));
        User user = findByUserName(number);
        String suffix = FileUtils.getFileExtension(file.getName());
        if (!(suffix.equals("jpg") || suffix.equals("jpeg") || suffix.equals("png"))) {
            photoExcelErrors.add(createPhotoExcelError(file.getName(), "导入失败", "上传照片类型不支持，照片类型必须是jpg,jpeg,png类型", ""));
            continue;
        }
        if(ObjectUtils.isEmpty(user)){
            photoExcelErrors.add(createPhotoExcelError(file.getName(),"导入失败","根据照片名字没有找到员工信息",""));
            continue;
        }
        if(StringUtils.isNotBlank(user.getPhoto())){
            photoExcelErrors.add(createPhotoExcelError(file.getName(),"导入失败","该员工已有照片，不可重复上传",""));
            continue;
        }
        /*List<DeviceUser> deviceUserList = deviceUserDao.findAllByUserId(user.getId());
        if(ObjectUtils.isEmpty(deviceUserList)){
            photoExcelErrors.add(createPhotoExcelError(file.getName(),"导入失败","没有找到该员工与设备信息的绑定关系",""));
            continue;
        }*/
        try {
            user.setPhoto(FileStoreUtils.createFile(file));
        } catch (IOException e) {
            e.printStackTrace();
            photoExcelErrors.add(createPhotoExcelError(file.getName(),"导入失败","文件上传异常",""));
            continue;
        }

        Map<String,File> fileMap=new HashMap<>();
        fileMap.put("image",file);
        Map<String,String> map=new HashMap<>();
        JSONObject description=new JSONObject();
        description.put("name",user.getName());
        description.put("userId",user.getId());
        map.put("description", description.toJSONString());
        try {
            map.put("imageMd5",FileUtils.file2Base64(file));
        } catch (IOException e) {
            e.printStackTrace();
            photoExcelErrors.add(createPhotoExcelError(file.getName(),"导入失败","解析图片md5失败",""));
            continue;
        }
        JSONObject jsonObject=null;
        JSONObject response=HttpURLConnectionUtils.post(deviceAddr+ B3rInterface.B3R_CONFIG.PATH+B3rInterface.B3R_5_1.PATH,map,fileMap);
        logger.error("=============上传B3r"+deviceAddr);
        String code=response.getString("code");
        if("0".equals(code)){
            jsonObject=response;
        }else {
            photoExcelErrors.add(createPhotoExcelError(file.getName(),"导入失败",response.getString("message"),""));
            continue;
        }
        JSONObject data=jsonObject.getJSONObject("data");
        String faceToken=data.getString("faceToken");
        String imageId = data.getString("imageId");
        JSONArray jsonArray=new JSONArray();
        jsonArray.add(0, faceGroupName);
        Map<String,Object> json=new HashMap<>();
        json.put("faceGroupList",jsonArray);

        //发送请求 保存信息
        JSONObject response1 = HttpURLConnectionUtils.post(deviceAddr+B3rInterface.B3R_CONFIG.PATH+B3rInterface.B3R_6_7.PATH+faceToken,json);
        String code1=response1.getString("code");
        if("0".equals(code1)) {
            Device device = deviceDao.getOne(Integer.valueOf(user.getDeviceList()));
            DeviceUser deviceUser = deviceUserDao.findByUserIdAndDeviceId(user.getId(), device.getId());
            deviceUser.setFaceToken(faceToken).setImageId(imageId);
            deviceUserDao.save(deviceUser);
        }else {
            photoExcelErrors.add(createPhotoExcelError(file.getName(),"导入失败","绑定人脸分组失败，"+response.getString("message"),""));
            continue;
        }

        this.save(user);
    }
    return photoExcelErrors;
}