复杂表头excel导出（一对多）

偏执的太偏执、 2023-10-11 20:46 39阅读 0赞

## 项目场景：复杂表头excel导出 ##

项目场景：业务需求，列表套列表数据，在最外层导出两个列表中的数据（一对多）

--------------------

## 使用easyExcel导出复杂表头信息（效果图如下） ##

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

代码如下：  
放在Util包下即可

package com.risen.app.util;
    
    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 lombok.Data;
    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导出合并单元格策略
     * @author:YJ
     **/
    @Data
    public class MergeUtil implements CellWriteHandler {
        
        private int[] mergeColumnIndex;
        private int mergeRowIndex;
    
        public MergeUtil() {
        
        }
    
        public MergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
        
            this.mergeRowIndex = mergeRowIndex;
            this.mergeColumnIndex = mergeColumnIndex;
        }
    
        @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, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        
    
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        
            // 当前行
            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;
                    }
                }
            }
        }
    
       
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        
            // 获取当前行的当前列和上一行的当前列列数据，通过上一行数据是否相同进行合并
            Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
            Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
            Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
    
            // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
            if (curData.equals(preData)) {
        
                Sheet sheet = writeSheetHolder.getSheet();
                List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
                boolean isMerged = false;
                for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
        
                    CellRangeAddress cellRangeAddr = mergedRegions.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);
                }
            }
        }
    }

--------------------

## 在接口中使用Util： ##

//内容策略
                    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    
                    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                    //设置 垂直居中
                    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                    contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
                    
                    response.setContentType("application/vnd.ms-excel;charset=utf-8");
                    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                    EasyExcel.write(response.getOutputStream())
                            .head(header())
                            // 合并策略：合并相同数据的行。第一个参数表示从哪一行开始进行合并，由于表头占了两行，因此从第2行开始（索引从0开始）
                            // 第二个参数是指定哪些列要进行合并
                            .registerWriteHandler(new MergeUtil(2, new int[]{
        0, 1, 2, 3}))
                            //设置列宽
                            .registerWriteHandler(new SimpleColumnWidthStyleStrategy(22))
                            //设置内容样式（垂直居中）
                            .registerWriteHandler(horizontalCellStyleStrategy)
                            // 注意：需要先调用registerWriteHandler()再调用sheet()方法才能使合并策略生效！！！
                            .sheet("试运行记录")
                            //数据源 dataList(List<List<Object>>)双层list集合
                            .doWrite(dataList);

下面这个是表头，在自己的接口中引用的内部方法，相同的表头需要合并就设置一样的名字即可，表数据合并策略已经在Util中做过处理

//导出表头
        private List<List<String>> header() {
        
            List<List<String>> headers = new ArrayList<>();
            headers.add(Arrays.asList("试运行日期", "试运行日期"));
            headers.add(Arrays.asList("试运行基本情况", "试运行基本情况"));
            headers.add(Arrays.asList("操作人", "操作人"));
            headers.add(Arrays.asList("更新时间", "更新时间"));
            headers.add(Arrays.asList("问题记录", "问题描述"));
            headers.add(Arrays.asList("问题记录", "提出人 "));
            headers.add(Arrays.asList("问题记录", "问题状态"));
            headers.add(Arrays.asList("问题记录", "解决时间"));
            headers.add(Arrays.asList("问题记录", "解决人"));
            headers.add(Arrays.asList("问题记录", "解决方法"));
            return headers;
        }

[bf0561879bca4f96b167e920669e15d2.png]: https://img-blog.csdnimg.cn/bf0561879bca4f96b167e920669e15d2.png