结合注解easyexcel合并单元格

清疚 2024-02-05 17:51 36阅读 0赞

EasyExcel导出合并单元格

前言  
功能展示  
功能类似这样：  
![在这里插入图片描述][4eeda39125a14375a50b86d048f3cc44.png]  
一、为什么选择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;
    
    }

![在这里插入图片描述][358a6152d77341849ba4d98690d1953c.png]  
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;
    
    }

![在这里插入图片描述][5f00a04fb6e94ad59f452427f4444b88.png]  
注意：  
@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;
    
    /**
     * @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);

![在这里插入图片描述][0d686e41b59b40f1ae6a3a72e02a3f6e.png]

[4eeda39125a14375a50b86d048f3cc44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b9e7e1bc94c34f36b04612f784181390.png
[358a6152d77341849ba4d98690d1953c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b0a4f08fa7934eb4a4c4b15cd1df06ca.png
[5f00a04fb6e94ad59f452427f4444b88.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/8b0691c82407486eb282bb809e24e92c.png
[0d686e41b59b40f1ae6a3a72e02a3f6e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/18b0db27d5a940fb9a25d811e0f352fa.png