Java使用EasyExcel进行excel读写操作指南

前言

EasyExcel是一个专为Java设计的高效Excel处理工具，它旨在帮助开发者在处理大文件时避免内存溢出问题，同时提供简单易用的API来进行Excel的读写操作。相较于Apache POI和jxl等其他库，EasyExcel通过优化的解析算法显著降低了内存消耗，特别是对于07版以上的Excel文件，其内存占用量远低于使用POI SAX模式解析时的需求，几乎消除了内存溢出的风险。此外，EasyExcel对03版Excel的支持则是基于POI的SAX模式并进行了进一步的模型转换封装，提高了使用的便捷性。

一、依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>4.0.3</version>
        </dependency>

二、读取excel指定sheet页数据分批处理

假设有 excel 如下

1.excel列名对应的映射实体类

使用@ExcelProperty(value = “”)来标识excel列名

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class RowData {
    
    /** 企业订单号 */
    @ExcelProperty(value = "企业订单号")
    private String enterpriseOrderNo;
  
    
    /** 所在部门（新）编号 */
    @ExcelProperty(value = "所在部门（新）编号")
    private Integer departmentNewNo;
    
    /** 乘车人姓名 */
    @ExcelProperty(value = "乘车人姓名")
    private String passengerName;
   
    
    /** 订单总金额 */
    @ExcelProperty(value = "订单总金额")
    private BigDecimal totalOrderAmount;
    
    /** 企业实付金额 */
    @ExcelProperty(value = "企业实付金额")
    private BigDecimal enterpriseActualPayment;
   
    
    /** 订单类型 */
    @ExcelProperty(value = "订单类型")
    private String orderType;
    
    /** 下单时间 */
    @ExcelProperty(value = "下单时间")
    private Date orderTime;
}

2.监听类，用于处理读取的excel行数据

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson2.JSON;
import hai.tang.model.RowData;

import java.util.List;

// 有个很重要的点 RowDataListener 不能被spring管理，所以如果使用spring要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class RowDataListener implements ReadListener<RowData> {

    /**
     * 每1000条存储数据库或者进行其他操作，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    /**
     * 缓存的数据
     */
    private List<RowData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    //   private DemoDAO demoDAO;

//    public RowDataListener() {
    // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
//        demoDAO = new DemoDAO();
//    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
//    public RowDataListener(DemoDAO demoDAO) {
//        this.demoDAO = demoDAO;
//    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(RowData data, AnalysisContext context) {
        System.out.println("解析到一条数据:{}" + JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

   /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        System.out.println("所有数据解析完成！");
    }

    /**
     * 存储到数据库，或者可改为对数据进行其他操作
     */
    private void saveData() {
        System.out.println("{}条数据，开始存储数据库！" + cachedDataList.size());
        //demoDAO.save(cachedDataList);
        System.out.println("存储数据库成功！");
    }
}

3.测试

        String fileName = "C:\\Users\\LAPtOP\\Desktop\\工作簿1.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取"用车订单"sheet，结束后文件流会自动关闭
        EasyExcel.read(fileName, RowData.class, new RowDataListener()).sheet("用车订单").doRead();

三、数据写入excel

1.数据量比较小可以一次性写入

        String fileName = "C:\\Users\\LAPtOP\\Desktop\\工作簿1.xlsx";
        //从数据库查询出的数据，或者构造的数据
        List<RowData> list = ...;
        //如果数据只有一万行左右，可以一次性写入。将数据写入 "用车订单" sheet 页
        EasyExcel.write(fileName, RowData.class).sheet("用车订单").doWrite(list);

2.数据量大，分批多次写入

        String fileName = "C:\\Users\\LAPtOP\\Desktop\\工作簿1.xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, RowData.class).build()) {
            // 这里注意 如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet("用车订单").build();
            // 这里调用了五次，每次获得一批数据写入。实际使用时根据数据库分页的总的页数来
            for (int i = 0; i < 5; i++) {
                // 分页去数据库查询数据或者构建数据 这里可以去数据库查询每一页的数据
                List<RowData> list = ...;
                excelWriter.write(list, writeSheet);
            }
        }

四、excel文件的上传和下载

1.下载

/**
    * 文件下载（失败了会返回一个有部分数据的Excel）
    * <p>
    * 1. 创建excel对应的实体对象 参照{@link DownloadData}
    * <p>
    * 2. 设置返回的 参数
    * <p>
    * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
    */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName=URLEncoder.encode("测试","UTF-8").replaceAll("\\+","%20");
        response.setHeader("Content-disposition","attachment;filename*=utf-8''"+fileName+".xlsx");
        List<RowData> list = ...;
        EasyExcel.write(response.getOutputStream(),RowData.class).sheet("sheet").doWrite(list);
    }

2. 上传

    /**
     * 文件上传
     * <p>1. 创建excel对应的实体对象 参照{@link UploadData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UploadDataListener}
     * <p>3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file)throws IOException{
        EasyExcel.read(file.getInputStream(),RowData.class,new UploadDataListener(uploadDAO)).sheet().doRead();
        return"success";
    }

五、分批读取并写入综合示例

下面的代码会读取excel文件里的指定sheet页，然后读取指定列的数据，最后新建一个excel文件，把读取到的列数据写入到新建的excel文件的指定sheet里。

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.List;

// 有个很重要的点 RowDataListener 不能被spring管理，所以如果使用spring要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class RowDataListener implements ReadListener<DiDiReconcileExcelParseModel> {

    /**
     * 每读取1000条数据进行操作，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    /**
     * 缓存的数据
     */
    private List<DiDiReconcileExcelParseModel> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 用于写入excel
     */
    private ExcelWriter excelWriter = null;
    /**
     * 用于写入excel
     */
    private WriteSheet writeSheet = null;


    public RowDataListener(ExcelWriter excelWriter, WriteSheet writeSheet) {
        this.excelWriter = excelWriter;
        this.writeSheet = writeSheet;
    }

    /**
     * 获取表头和其他行的水平和垂直居中设置
     */
    public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        // 设置表头单元格样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置水平居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置除表头外的其他行的内容单元格样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 使用自定义样式策略
       return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }


    /**
     * 每一条数据解析都会调用该方法一次
     *
     * @param data    one row value. Is is same as
     * @param context
     */
    @Override
    public void invoke(DiDiReconcileExcelParseModel data, AnalysisContext context) {
        //System.out.println("解析到一条数据:" + JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 当所有数据全部解析读取完后 会调用该方法一次
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        System.out.println("所有数据写入完成！");
        excelWriter.finish();
        if (excelWriter != null) {
            excelWriter.close();
        }
    }

    /**
     * 保存数据
     */
    private void saveData() {
        System.out.println("写入" + cachedDataList.size()+"条数据");
        excelWriter.write(cachedDataList, writeSheet);
    }
}

测试：

        String writeFile = "C:\\Users\\LEPLOP\\Desktop\\写入.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(writeFile, DiDiReconcileExcelParseModel.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("用车订单")
                //设置垂直和水平居中
                .registerWriteHandler(RowDataListener.getHorizontalCellStyleStrategy())
                .build();

        //从 readFile 中的 "用车订单" sheet中读取指定的列数据（DiDiReconcileExcelParseModel 里指定的列），然后通过excelWriter, writeSheet 写入到 writeFile中
        String readFile = "C:\\Users\\LEPLOP\\Desktop\\test\\账单.xlsx";
        EasyExcel.read(readFile, DiDiReconcileExcelParseModel.class, new RowDataListener(excelWriter, writeSheet))
                .sheet("用车订单")
                .doRead();

根据测试，读取一个14万行并有216列的excel文件，然后挑选其中6列写入新的excel文件，大概耗时不到3分钟