使用EasyExcel读取和写入文件

最新推荐文章于 2025-04-15 18:10:51 发布

青梅骑着竹马

最新推荐文章于 2025-04-15 18:10:51 发布

阅读量1.5k

点赞数 1

分类专栏： Java 文章标签： jvm java excel

本文链接：https://blog.youkuaiyun.com/weixin_42708896/article/details/121515177

版权

Java 专栏收录该内容

7 篇文章

订阅专栏

这篇博客介绍了如何使用阿里巴巴的EasyExcel库进行Excel数据的读取和验证，并展示了如何将校验后的数据同步写回Excel，包括设置单元格样式、数据校验以及多工作表写入等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、使用的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 : 2021/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;
    }

    @Override
    public String toString() {
        return "EasyUserDTO{" +
                "userEmail='" + userEmail + '\'' +
                ", userLoginName='" + userLoginName + '\'' +
                ", userRealName='" + userRealName + '\'' +
                ", userPhone='" + userPhone + '\'' +
                ", userRoleName='" + userRoleName + '\'' +
                ", userCompanyCode='" + userCompanyCode + '\'' +
                ", userDepartmentCode='" + userDepartmentCode + '\'' +
                '}';
    }
}

/**
 * description:
 *
 * @author 郑稳振
 * @date 2021/9/1 15:07
 */
 @Override
    public 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;
    }