[Java知识库]EasyExcel的使用

1、什么是EasyExcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目

它有以下优势：

• 注解式自定义操作
• 输入输出简单，提供输入输出过程的接口
• 支持一定程度的单元格合并等灵活化操作
• 可以基于对象的方式操作Excel
• 节省内存

? ? EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析

二、基本使用

1、简单导出

GoodsSimpleVo.java

package priv.cwr.excel.vo;

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

import java.math.BigDecimal;

/**
 * @Description 简单商品Vo
 * @Author CWR
 * @Date 2022/3/24 15:33
 */
@Data
@ExcelIgnoreUnannotated
public class GoodsSimpleVo {

    /**
     * 商品id
     */
    @ExcelProperty("ID")
    private Long id;

    /**
     * 商品名称
     */
    @ExcelProperty("商品名称")
    private String goodsName;

    /**
     * 品牌
     */
    @ExcelProperty("品牌")
    private String brand;

    /**
     * 单位
     */
    @ExcelProperty("单位")
    private String unit;

    /**
     * 价格
     */
    @ExcelProperty("价格")
    private BigDecimal price;

    /**
     * 是否包邮
     */
    @ExcelProperty("是否包邮")
    private String freeShipping;

    /**
     * 其他信息
     */
    private String other;

    public GoodsSimpleVo() {

    }

    public GoodsSimpleVo(Long id, String goodsName, String brand, String unit, BigDecimal price, String freeShipping, String other) {
        this.id = id;
        this.goodsName = goodsName;
        this.brand = brand;
        this.unit = unit;
        this.price = price;
        this.freeShipping = freeShipping;
        this.other = other;
    }
}

 /**
     * 简单导出
     *
     * @param response
     */
    @GetMapping("/export/simple")
    public void exportSimple(HttpServletResponse response) throws IOException {
        List<GoodsSimpleVo> list = new ArrayList<>();
        list.add(new GoodsSimpleVo(1001L, "三相之力", "神话", "把", BigDecimal.valueOf(3433.5), "包邮", "AD"));
        list.add(new GoodsSimpleVo(1002L, "渴血战斧", "神话", "把", BigDecimal.valueOf(3433.69), "包邮", "吸血"));
        list.add(new GoodsSimpleVo(1003L, "卢登的回响", "神话", "把", BigDecimal.valueOf(3400.00), "包邮", "AP"));
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 保证下载到本地文件名不乱码
        String fileName = URLEncoder.encode("简单导出", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcelFactory.write(response.getOutputStream(), GoodsSimpleVo.class)
                .sheet("sheet")
                .doWrite(list);
    }

结果如下

2、复杂导出

这里的复杂导出主要是指的需要合并单元格

GoodsComplexVo.java

package priv.cwr.excel.vo;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;

/**
 * @Description 商品复杂Vo
 * @Author CWR
 * @Date 2022/3/22 14:40
 */
@Data
@HeadRowHeight(30)
@NoArgsConstructor
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class GoodsComplexVo {
    /**
     * 商品spu
     */
    @ColumnWidth(15)
    @ExcelProperty("商品SPU")
    private Integer commonId;

    /**
     * 商品名称
     */
    @ColumnWidth(40)
    @ExcelProperty("商品名称")
    private String goodsName;

    /**
     * 品牌
     */
    @ColumnWidth(10)
    @ExcelProperty("品牌")
    private String brand;

    /**
     * 品质
     */
    @ColumnWidth(10)
    @ExcelProperty("品质")
    private String quality;

    /**
     * 原库存
     */
    @ColumnWidth(10)
    @ExcelProperty("原库存")
    private Integer goodsStorage;

    /**
     * 原阶梯数量
     */
    @ColumnWidth(15)
    @ExcelProperty("原阶梯数量")
    private Integer batchNum;

    /**
     * 原价格
     */
    @ColumnWidth(10)
    @ExcelProperty("原价格")
    private BigDecimal batchPrice;
}

CellMergeStrategy.java

package priv.cwr.excel.easyexcel;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.Arrays;
import java.util.List;

/**
 * @Description: Excel导出单元格合并策略
 * @Author: Very
 * @Date: 2022/3/23 16:08
 */
public class CellMergeStrategy implements CellWriteHandler {

    /**
     * 同组列索引,必须全部列相同才会向上合并(从0开始)
     */
    private int[] groupColumnIndex;

    /**
     * 合并列索引(从0开始)
     */
    private int[] mergeColumnIndex;

    /**
     * 合并起始行索引
     */
    private int mergeRowIndex;

    public CellMergeStrategy(int[] groupColumnIndex, int[] mergeColumnIndex, int mergeRowIndex) {
        this.groupColumnIndex = groupColumnIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.mergeRowIndex = mergeRowIndex;
    }

    /**
     * 在创建单元格前调用
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param row
     * @param head
     * @param integer
     * @param integer1
     * @param aBoolean
     */
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    }

    /**
     * 在创建单元格后调用
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cell
     * @param head
     * @param integer
     * @param aBoolean
     */
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    /**
     * 在单元格数据转换后调用
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cellData
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }

    /**
     * 在单元上的所有操作完成后调用(可以对单元格进行任何操作)
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param list
     * @param cell
     * @param head
     * @param integer
     * @param aBoolean
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        // 如果当前行大于合并其实行则进行合并
        if (cell.getRowIndex() > mergeRowIndex) {
            for (int columnIndex : mergeColumnIndex) {
                if (cell.getColumnIndex() == columnIndex) {
                    mergeWithPreRow(writeSheetHolder.getSheet(), cell);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param sheet 当前sheet
     * @param cell  当前单元格
     */
    private void mergeWithPreRow(Sheet sheet, Cell cell) {
        // 当前单元格行、列索引
        int curRowIndex = cell.getRowIndex();
        int curColumnIndex = cell.getColumnIndex();
        // 标记是否需要和上一行合并
        boolean needMerge = true;
        for (int columnIndex : this.groupColumnIndex) {
            // 获取上一行指定单元格
            Cell preColumnCell = sheet.getRow(curRowIndex - 1).getCell(columnIndex);
            Object preColumnCellValue = preColumnCell.getCellTypeEnum() == CellType.STRING ? preColumnCell.getStringCellValue() : preColumnCell.getNumericCellValue();
            // 获取当前行指定单元格
            Cell curColumnCell = sheet.getRow(curRowIndex).getCell(columnIndex);
            Object curColumnCellValue = curColumnCell.getCellTypeEnum() == CellType.STRING ? curColumnCell.getStringCellValue() : curColumnCell.getNumericCellValue();
            // 只要有一列不相等则不向上合并
            if (!preColumnCellValue.equals(curColumnCellValue)) {
                needMerge = false;
                break;
            }
        }
        // 获取当前行的当前列的数据和上一行的当前列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColumnIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        //比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (needMerge && curData.equals(preData)) {
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
            // 标记当前单元格是否已被合并
            boolean isMerged = false;
            for (int i = 0; i < mergedRegions.size(); i++) {
                CellRangeAddress cellAddresses = mergedRegions.get(i);
                // 判断上一行单元格是否已经被合并，是则先移出原有的合并单元，再重新添加合并单元
                if (cellAddresses.isInRange(curRowIndex - 1, curColumnIndex)) {
                    sheet.removeMergedRegion(i);
                    cellAddresses.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellAddresses);
                    isMerged = true;
                    break;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellAddresses = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColumnIndex, curColumnIndex);
                sheet.addMergedRegion(cellAddresses);
            }
        }
    }
}

/**
     * 复杂导出(单元格合并)
     *
     * @param response
     */
    @GetMapping("/export/complex")
    public void exportComplex(HttpServletResponse response) throws IOException {
        List<GoodsComplexVo> list = new ArrayList<>();
        list.add(new GoodsComplexVo(1001, "三相之力", "艾欧尼亚", "神话", 100, 1, BigDecimal.valueOf(3433)));
        list.add(new GoodsComplexVo(1001, "三相之力", "艾欧尼亚", "神话", 100, 10, BigDecimal.valueOf(3000)));
        list.add(new GoodsComplexVo(1001, "三相之力", "艾欧尼亚", "神话", 100, 100, BigDecimal.valueOf(2800)));

        list.add(new GoodsComplexVo(1003, "渴血战斧", "艾欧尼亚", "传说", 46, 1, BigDecimal.valueOf(3200)));
        list.add(new GoodsComplexVo(1003, "渴血战斧", "艾欧尼亚", "传说", 46, 10, BigDecimal.valueOf(3150.58)));
        list.add(new GoodsComplexVo(1003, "渴血战斧", "艾欧尼亚", "传说", 46, 100, BigDecimal.valueOf(3000)));

        list.add(new GoodsComplexVo(1002, "卢登的回响", "弗雷尔卓德", "史诗", 150, 1, BigDecimal.valueOf(3300)));
        list.add(new GoodsComplexVo(1002, "卢登的回响", "弗雷尔卓德", "史诗", 150, 10, BigDecimal.valueOf(3228.89)));
        list.add(new GoodsComplexVo(1002, "卢登的回响", "弗雷尔卓德", "史诗", 150, 100, BigDecimal.valueOf(3158.66)));
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 保证下载到本地文件名不乱码
        String fileName = URLEncoder.encode("复杂导出", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcelFactory.write(response.getOutputStream(), GoodsComplexVo.class)
                .sheet("sheet")
                .registerWriteHandler(new CellMergeStrategy(new int[]{0}, new int[]{0, 1, 2, 3, 4}, 1))
                .doWrite(list);
    }

结果如下

3、简单导入

模拟网页导入Excel

GoodsImportObjectListener.java

package priv.cwr.excel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import priv.cwr.excel.vo.GoodsComplexVo;

import java.util.Map;

/**
 * @Description 商品导入(解析Object)
 * @Author CWR
 * @Date 2021/9/22 14:07
 */

public class GoodsImportObjectListener extends AnalysisEventListener<GoodsComplexVo> {

    private static final Logger LOGGER = LoggerFactory.getLogger(GoodsImportObjectListener.class);

    @Override
    public void invoke(GoodsComplexVo vo, AnalysisContext analysisContext) {
        LOGGER.info("解析第" + analysisContext.readRowHolder().getRowIndex() + "行数据:{}", vo);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        StringBuilder stringBuilder = new StringBuilder("所有数据解析完成 ");
    }
}

 /**
     * 导入数据(对象)
     *
     * @param multipartFile 导入excel
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "import/object", method = RequestMethod.POST)
    public void excelImportByObject(MultipartFile multipartFile) {
        // 解析Excel
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcelFactory.read(multipartFile.getInputStream(), GoodsComplexVo.class, new GoodsImportObjectListener()).build();
            ReadSheet readSheet = EasyExcelFactory.readSheet(0).build();
            excelReader.read(readSheet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (excelReader != null) {
                excelReader.finish();
            }
        }
    }