阿里 easyExcel 动态写入数据,多级表头,单元格合并示例

excel写入流程

    public static void main(String[] args) throws Exception {
        //标题
        List<List<String>> header = getHeader();
        //数据
        List<List<String>> data = getData();
        //单元格合并条件
        List<MergeRange> mergePlans=getMergePlans();
        //写入文件
        write(Files.newOutputStream(Paths.get("D://test.xlsx")),header,data,mergePlans);
    }

    //随意造的数据
    private static List<List<String>> getData() {
        List<List<String>> list=new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            ArrayList<String> l1 = new ArrayList<>();
            for (int i1 = 0; i1 < 4; i1++) {
                l1.add(i1+"");
            }
            list.add(l1);
        }
        return list;
    }

    private static List<List<String>> getHeader() {
        List<List<String>> list = new ArrayList<>();
        //创建一个一级标题
        list.add(createHead("标题1"));
        //创建一个二级标题
        list.add(createHead("标题2-1", "标题2-2"));
        list.add(createHead("标题3-1", "标题3-2"));
        //创建一个三级标题
        list.add(createHead("标题4-1", "标题4-2","标题4-3"));
        return list;
    }

    public static List<String> createHead(String... head) {
        return new ArrayList<>(Arrays.asList(head));
    }

    private static List<MergeRange> getMergePlans() {
        List<MergeRange> list= new ArrayList<>();
        //行合并,0列的3-4行进行合并
        list.add(new RowMergeRange(0,3,4));
        //行合并,0列的5-7行进行合并
        list.add(new RowMergeRange(0,5,7));
        //列合并,8行的1-3列进行合并
        list.add(new ColMergeRange(8,1,3));
        //列合并,10行的1-2列进行合并
        list.add(new ColMergeRange(10,1,2));
        return list;
    }
    
    public  static void write(OutputStream outputStream, List<List<String>> header,
         List<List<String>> data, List<MergeRange> mergeRanges){

        EasyExcel.write(outputStream)
                .head(header)
                .registerWriteHandler(new BizMergeStrategy(mergeRanges))
                .sheet("sheet")
                .doWrite(data);
    }

使用到的相关类

    @Data
    public static abstract class MergeRange{
        public int start;
        public int end;

        public abstract CellRangeAddress toCellRangeAddress();
    }

    @Data
    public static class RowMergeRange extends MergeRange{
        private int columnIndex;

        public RowMergeRange(int columnIndex,int start,int end) {
            this.start=start;
            this.end=end;
            this.columnIndex = columnIndex;
        }

        @Override
        public CellRangeAddress toCellRangeAddress() {
            return  new CellRangeAddress(start,
                    end, columnIndex, columnIndex);
        }
    }

    @Data
    public static class ColMergeRange extends MergeRange{
        private int rowIndex;

        public ColMergeRange(int rowIndex,int start,int end) {
            this.start=start;
            this.end=end;
            this.rowIndex = rowIndex;
        }

        @Override
        public CellRangeAddress toCellRangeAddress() {
            return  new CellRangeAddress(rowIndex,
                    rowIndex, start, end);
        }
    }

    public static class BizMergeStrategy extends AbstractMergeStrategy {

        private List<MergeRange> mergeSrategy;

        public BizMergeStrategy(List<MergeRange> list) {
            this.mergeSrategy = list;
        }

        @Override
        protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
            for (MergeRange mergeRange : mergeSrategy) {
                sheet.addMergedRegionUnsafe(mergeRange.toCellRangeAddress());
            }
        }
    }

效果如图