[移动开发]EasyExcel导出excel合并表头和数据

maven依赖

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

数据结构与element表格数据结构类似
多级表头使用children渲染
合并行或列通过cols和rows控制

关键代码

try (OutputStream out = new FileOutputStream(file)) {
EasyExcel.write(out).head(excelHead(tableVO.getTableHead())).sheet("Sheet1")
            .registerWriteHandler(new MyMergeStrategy(tableVO))//自定义合并单元格
            .registerWriteHandler(new CustomCellWriteUtil())//表头宽度自适应
            .doWrite(excelData(tableVO));
} catch (IOException e) {
    e.printStackTrace();
}

/**
* 创建表头，这里会自动合并表头
 *
 * @return
 */
private List<List<String>> excelHead(List<TableHeadVO> headVOS) {
    boolean needMerge = false;
    for (TableHeadVO headVO : headVOS) {
        if (CollectionUtils.isNotEmpty(headVO.getChildren())) {
            needMerge = true;
        }
    }
    List<List<String>> headList = new ArrayList<>();
    for (TableHeadVO headVO : headVOS) {
        if (CollectionUtils.isNotEmpty(headVO.getChildren())) {
            headVO.getChildren().forEach(child -> {
                List<String> head = new ArrayList<>();
                head.add(headVO.getLabel());
                head.add(child.getLabel());
                headList.add(head);
            });
        } else {
            List<String> head = new ArrayList<>();
            head.add(headVO.getLabel());
            if (needMerge) {
                head.add(headVO.getLabel());
            }
            headList.add(head);
        }
    }
    return headList;
}

创建数据

/**
 * 创建数据
 *
 * @return
 */
private List<List<Object>> excelData(TableVO tableVO) {
    int maxExcel = 32767;
    List<List<Object>> list = new ArrayList<>();
    if (Objects.isNull(tableVO) || CollectionUtils.isEmpty(tableVO.getTableData())) {
        return list;
    }
    List<TableHeadVO> heads = tableVO.getTableHead();
    for (Map<String, Object> map : tableVO.getTableData()) {
        List<Object> data = new ArrayList<>();
        for (TableHeadVO head : heads) {
            if (CollectionUtils.isNotEmpty(head.getChildren())) {
                head.getChildren().forEach(child -> {
                    Object value = map.get(child.getProp());
                    if ((value + "").length() >= maxExcel) {
                        data.add((value + "").substring(0, 255) + "字符过长");
                    } else {
                        data.add(value);
                    }
                });
            } else {
                Object value = map.get(head.getProp());
                if ((value + "").length() >= maxExcel) {
                    data.add((value + "").substring(0, 255) + "字符过长");
                } else {
                    data.add(value);
                }
            }
        }
        list.add(data);
    }
    return list;
}

表格数据

import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

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

@NoArgsConstructor
@AllArgsConstructor
@Data
public class TableVO {
    @ApiModelProperty("表头信息")
    private List<TableHeadVO> tableHead;
    @ApiModelProperty("表格数据信息")
    private List<Map<String, Object>> tableData;
    @ApiModelProperty("总数据")
    private long total;
    @ApiModelProperty("总页数")
    private long totalPage;
    @ApiModelProperty("当前页：从1开始")
    private long pageIndex;
    @ApiModelProperty("页大小")
    private long pageSize;

    public TableVO(List<TableHeadVO> tableHead, List<Map<String, Object>> tableData) {
        this.tableHead = tableHead;
        this.tableData = tableData;
    }

    public static TableVO of(PageResult result) {
        TableVO tableVO = new TableVO();
        tableVO.setPageIndex(result.getPageIndex());
        tableVO.setPageSize(result.getPageSize());
        tableVO.setTotal(result.getTotalCount());
        tableVO.setTotalPage(result.getTotalPage());
        return tableVO;
    }
}

表头对象

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Builder
@NoArgsConstructor
@AllArgsConstructor
@ApiModel("表头")
@Data
public class TableHeadVO {
    @ApiModelProperty("是否合并")
    private boolean merge = true;
    @ApiModelProperty("表格的字段")
    private String prop;
    @ApiModelProperty("表格字段名称")
    private String label;
    @ApiModelProperty("字段类型：input,textarea,number,radio,checkbox,time,date,rate,color,select,switch,slider,text,link,imgupload,fileupload,table,grid,report,divider")
    private String type;
    @ApiModelProperty("是否为导出excel字段")
    private Boolean excel;
    @ApiModelProperty("宽度")
    private String width;
    @ApiModelProperty("后台自定义")
    private Boolean custom = false;
    @ApiModelProperty("子表单")
    private List<TableHeadVO> children;

    public TableHeadVO(String prop, String label) {
        this.prop = prop;
        this.label = label;
        this.type = "input";
        this.excel = true;
        this.width = "auto";
    }
}

最效果

数据合并

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

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

@Slf4j
public class MyMergeStrategy extends AbstractMergeStrategy {
    //合并坐标集合
    private List<CellRangeAddress> cellRangeAddresses;

    public MyMergeStrategy(TableVO tableVO) {
        List<CellRangeAddress> list = new ArrayList<>();

        // 判断是否需要合并，需要合并的再构造合并的地址
        boolean needMerge = false;
        for (TableHeadVO headVO : tableVO.getTableHead()) {
            if (CollectionUtils.isNotEmpty(headVO.getChildren())) {
                needMerge = true;
            }
        }
        if (needMerge) {
            // 重新定义head
            List<TableHeadVO> heads = new ArrayList<>();
            for (TableHeadVO headVO : tableVO.getTableHead()) {
                if (CollectionUtils.isNotEmpty(headVO.getChildren())) {
                    for (TableHeadVO child : headVO.getChildren()) {
                        heads.add(child);
                    }
                } else {
                    heads.add(headVO);
                }
            }
            if (CollectionUtils.isNotEmpty(tableVO.getTableData())) {
                for (int i = 0; i < tableVO.getTableData().size(); i++) {
                    Map<String, Object> data = tableVO.getTableData().get(i);
                    System.out.println("第" + i + "行数据" + data);

                    for (int j = 0; j < heads.size(); j++) {
                        TableHeadVO head = heads.get(j);

                        int cols = (int) data.getOrDefault("cols", 1);
                        int rows = (int) data.getOrDefault("rows", 1);
                        // 判断有没有孩子，有孩子的不用合并，没孩子的都要合并，根据所占列数合并
                        if (rows > 1 && head.isMerge()) {
                            System.out.print(head.getLabel());
                            log.info("{}, {} , {}, {}", i + 1 + 1, i + 1 + rows, j, j);
                            // row 行，col列
                            // int firstRow, int lastRow, int firstCol, int lastCol
                            CellRangeAddress item = new CellRangeAddress(i + 1 + 1, i + 1 + rows, j, j);
                            list.add(item);
                        }
                    }
                    System.out.println();
                }
            }
        }

        this.cellRangeAddresses = list;
    }

    /**
     * 合并操作：对每个单元格执行！！！
     *
     * @param sheet            sheet对象
     * @param cell             当前单元格
     * @param head             表头对象
     * @param relativeRowIndex 相关行索引
     */
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        /*
         * 合并单元格
         *
         * 由于merge()方法会在写每个单元格(cell)时执行，因此需要保证合并策略只被添加一次。否则如果每个单元格都添加一次
         * 合并策略，则会造成重复合并。例如合并A2:A3,当cell为A2时，合并A2:A3，但是当cell为A3时，又要合并A2:A3，而此时
         * 的A2已经是之前的A2和A3合并后的结果了。
         * 由于此处的表头占了两行，因此数据单元格是从(2, 0)开始的，所以就对这个单元格(cell.getRowIndex() == 2 && cell.getColumnIndex() == 0)
         * 添加一次合并策略就可以了。如果表头只有一行，则判断条件改为「cell.getRowIndex() == 1 && cell.getColumnIndex() == 0」就可以了。
         */
        if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
            for (CellRangeAddress item : cellRangeAddresses) {
                sheet.addMergedRegion(item);
            }
        }

        /*
         * 如果不作判断，可以使用addMergedRegionUnsafe()方法，
         * 这样生成的Excel文件可以打开，只是打开时会提示内容有问题，修复后可以打开
         */
        // for (CellRangeAddress item : cellRangeAddresses) {
        //     sheet.addMergedRegionUnsafe(item);
        // }
    }
}

列宽自适应

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel自适应列宽 拉过去就能直接套用自动适应列宽
 */
public class CustomCellWriteUtil extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public CustomCellWriteUtil() {
    }

    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead;
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null || maxColumnWidthMap.isEmpty()) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, true);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }
                Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), (int) ((columnWidth + 0.72) * 256));
                }
            }
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

参考文献

easyexcel（十一）：easyexcel动态表头，表头合并
EasyExcel 动态表头 + 数据单元格合并

创作挑战赛

新人创作奖励来咯，坚持创作打卡瓜分现金大奖