Excel实现合并单元

Java中导出Excel实现合并单元

针对主表、明细表这种场景，目前仅支持两级。
仅需要封装为主表+明细即可，简单好用

效果：前两列根据第三列的高度自动合并单元格

下面代码中list为原始数据 AssetsMaterialTotalExportVO 是主表，包含明细列表List details，而导出的ArrayList assetsMaterialTotalExportVOS 中AssetsMaterialTotalExportVO是包括主表+明细字段

public FileRespVO materialStatisticsExportExcel(@ParamJson HashMap<String, Object> map) throws IOException {
        if (CollUtil.isEmpty(list)) throw new CenterException("没有要导出的数据");
        ArrayList<AssetsMaterialTotalExportVO> assetsMaterialTotalExportVOS = new ArrayList<>();
        for (AssetsMaterialStatisticsVO item : list) {
            AssetsMaterialTotalExportVO exportModel = new AssetsMaterialTotalExportVO();
            exportModel.setRegionName(item.getRegionName());
            exportModel.setTime(item.getTime());

            // 明细
            List<AssetsMaterialStatisticsExportVO> details = item.getDetails();
            if (CollectionUtil.isEmpty(details)) {
                assetsMaterialTotalExportVOS.add(exportModel);
                continue;
            }
            for (AssetsMaterialStatisticsExportVO detail : details) {
                AssetsMaterialTotalExportVO detailModel = BeanUtils.copyProperties(exportModel, AssetsMaterialTotalExportVO.class);
                detailModel.setMaterialType(detail.getMaterialType());
                detailModel.setTotalNum(detail.getTotalNum());
                detailModel.setAddNum(detail.getAddNum());
                detailModel.setUpdateNum(detail.getUpdateNum());
                detailModel.setInspectionTotalNum(detail.getInspectionTotalNum());
                assetsMaterialTotalExportVOS.add(detailModel);
            }


        }
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        // new ListMergeStrategy(list, 2)`表示前两列合并。
        EasyExcel.write(bos, AssetsMaterialTotalExportVO.class).registerWriteHandler(new ListMergeStrategy(list, 2)).registerWriteHandler(new CustomCellWriteHandler())
                .sheet().doWrite(assetsMaterialTotalExportVOS);
        FileRespVO fileRespVO = new FileRespVO();
        String fileName = "物资台账统计" + ".xlsx";
        fileRespVO.setFileName(fileName);
        fileRespVO.setFile(bos.toByteArray());
        return fileRespVO;
    }

AssetsMaterialTotalExportVO由主表＋明细 构成

@Data
public class AssetsMaterialTotalExportVO implements Serializable {
    // ====================主表=======================
    @ExcelProperty(value = "区域")
    private String regionName;
    @ExcelProperty(value = "时间")
    private String time;
    // ==================明细=========================
    @ExcelProperty(value = "物资类型")
    private String materialType;
    @ExcelProperty(value = "总数量")
    private int totalNum = 0;
    @ExcelProperty(value = "新增数量")
    private int addNum = 0;
    @ExcelProperty(value = "更换数量")
    private int updateNum = 0;
    ........


}

以下为工具类 直接复制即可：
ListMergeStrategy类

/**
 * 主从表的单元格合并策略(明细必须是List类型)
 * 
 * @author lansongtao
 * @Date 2020/09/04
 */
@Slf4j
public class ListMergeStrategy extends AbstractSheetWriteHandler {

    /** 数据集合 */
    private List<?> dataList;
    /** 主表字段数量（最后的子级不用传） */
    private Integer[] fieldNumArgs;
    /** 合并策略明细 */
    private List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();

    /**
     * 构造函数
     * 
     * @param dataList
     *            明细List必须指定泛型
     * @param fieldNumArgs
     *            主表字段数量（最后的子级不用传）
     */
    public ListMergeStrategy(List<?> dataList, Integer[] fieldNumArgs) {
        this.dataList = dataList;
        this.fieldNumArgs = fieldNumArgs;
        try {
            generateCellRange();
        } catch (Exception e) {
            log.error("设置单元格合并策略失败", e);
        }
    }

    /**
     * 构造函数
     *
     * @param dataList
     *            明细List必须指定泛型
     * @param fieldNum
     *            主表字段数量（最后的子级不用传）
     */
    public ListMergeStrategy(List<?> dataList, Integer fieldNum) {
        this.dataList = dataList;
        this.fieldNumArgs = new Integer[] {fieldNum};
        try {
            generateCellRange();
        } catch (Exception e) {
            log.error("设置单元格合并策略失败", e);
        }
    }

    /**
     * 根据集合中的子级List条数来合并单元格
     * 
     * @return
     */
    private void generateCellRange() throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        List<Field> detailFieldList = getListGetters();
        countDetailSize(this.dataList, detailFieldList, -1, 1);
    }

    private Integer countDetailSize(List<?> parentList, List<Field> detailFieldList, int forIndex, int rowBegin)
        throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        forIndex++;
        if (detailFieldList.size() <= forIndex) {
            return parentList.size();
        }
        int columnBegin = 0;
        if (forIndex > 0) {
            for (int i = 0; i < forIndex; i++) {
                columnBegin += fieldNumArgs[i];
            }
        }
        int columnEnd = columnBegin + fieldNumArgs[forIndex];

        int allDetailCount = 0;
        for (Object parentData : parentList) {
            Object detail = ReflectByCache.getValueByPropertyName(parentData, detailFieldList.get(forIndex).getName());
            int detailCount = 1;
            if (detail != null && CollectionUtil.isNotEmpty((List)detail)) {
                // 有明细才需要合并
                detailCount = countDetailSize((List)detail, detailFieldList, forIndex, rowBegin);
                if (detailCount > 1) {
                    int rowEnd = rowBegin + detailCount - 1;
                    for (int i = columnBegin; i < columnEnd; i++) {
                        cellRangeAddressList.add(new CellRangeAddress(rowBegin, rowEnd, i, i));
                    }
                    rowBegin = rowEnd;
                }
            }
            rowBegin++;
            allDetailCount += detailCount;
        }
        return allDetailCount;
    }

    private List<Field> getListGetters() {
        // 主表
        List<Field> mainFieldList = ReflectByCache.getAllFields(dataList.get(0).getClass());
        // 看有几级明细
        List<Field> resultList = new ArrayList<>(fieldNumArgs.length);
        for (Integer fieldNum : fieldNumArgs) {
            // 找返回类型为List的字段
            Field detailField = null;
            for (Field field : mainFieldList) {
                if (List.class.isAssignableFrom(field.getType())) {
                    detailField = field;
                    resultList.add(field);
                }
            }
            // 根据泛型找到明细的类型
            if (detailField != null) {
                Type genericType = detailField.getGenericType();
                if (null == genericType) {
                    break;
                }
                if (genericType instanceof ParameterizedType) {
                    ParameterizedType pt = (ParameterizedType)genericType;
                    // 得到泛型里的class类型对象
                    Class<?> actualTypeArgument = (Class<?>)pt.getActualTypeArguments()[0];
                    // 重置主表的类型
                    mainFieldList = ReflectByCache.getAllFields(actualTypeArgument);
                } else {
                    break;
                }
            }
        }
        return resultList;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (CollectionUtil.isNotEmpty(cellRangeAddressList)) {
            for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
                writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }
}

CustomCellWriteHandler 类

public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            //标题字体比较大，加长一点
            return new BigDecimal(cell.getStringCellValue().length()).multiply(new BigDecimal("3")).intValue();
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return new BigDecimal(cellData.getStringValue().length()).multiply(new BigDecimal("1.2")).intValue();
                    case NUMBER:
                        return new BigDecimal(cellData.getNumberValue().toString().length()).divide(new BigDecimal("3"),0,BigDecimal.ROUND_HALF_UP).intValue();
                    default:
                        return -1;
                }
            }
        }
    }
}