EasyExcel学习笔记

jupiter
2023-09-17 / 0 评论 / 95 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2023年09月17日,已超过160天没有更新,若内容或图片失效,请留言反馈。

1.简介

1.1 EasyExcel简介

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便

2.SpringBoot集成easyexcel

2.1 pom依赖

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

2.2 简单使用

实体类

@Data
public class ArticleScoreData {
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("文章")
    private String title;
    @ExcelProperty("得分")
    private Double score;
}

读操作

  • 待读取article.xlsx
姓名文章得分
张三张三的文章87
李四李四的文章34
王五王五的文章99
  • 读操作代码
@Test
    public void testRead() {
        String pathName = "C:\\Users\\jupiter\\Desktop\\article.xlsx";
        // PageReadListener:excel一行一行的回调监听器
        EasyExcel.read(pathName, ArticleScoreData.class, new PageReadListener<ArticleScoreData>(dataList -> {
            for (ArticleScoreData demoData : dataList) {
                log.info("读取到一条数据{}", "姓名:" + demoData.getName() + " 文章:" + demoData.getTitle() + " 得分:" + demoData.getScore());
            }
        })).sheet().doRead();
    }
  • 运行结果
2023-09-14T21:41:36.957+08:00  INFO 81220 --- [           main] c.e.e.EasyExcelStudyApplicationTests     : 读取到一条数据姓名:张三 文章:张三的文章 得分:87.0
2023-09-14T21:41:36.962+08:00  INFO 81220 --- [           main] c.e.e.EasyExcelStudyApplicationTests     : 读取到一条数据姓名:李四 文章:李四的文章 得分:34.0
2023-09-14T21:41:36.962+08:00  INFO 81220 --- [           main] c.e.e.EasyExcelStudyApplicationTests     : 读取到一条数据姓名:王五 文章:王五的文章 得分:99.0

写操作

  • 代码
@Test
    public void testWrite() {
        String xlsxPath = "C:\\Users\\jupiter\\Desktop\\output.xls";

        List<ArticleScoreData> dataList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            ArticleScoreData data = new ArticleScoreData();
            data.setName("姓名" + i)
            data.setTitle("文章" + i);
            data.setScore(80.0+i);
            dataList.add(data);
        }

        EasyExcel.write(xlsxPath, ArticleScoreData.class)
                .sheet("文章得分表")
                .doWrite(() -> dataList);
    }
  • 运行效果output.xls
姓名文章得分
姓名0文章080
姓名1文章181
姓名2文章282
姓名3文章383
姓名4文章484

2.3 单独实现最简单的读的监听器进行文件读取

待读取article.xlsx

姓名文章得分
张三张三的文章87
李四李四的文章34
王五王五的文章99

实体类

@Data
public class ArticleScoreData {
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("文章")
    private String title;
    @ExcelProperty("得分")
    private Double score;
}

SimpleDataListener

import cn.hutool.json.JSONUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.example.easyexcelstudy.domain.entity.ArticleScoreData;
import lombok.extern.slf4j.Slf4j;

import java.util.Map;
@Slf4j
public class SimpleDataListener implements ReadListener<ArticleScoreData> {
    /**
     * 解析excel的表头-第一行
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        ReadListener.super.invokeHead(headMap, context);
        log.info("读取到表头:{}",JSONUtil.toJsonStr(headMap));
    }

    /**
     * 读取excel的每一行都会调用该方法
     */
    @Override
    public void invoke(ArticleScoreData articleScoreData, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSONUtil.toJsonStr(articleScoreData));
    }

    /**
     * 所有数据解析完成了,都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析完成!");
    }
}

test

    @Test
    public void testReadBySimpleDataListener() {
        String xlsxPath = "C:\\Users\\jupiter\\Desktop\\article.xlsx";
        EasyExcel.read(xlsxPath,new SimpleDataListener()).sheet().doRead();
    }

运行结果

2023-09-14T22:39:47.596+08:00  INFO 186196 --- [           main] c.e.e.util.excel.SimpleDataListener      : 读取到表头:
{
    "0": {
        "dataFormatData": {
            "index": 0,
            "format": "General"
        },
        "type": "STRING",
        "stringValue": "姓名",
        "rowIndex": 0,
        "columnIndex": 0
    },
    "1": {
        "dataFormatData": {
            "index": 0,
            "format": "General"
        },
        "type": "STRING",
        "stringValue": "文章",
        "rowIndex": 0,
        "columnIndex": 1
    },
    "2": {
        "dataFormatData": {
            "index": 0,
            "format": "General"
        },
        "type": "STRING",
        "stringValue": "得分",
        "rowIndex": 0,
        "columnIndex": 2
    }
}
2023-09-14T22:39:47.689+08:00  INFO 186196 --- [           main] c.e.e.util.excel.SimpleDataListener      : 解析到一条数据:{"title":"张三的文章","score":87,"name":"张三"}
2023-09-14T22:39:47.690+08:00  INFO 186196 --- [           main] c.e.e.util.excel.SimpleDataListener      : 解析到一条数据:{"title":"李四的文章","score":34,"name":"李四"}
2023-09-14T22:39:47.690+08:00  INFO 186196 --- [           main] c.e.e.util.excel.SimpleDataListener      : 解析到一条数据:{"title":"王五的文章","score":99,"name":"王五"}
2023-09-14T22:39:47.691+08:00  INFO 186196 --- [           main] c.e.e.util.excel.SimpleDataListener      : 所有数据解析完成!

2.4 (★★★)读取超级版本:无需实体类,实现任意excel文件的读取

待读取excel文件

  • sheet1

  • sheet2

    正常情况2
    表头1表头2表头3表头4表头5
    表头1表头2表头3表头4表头5
    列头1
    列头2
    列头3

ExcelSheetDataReadListener

package com.example.excelstudy.utils.excel;

import cn.hutool.json.JSONUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author LuoJia
 * @version 1.0
 * @description: 万能excel的单个sheet读取Listener
 * @date 2023/9/15 11:24
 */
@Slf4j
public class ExcelSheetDataReadListener extends AnalysisEventListener<Map<Integer,String>> {
    // 表格sheet编号
    int sheetNo;
    // 表格行数
    int rowCount=0;
    // 表格列数
    int colCount=0;
    // 用于存储原生读取到的数据
    List<Map<Integer, String>> lineDataList = new ArrayList<>();
    // 用于存储表格的合并单元格的区域列表
    List<CellExtra> mergeAreaList = new ArrayList<>();

    /**
     * 解析excel的表头-即读取第一行
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // 设置不忽略空行
        context.readWorkbookHolder().setIgnoreEmptyRow(false);

        // 获取sheetNo
        sheetNo = context.readSheetHolder().getSheetNo();

        // 更新表格行列数
        rowCount += 1;
        colCount = Math.max(colCount, headMap.size());

        // 保存原始的单行数据
        lineDataList.add(headMap);
        //log.info("读取到表头:{}", JSONUtil.toJsonStr(headMap));
    }

    /**
     * 读取excel的每一行都会调用该方法
     */
    @Override
    public void invoke(Map<Integer,String> lineData, AnalysisContext context) {
        // 更新表格行列数
        rowCount += 1;
        colCount = Math.max(colCount, lineData.size());

        // 保存原始的单行数据
        lineDataList.add(lineData);
        //log.info("解析到一条数据:{}", JSONUtil.toJsonStr(lineData));
    }

    /**
     * 获取合并单元格的范围
     */
    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        if (extra.getType() != CellExtraTypeEnum.MERGE) {
            return ;
        }
        mergeAreaList.add(extra);
    }

    /**
     * 所有数据解析完成了,都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("=============================================================");
        log.info("sheet{}-所有数据解析完成!sheet总行数:{},总列数:{}",sheetNo+1,rowCount,colCount);

        // 处理mergerList--即处理所有的合并单元格
        for(CellExtra mergeArea:mergeAreaList){
            // 获取填充部分的单元格的有效值
            String value = lineDataList.get(mergeArea.getFirstRowIndex()).get(mergeArea.getFirstColumnIndex());
            // 对合并单元格的为null值部分的数据进行有效填充
            for (int i = mergeArea.getFirstRowIndex(); i <= mergeArea.getLastRowIndex(); i++) {
                for (int j = mergeArea.getFirstColumnIndex(); j <= mergeArea.getLastColumnIndex(); j++) {
                    // 合并单元格的最最左上角已经被有效填充了,跳过
                    if(i==mergeArea.getFirstRowIndex()&&j== mergeArea.getFirstColumnIndex()){
                        continue;
                    }
                    // 对合并单元格的其他单元格进行数据填充
                    lineDataList.get(i).put(j,value);
                }
            }
        }

        // 打印表格数据
        for (int i = 0; i < rowCount; i++) {
            log.info("sheet第{}行数据:{}",(i+1),JSONUtil.toJsonStr(lineDataList.get(i)));
        }
        log.info("=============================================================");
    }
}

test

    @Test
    public void testRead() throws FileNotFoundException {
        String pathName = "C:\\Users\\LuoJia\\Desktop\\test.xlsx";
        InputStream inputStream = new FileInputStream(new File(pathName));

        // 创建excel读取reader
        ExcelReader excelReader = EasyExcel.read(inputStream).extraRead(CellExtraTypeEnum.MERGE).ignoreEmptyRow(false).build();

        // 创建每个sheet的读取listener并执行读取
        List<ReadSheet> readSheets = excelReader.excelExecutor().sheetList();
        List<ExcelSheetDataReadListener> listenerList = new ArrayList<>(readSheets.size()); // 用于进行数据和合并单元格区域保存
        //读取多个sheet
        List<ReadSheet> sheetList = readSheets.stream().map(sheet -> {
            ExcelSheetDataReadListener listener = new ExcelSheetDataReadListener();
            ReadSheet readSheet = EasyExcel.readSheet(sheet.getSheetName()).registerReadListener(listener).build();
            listenerList.add(listener);
            return readSheet;
        }).collect(Collectors.toList());
        excelReader.read(sheetList);

        // 释放资源
        excelReader.finish();
    }

运行结果

xxxx: =============================================================
xxxx: sheet1-所有数据解析完成!sheet总行数:10,总列数:6
xxxx: sheet第1行数据:{}
xxxx: sheet第2行数据:{"0":"异常情况","1":"异常情况","2":"异常情况","3":"异常情况","4":"异常情况"}
xxxx: sheet第3行数据:{"0":"表头1","1":"表头2","2":"表头3","3":"表头4","4":"表头5"}
xxxx: sheet第4行数据:{"0":"表头1","1":"表头2","2":"表头3","3":"表头4","4":"表头5"}
xxxx: sheet第5行数据:{}
xxxx: sheet第6行数据:{}
xxxx: sheet第7行数据:{"3":"dasdada","4":"dasdada"}
xxxx: sheet第8行数据:{"3":"dasdada","4":"dasdada"}
xxxx: sheet第9行数据:{}
xxxx: sheet第10行数据:{"5":"saSASA"}
xxxx: =============================================================
xxxx: =============================================================
xxxx: sheet2-所有数据解析完成!sheet总行数:2,总列数:5
xxxx: sheet第1行数据:{"0":"正常情况1","1":"正常情况1","2":"正常情况1","3":"正常情况1","4":"正常情况1"}
xxxx: sheet第2行数据:{"0":"表头1","1":"表头2","2":"表头3","3":"表头4","4":"表头5"}
xxxx: =============================================================
xxxx: =============================================================
xxxx: sheet3-所有数据解析完成!sheet总行数:5,总列数:5
xxxx: sheet第1行数据:{"0":"正常情况1","1":"正常情况1","2":"正常情况1","3":"正常情况1","4":"正常情况1"}
xxxx: sheet第2行数据:{"0":"表头1","1":"表头2","2":"表头3","3":"表头4","4":"表头5"}
xxxx: sheet第3行数据:{"0":"列头1"}
xxxx: sheet第4行数据:{"0":"列头2"}
xxxx: sheet第5行数据:{"0":"列头3"}
xxxx: =============================================================

2.5(★★★)带合并单元格的写入

ExcelCustomMergeHandler(处理单元格合并的handler)


/**
 * @author jupiter
 * @version 1.0
 * @description: TODO
 * @date 2023/9/16 23:01
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Slf4j
public class ExcelCustomMergeHandler implements CellWriteHandler {
    // 表格行数
    int rowCount;
    // 表格列数
    int colCount;
    // 用于存储表格的合并单元格的区域列表
    List<CellExtra> mergeAreaList = new ArrayList<>();
    
    /**  
    * @description: 在单元格被创建之前的处理
    * @author jupiter
    * @date: 2023/9/16 23:12 
    */ 
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        CellWriteHandler.super.beforeCellCreate(writeSheetHolder, writeTableHolder, row, head, columnIndex, relativeRowIndex, isHead);
    }

    /**
     * @description: 在单元格被创建之后的处理
     * @author jupiter
     * @date: 2023/9/16 23:12
     */
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        CellWriteHandler.super.afterCellCreate(writeSheetHolder, writeTableHolder, cell, head, relativeRowIndex, isHead);
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 获取当前的单元格
        Sheet sheet = writeSheetHolder.getSheet();

        // 设置单元格居中
        CellStyle cellStyle = cell.getCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        //log.info("当前处理的单元格序号:{},{}",cell.getRowIndex(),cell.getColumnIndex());

        // 在最后一个单元格处理单元格合并
        if(cell.getRowIndex()==rowCount-1&&cell.getColumnIndex()==colCount-1){
            for(CellExtra mergeArea:mergeAreaList){
                CellRangeAddress cellAddresses = new CellRangeAddress(mergeArea.getFirstRowIndex(),mergeArea.getLastRowIndex(),mergeArea.getFirstColumnIndex(),mergeArea.getLastColumnIndex());
                log.info("写入添加合并区域:{}", JSONUtil.toJsonStr(mergeArea));
                sheet.addMergedRegion(cellAddresses);
            }
        }
    }
}

test(这里为了避免构建数据直接衔接了2.4用的读取后的数据)

@Test
    public void testCustomWrite() throws FileNotFoundException {
        String pathName = "C:\\Users\\jupiter\\Desktop\\test.xlsx";
        // 创建excel读取reader
        ExcelReader excelReader = EasyExcel.read(pathName).extraRead(CellExtraTypeEnum.MERGE).ignoreEmptyRow(false).build();

        // 创建每个sheet的读取listener并执行读取
        List<ReadSheet> readSheets = excelReader.excelExecutor().sheetList();
        List<ExcelSheetDataReadListener> listenerList = new ArrayList<>(readSheets.size()); // 用于进行数据和合并单元格区域保存
        //读取多个sheet
        List<ReadSheet> sheetList = readSheets.stream().map(sheet -> {
            ExcelSheetDataReadListener listener = new ExcelSheetDataReadListener();
            ReadSheet readSheet = EasyExcel.readSheet(sheet.getSheetName()).registerReadListener(listener).build();
            listenerList.add(listener);
            return readSheet;
        }).collect(Collectors.toList());
        excelReader.read(sheetList);

        // 释放资源
        excelReader.finish();

        // 开始执行excel写入
        pathName = "C:\\Users\\jupiter\\Desktop\\testWrite.xlsx";

        // 创建excel写入writer
        ExcelWriter excelWriter = EasyExcel.write(pathName).build();

        // 写入多个sheetList
        for (int i = 0; i < sheetList.size(); i++) {
            // 单元格总行数
            int rowCount = listenerList.get(i).getRowCount();
            // 单元格总列数
            int colCount = listenerList.get(i).getColCount();
            // sheet的逐行数据
            List<Map<Integer, String>> lineDataList = listenerList.get(i).getLineDataList();
            // 需要合并的单元格区域
            List<CellExtra> mergeAreaList = listenerList.get(i).getMergeAreaList();
            // 处理单元格合并的handle
            ExcelCustomMergeHandler writeHandle = new ExcelCustomMergeHandler(rowCount,colCount,mergeAreaList);
            // 构建sheet写入对象
            WriteSheet writeSheet = EasyExcel.writerSheet(i,sheetList.get(i).getSheetName()).registerWriteHandler(writeHandle).build();
            // 执行sheet数据写入
            excelWriter.write(lineDataList,writeSheet);
        }

        // 释放资源
        excelWriter.finish();
    }

执行效果

  • test.xlsx

    • sheet1

    • sheet2

  • testWrite.xlsx

    • sheet1

    • sheet2

参考资料

  1. EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel (alibaba.com)
  2. EasyExcel全面教程快速上手_easeexcel_知春秋的博客-CSDN博客
  3. 解决EasyExcel工具读取Excel空数据行的问题_easyexcel空行导入问题_流沙QS的博客-CSDN博客
  4. EasyExcel导入(含表头验证+空白行读取)_easyexcel导入表头校验_MMO_的博客-CSDN博客
  5. 阿里的easyExcel_easyexcel aftercelldispose_一直想成为大神的菜鸟的博客-CSDN博客
  6. easyexcel导出中自定义合并单元格,通过重写AbstractRowWriteHandler_easyexcel合并单元格策略_阿莫西林的博客-CSDN博客
  7. EasyExcel导出自定义合并单元格的策略_我可能在扯淡的博客-CSDN博客
0

评论 (0)

打卡
取消