EasyExcel导出合并单元格

作者: 小波同学 | 来源:发表于2023-03-30 00:42 被阅读0次

前言

功能展示

功能类似这样：

一、为什么选择Alibaba EasyExcel

优势：

EasyExcel 重写了poi对07版Excel的解析，并且再大的excel也不会出现内存溢出

导入EasyExcel依赖

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

二、easyexcel 注解合并单元格

@ContentLoopMerge：标注在字段上

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ContentLoopMerge {

    /**
     * 合并行
     *
     * @return
     */
    int eachRow() default 1;

    /**
     * 合并列
     *
     * @return
     */
    int columnExtend() default 1;
}

OnceAbsoluteMerge：标注在类上

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface OnceAbsoluteMerge {

    /**
     * 初始行
     *
     * @return
     */
    int firstRowIndex() default -1;

    /**
     * 最后一行
     *
     * @return
     */
    int lastRowIndex() default -1;

    /**
     * 初始列
     *
     * @return
     */
    int firstColumnIndex() default -1;

    /**
     * 最后一列
     *
     * @return
     */
    int lastColumnIndex() default -1;
}

2.1 @ContentLoopMerge示例

@Data
public class Demo implements Serializable {

    @ExcelProperty(value = "商户名称", index = 0)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String appName;

    @ExcelProperty(value = "城市名称", index = 1)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    @ContentLoopMerge(eachRow = 2, columnExtend = 3)
    private String cityName;

    @ExcelProperty(value = "区域名称", index = 2)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String regionName;

    @ExcelProperty(value = "商圈名称", index = 3)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String businessAreaName;

    @ExcelProperty(value = "楼盘名称", index = 4)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String gardenName;

    @ExcelProperty(value = "楼栋名称", index = 5)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String buildingName;

    @ExcelProperty(value = "单元名称", index = 6)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String unitName;

    @ExcelProperty(value = "价格", index = 7)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private Integer price;

}

2.2 @OnceAbsoluteMerge示例

@Data
@OnceAbsoluteMerge(firstRowIndex = 1, lastRowIndex = 3 , firstColumnIndex = 1 , lastColumnIndex = 3)
public class Demo implements Serializable {

    @ExcelProperty(value = "商户名称", index = 0)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String appName;

    @ExcelProperty(value = "城市名称", index = 1)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String cityName;

    @ExcelProperty(value = "区域名称", index = 2)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String regionName;

    @ExcelProperty(value = "商圈名称", index = 3)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String businessAreaName;

    @ExcelProperty(value = "楼盘名称", index = 4)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String gardenName;

    @ExcelProperty(value = "楼栋名称", index = 5)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String buildingName;

    @ExcelProperty(value = "单元名称", index = 6)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String unitName;

    @ExcelProperty(value = "价格", index = 7)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private Integer price;

}

注意：

@ContentLoopMerge(eachRow = 2, columnExtend = 3) 可以合并单元格，但是他是按指定行数和指定列数去合并，并不能实现内容相同的合并。
@OnceAbsoluteMerge(firstRowIndex = 1, lastRowIndex = 3 , firstColumnIndex = 1 , lastColumnIndex = 3) 可以合并单元格，但是他是按指定行数和指定列数去合并，并不能实现内容相同的合并。
@ExcelProperty(value = {"学生信息","备注"}，能实现多个标题，但标题是固定的，不是动态的。

三、自定义改造

3.1 导出的实体类，也就是表头

@Data
public class Demo implements Serializable {

    @ExcelProperty(value = "商户名称", index = 0)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String appName;

    @ExcelProperty(value = "城市名称", index = 1)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String cityName;

    @ExcelProperty(value = "区域名称", index = 2)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String regionName;

    @ExcelProperty(value = "商圈名称", index = 3)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String businessAreaName;

    @ExcelProperty(value = "楼盘名称", index = 4)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String gardenName;

    @ExcelProperty(value = "楼栋名称", index = 5)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String buildingName;

    @ExcelProperty(value = "单元名称", index = 6)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private String unitName;

    @ExcelProperty(value = "价格", index = 7)
    @ColumnWidth(15)
    @ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
    private Integer price;

}

3.2 行合并工具类

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
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.List;

/**
 * @Author: huangyibo
 * @Date: 2023/3/30 9:45
 * @Description: Excel单元格行合并处理策略
 */

public class ExcelFillCellRowMergeStrategy implements CellWriteHandler {

    //数组存放这一行需要合并那几列  [0,1,2] 在这mergeRowIndex行中合并 0、1、2列
    private int[] mergeColumnIndex;

    // 存放需要向上合并的行
    private int mergeRowIndex;

    // 不要合并的行
    private Integer noMergeRowIndex;

    public ExcelFillCellRowMergeStrategy() {
    }

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

    public ExcelFillCellRowMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, Integer noMergeRowIndex) {
        this.mergeColumnIndex = mergeColumnIndex;
        this.mergeRowIndex = mergeRowIndex;
        this.noMergeRowIndex = noMergeRowIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        int curRowIndex = cell.getRowIndex();
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     * .
     * @param writeSheetHolder writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Row  preRow = cell.getSheet().getRow(curRowIndex - 1);
        if (preRow == null) {
            // 当获取不到上一行数据时，使用缓存sheet中数据
            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell preCell = preRow.getCell(curColIndex);
        Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        //不需要合并的列直接跳出
        if ((noMergeRowIndex != null) && noMergeRowIndex == (curRowIndex - 1)){
            return;
        }
        // 将当前单元格数据与上一个单元格数据比较
        boolean dataBool = preData.equals(curData);

        //此处需要注意：所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并
        boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        if (dataBool && equals) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

3.3 列合并的工具类

@Data
@AllArgsConstructor
public class CellLineRange {

    /**
     * 起始列
     */
    private int firstCol;

    /**
     * 结束列
     */
    private int lastCol;
}

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
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.List;

/**
 * @Author: huangyibo
 * @Date: 2023/3/30 10:30
 * @Description: Excel单元格列合并处理策略
 */

public class ExcelFillCelColumnMergeStrategy implements CellWriteHandler {

    //自定义合并单元格的列 如果想合并 第4列和第5例 、第6列和第7例： [CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
    private List<CellLineRange> cellLineRangeList;

    //自定义合并单元格的开始的行  一般来说填表头行高0 表示从表头下每列开始合并 ：如表头行高位为3则 int mergeRowIndex = 2  ;
    private int mergeRowIndex;

    public ExcelFillCelColumnMergeStrategy(int mergeRowIndex, List<CellLineRange> cellLineRangeList) {
        this.mergeRowIndex=mergeRowIndex;
        this.cellLineRangeList=cellLineRangeList;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean aBoolean) {

        //当前单元格的行数
        int curRowIndex = cell.getRowIndex();
        // 当前单元格的列数
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < cellLineRangeList.size(); i++) {
                if (curColIndex > cellLineRangeList.get(i).getFirstCol()&&curColIndex<=cellLineRangeList.get(i).getLastCol()) {
                    //单元格数据处理
                    mergeWithLeftLine(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * @description 当前单元格向左合并
     */
    private void mergeWithLeftLine(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //当前单元格中数据
        Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        //获取当前单元格的左面一个单元格
        Cell leftCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
        //获取当前单元格的左面一个单元格中的数据
        Object leftData = leftCell.getCellType() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue();

        // 将当前单元格数据与左侧一个单元格数据比较
        if (leftData.equals(curData)) {
            //获取当前sheet页
            Sheet sheet = writeSheetHolder.getSheet();
            //得到所有的合并单元格
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            //是否合并
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                //CellRangeAddress POI合并单元格
                //CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
                //例子：CellRangeAddress（2, 6000, 3, 3）；
                //第2行起 第6000行终止 第3列开始 第3列结束。
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // cellRangeAddr.isInRange(int rowInd, int colInd)确定给定坐标是否在此范围的范围内。
                // 若左侧一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex, curColIndex - 1)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastColumn(curColIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若左侧一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex , curRowIndex, curColIndex- 1, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

3.4 EasyExcelUtil 工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.kfang.web.price.manager.excel.ImportDataAbstractListener;
import com.kfang.web.price.manager.excel.ImportDataReturnVo;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Base64;
import java.util.List;

/**
 * @author: huangyibo
 * @Date: 2022/3/24 20:11
 * @Description: EasyExcelUtil 工具类
 */

public class EasyExcelUtil {

    /**
     * 下载文件时，针对不同浏览器，进行附件名的编码
     *
     * @param filename 下载文件名
     *
     * @param agent 客户端浏览器
     *
     * @return 编码后的下载附件名
     * @throws IOException
     */
    public static String encodeDownloadFilename(String filename, String agent)
            throws IOException {
        if (agent.contains("Firefox")) { // 火狐浏览器
            filename = "=?UTF-8?B?"
                    + Base64.getEncoder().encode(filename.getBytes("utf-8"))
                    + "?=";
            filename = filename.replaceAll("\r\n", "");
        } else { // IE及其他浏览器
            filename = URLEncoder.encode(filename, "utf-8");
            filename = filename.replace("+"," ");
        }
        return filename;
    }


    /**
     * 设置导出Excel格式
     * @param response
     * @param request
     * @param filename
     * @throws IOException
     */
    public static void setExportExcelFormat(HttpServletResponse response, HttpServletRequest request, String filename) throws IOException {
        String agent = request.getHeader("user-agent");//获得游览器
        filename = filename + ".xlsx";
        String downloadFilename = encodeDownloadFilename(filename, agent); //使用工具类解决文件乱码的问题
        response.setCharacterEncoding("UTF-8");
        // 设置响应输出的头类型
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename="+downloadFilename);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
    }


    /**
     * 读取导入excel数据(单sheet页读取)
     * @param file              上传文件
     * @param clazz             导入实体类型
     * @param excelListener     数据导入监听器
     * @return ImportDataReturnVo<?>
     * @throws IOException
     */
    public static ImportDataReturnVo<?> read(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener) throws IOException {
        EasyExcel.read(file.getInputStream(), clazz, excelListener).sheet().doRead();
        return excelListener.getResult();
    }


    /**
     * 读取导入excel数据(读取全部sheet页)
     * @param file              上传文件
     * @param clazz             导入实体类型
     * @param excelListener     数据导入监听器
     * @return ImportDataReturnVo<?>
     * @throws IOException
     */
    public static ImportDataReturnVo<?> readAll(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener) throws IOException {
        EasyExcel.read(file.getInputStream(), clazz, excelListener).doReadAll();
        return excelListener.getResult();
    }


    /**
     * 读取导入excel数据(读取指定sheet页)
     * @param file              上传文件
     * @param clazz             导入实体类型
     * @param excelListener     数据导入监听器
     * @param sheetNumList      指定的sheetNum
     * @return ImportDataReturnVo<?>
     * @throws IOException
     */
    public static ImportDataReturnVo<?> readSheet(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener, List<Integer> sheetNumList) throws IOException {
        ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
        // 读取不同的数据, 需要使用不同的head 和Listener
        List<ReadSheet> readSheetList = new ArrayList<>();
        sheetNumList.forEach(sheetNum -> {
            // readSheet参数设置读取sheet的序号
            ReadSheet readSheet = EasyExcel.readSheet(sheetNum).head(clazz).registerReadListener(excelListener).build();
            readSheetList.add(readSheet);
        });
        // 这里注意 一定要把readSheetList一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
        excelReader.read(readSheetList);
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
        return excelListener.getResult();
    }

    /**
     * 将数据写入Excel并写入流中
     * @param outputStream  输出流
     * @param clazz         导出实体类型
     * @param list          导出数据集合
     * @param sheetName     sheet页名称
     */
    public static void writeExcel2Stream(ByteArrayOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName){
        EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(list);
    }

    /**
     * 单sheet页写入Excel
     * @param outputStream  输出流
     * @param clazz         导出实体类型
     * @param list          导出数据集合
     * @param sheetName     sheet页名称
     */
    public static void writeExcel(ServletOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName) {
        EasyExcel.write(outputStream, clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet(sheetName)
                .doWrite(list);
    }

    /**
     * 单sheet页写入Excel-合并策略
     * @param outputStream          输出流
     * @param clazz                 导出实体类型
     * @param list                  导出数据集合
     * @param sheetName             sheet页名称
     * @param cellWriteHandlerList  合并策略集合
     */
    public static void writeExcelMerge(ServletOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName, List<CellWriteHandler> cellWriteHandlerList) {
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream, clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
        if(!CollectionUtils.isEmpty(cellWriteHandlerList)){
            for (CellWriteHandler cellWriteHandler : cellWriteHandlerList) {
                excelWriterBuilder.registerWriteHandler(cellWriteHandler);
            }
        }
        excelWriterBuilder.sheet(sheetName).doWrite(list);
    }

    /**
     * 多sheet页写入Excel
     * @param excelWriter   excelWriter写出对象
     * @param clazz         导出实体类型
     * @param list          导出数据集合
     * @param num           sheet页码
     * @param sheetName     sheet页名称
     */
    public static void writerSheetExcel(ExcelWriter excelWriter, Class<?> clazz, List<?> list, Integer num, String sheetName) {
        WriteSheet writeSheet = EasyExcel.writerSheet(num, sheetName).head(clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
        excelWriter.write(list, writeSheet);
    }


    /**
     * 多sheet页写入Excel-合并策略
     * @param excelWriter           excelWriter写出对象
     * @param clazz                 导出实体类型
     * @param list                  导出数据集合
     * @param num                   sheet页码
     * @param sheetName             sheet页名称
     * @param cellWriteHandlerList  合并策略集合
     */
    public static void writerSheetExcelMerge(ExcelWriter excelWriter, Class<?> clazz, List<?> list, Integer num, String sheetName, List<CellWriteHandler> cellWriteHandlerList) {
        ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.writerSheet(num, sheetName).head(clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
        if(!CollectionUtils.isEmpty(cellWriteHandlerList)){
            for (CellWriteHandler cellWriteHandler : cellWriteHandlerList) {
                excelWriterSheetBuilder.registerWriteHandler(cellWriteHandler);
            }
        }
        WriteSheet writeSheet = excelWriterSheetBuilder.build();
        excelWriter.write(list, writeSheet);
    }
}

3.5 单sheet页写入Excel-合并策略

ExcelFillCellRowMergeStrategy和ExcelFillCelColumnMergeStrategy可以单独使用，也可以组合使用，这里演示组合使用

@Lazy
@RestController
@RequestMapping(value = "/demo", produces = {"application/json;charset=UTF-8"})
@Slf4j
@Api(tags = ApiSwaggerTags.DICT_ROOM_RULE_APPRAISAL,value = "规则管理")
public class DictRoomRuleController extends WebBaseController {

    @PostMapping(value = "/exportMerge")
    @ApiOperation(value = "导出测试信息")
    @ApiResponses({
            @ApiResponse(message = "导出测试信息", code = 200, response = Boolean.class)
    })
    public void exportMerge(HttpServletResponse response, HttpServletRequest request) {
        try {
            ServletOutputStream outputStream = response.getOutputStream();

            List<Demo> list = new ArrayList<>();
            buildData(list);

            EasyExcelUtil.setExportExcelFormat(response, request,"测试数据");

            //需要合并的列
            int[] mergeColumnIndex = {0, 1, 2, 3, 4};

            //设置第几行开始合并
            int mergeRowIndex = 1;

            // Excel单元格行合并处理策略
            ExcelFillCellRowMergeStrategy rowMergeStrategy = new ExcelFillCellRowMergeStrategy(mergeRowIndex, mergeColumnIndex);

            //列合并的工具实体类
            ArrayList<CellLineRange> cellLineRanges=new ArrayList<>();
            cellLineRanges.add(new CellLineRange(0,2));
            // Excel单元格列合并处理策略
            ExcelFillCelColumnMergeStrategy celColumnMergeStrategy = new ExcelFillCelColumnMergeStrategy(0, cellLineRanges);

            List<CellWriteHandler> cellWriteHandlerList = Stream.of(celColumnMergeStrategy, rowMergeStrategy).collect(Collectors.toList());

            EasyExcelUtil.writeExcelMerge(outputStream, Demo.class, list, "测试数据", cellWriteHandlerList);

            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            log.error("楼盘估价数据导出excel Exception", e);
        }
    }

    /**
     * 构建数据
     * @param list
     */
    private void buildData (List<Demo> list){
        for (int i = 0; i < 5; i++) {
            Demo demo = new Demo();
            demo.setAppName("app1");
            demo.setCityName("app1");
            demo.setRegionName("app1");
            demo.setBusinessAreaName("深大");
            demo.setGardenName("大冲国际中心");
            demo.setBuildingName("一期");
            demo.setUnitName("A座");
            demo.setPrice(100000 + i);
            list.add(demo);
        }

        for (int i = 0; i < 2; i++) {
            Demo demo = new Demo();
            demo.setAppName("app2");
            demo.setCityName("深圳");
            demo.setRegionName("南山区");
            demo.setBusinessAreaName("前海湾");
            demo.setGardenName("前海中心大厦");
            demo.setBuildingName("一期");
            demo.setUnitName("B座");
            demo.setPrice(100000 + i);
            list.add(demo);
        }

        for (int i = 0; i < 2; i++) {
            Demo demo = new Demo();
            demo.setAppName("深圳");
            demo.setCityName("深圳");
            demo.setRegionName("深圳");
            demo.setBusinessAreaName("后海");
            demo.setGardenName("中国华润大厦");
            demo.setBuildingName("中国华润大厦");
            demo.setUnitName("A座");
            demo.setPrice(100000 + i);
            list.add(demo);
        }

        for (int i = 0; i < 1; i++) {
            Demo demo = new Demo();
            demo.setAppName("app3");
            demo.setCityName("深圳");
            demo.setRegionName("宝安区");
            demo.setBusinessAreaName("壹方城");
            demo.setGardenName("壹方中心");
            demo.setBuildingName("一期");
            demo.setUnitName("A座");
            demo.setPrice(100000 + i);
            list.add(demo);
        }
    }
}

3.6 多sheet页写入Excel-合并策略

ExcelFillCellRowMergeStrategy和ExcelFillCelColumnMergeStrategy可以单独使用，也可以组合使用，这里演示组合使用

@Lazy
@RestController
@RequestMapping(value = "/demo", produces = {"application/json;charset=UTF-8"})
@Slf4j
@Api(tags = ApiSwaggerTags.DICT_ROOM_RULE_APPRAISAL,value = "规则管理")
public class DictRoomRuleController extends WebBaseController {

    @PostMapping(value = "/exportMergeTest")
    @ApiOperation(value = "导出测试信息")
    @ApiResponses({
            @ApiResponse(message = "导出测试信息", code = 200, response = Boolean.class)
    })
    public void exportMergeTest(HttpServletResponse response, HttpServletRequest request) {
        try {
            ServletOutputStream outputStream = response.getOutputStream();

            List<List<Demo>> list = new ArrayList<>();
            buildListData(list);

            EasyExcelUtil.setExportExcelFormat(response, request,"KF楼盘估价");

            //需要合并的列
            int[] mergeColumnIndex = {0, 1, 2, 3, 4};

            //设置第几行开始合并
            int mergeRowIndex = 1;

            // Excel单元格行合并处理策略
            ExcelFillCellRowMergeStrategy rowMergeStrategy = new ExcelFillCellRowMergeStrategy(mergeRowIndex, mergeColumnIndex);

            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            for (int i = 0; i < list.size(); i++) {
                //sheet页码
                int num = 1 + 1;

                //列合并的工具实体类
                ArrayList<CellLineRange> cellLineRanges=new ArrayList<>();
                cellLineRanges.add(new CellLineRange(0,2));

                // Excel单元格列合并处理策略
                ExcelFillCelColumnMergeStrategy celColumnMergeStrategy = new ExcelFillCelColumnMergeStrategy(0, cellLineRanges);
                List<CellWriteHandler> cellWriteHandlerList = Stream.of(celColumnMergeStrategy, rowMergeStrategy).collect(Collectors.toList());
                EasyExcelUtil.writerSheetExcelMerge(excelWriter, Demo.class, list.get(i), num, "楼盘估价"+num, cellWriteHandlerList);
            }

            //刷新流
            excelWriter.finish();

            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            log.error("楼盘估价数据导出excel Exception", e);
        }
    }

    /**
     * 构建数据
     * @param list
     */
    private void buildListData (List<List<Demo>> list){
        List<Demo> list1 = new ArrayList<>();
        List<Demo> list2 = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            Demo demo = new Demo();
            demo.setAppName("app1");
            demo.setCityName("app1");
            demo.setRegionName("app1");
            demo.setBusinessAreaName("深大");
            demo.setGardenName("大冲国际中心");
            demo.setBuildingName("一期");
            demo.setUnitName("A座");
            demo.setPrice(100000 + i);
            list1.add(demo);
        }

        for (int i = 0; i < 2; i++) {
            Demo demo = new Demo();
            demo.setAppName("app2");
            demo.setCityName("深圳");
            demo.setRegionName("南山区");
            demo.setBusinessAreaName("前海湾");
            demo.setGardenName("前海中心大厦");
            demo.setBuildingName("一期");
            demo.setUnitName("B座");
            demo.setPrice(100000 + i);
            list1.add(demo);
        }

        for (int i = 0; i < 2; i++) {
            Demo demo = new Demo();
            demo.setAppName("深圳");
            demo.setCityName("深圳");
            demo.setRegionName("深圳");
            demo.setBusinessAreaName("后海");
            demo.setGardenName("中国华润大厦");
            demo.setBuildingName("中国华润大厦");
            demo.setUnitName("A座");
            demo.setPrice(100000 + i);
            list2.add(demo);
        }

        for (int i = 0; i < 1; i++) {
            Demo demo = new Demo();
            demo.setAppName("app3");
            demo.setCityName("深圳");
            demo.setRegionName("宝安区");
            demo.setBusinessAreaName("壹方城");
            demo.setGardenName("壹方中心");
            demo.setBuildingName("一期");
            demo.setUnitName("A座");
            demo.setPrice(100000 + i);
            list2.add(demo);
        }
        list.add(list1);
        list.add(list2);
    }
}

3.7 动态标题

生成动态标题

//生成动态标题
private List<List<String>> getHeaders(String dynamicTitle) {
    List<List<String>> headers=new ArrayList<>();
    List<String> schoolHead=new ArrayList<>();
    schoolHead.add(dynamicTitle);
    schoolHead.add("学校");
    List<String> nameHead=new ArrayList<>();
    nameHead.add(dynamicTitle);
    nameHead.add("姓名");
    List<String> sexHead=new ArrayList<>();
    sexHead.add(dynamicTitle);
    sexHead.add("性别");
    List<String> ageHead=new ArrayList<>();
    ageHead.add(dynamicTitle);
    ageHead.add("年龄");
    List<String> cityHead=new ArrayList<>();
    cityHead.add(dynamicTitle);
    cityHead.add("城市");
    List<String> remarksHead=new ArrayList<>();
    remarksHead.add(dynamicTitle);
    remarksHead.add("备注");
    headers.add(schoolHead);
    headers.add(nameHead);
    headers.add(sexHead);
    headers.add(ageHead);
    headers.add(cityHead);
    headers.add(remarksHead);
    return headers;
}

设置动态标题

//设置动态标题
List<List<String>> headers = this.getHeaders("学生信息" + dynamicTitle);

EasyExcel.write(outputStream, clazz)
        .sheet(sheetName)
        .head(headers)
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .doWrite(list);