easyexcel 合并单元格

*********************

相关注解

ContentLoopMerge：标注在字段上

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ContentLoopMerge {

    int eachRow() default -1;      //合并行
    int columnExtend() default 1;  //合并列
}

OnceAbsoluteMerge：标注在类上

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface OnceAbsoluteMerge {

    int firstRowIndex() default -1;      //初始行
    int lastRowIndex() default -1;       //最后一行

    int firstColumnIndex() default -1;   //初始列
    int lastColumnIndex() default -1;    //最后一列
}

*********************

示例

Test

@Data
class Book{

    @ContentLoopMerge(eachRow = 2)
    private Integer id;

    private String name;

    private Double price;
}

public class Test {

    private static final String write_path="e:"+ File.separator+"java"+File.separator+"easyexcel"+File.separator+"write.xlsx";

    public static void write(){
        List<Book> list=new ArrayList<>();

        for (int i=0;i<5;i++){
            Book book=new Book();
            book.setId(i);
            book.setName("海贼王"+i);
            book.setPrice((double)(i+10));

            list.add(book);
        }

        EasyExcel.write(write_path,Book.class).sheet().doWrite(list);
    }

    public static void main(String[] args){
        write();
    }
}

****************

使用测试

1. 添加依赖

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

2.表头(含单元格合并)

 public List<List<String>> ExcelHead() {
        List<List<String>> headList = new ArrayList();

        headList.add(new ArrayList() {{
            add("时间");
            add("参数名\\回路名");
        }});
        headList.add(new ArrayList() {{
            add("时间"        }});
        headList.add(new ArrayList() {{
            add("时间");
            add("电流I(A)");
        }});
        headList.add(new ArrayList() {{
            add("环境温度：" + temperature);
            add("相电压U");
        }});
        headList.add(new ArrayList() {{
            add("环境温度");
            add("线电压U");
        }});
        headList.add(new ArrayList() {{
               add("环境温度");
            add("频率(Hz)");
        }});
        headList.add(new ArrayList() {{
            add("环境湿度");
            add("开关分合(分\\合)");
        }});
        headList.add(new ArrayList() {{
             add("环境湿度");
            add("总有功功率(kW)");
        }});
        headList.add(new ArrayList() {{
            add("抄表记录单");
            add("总无功功率(kVar)");
        }});
        headList.add(new ArrayList() {{
            add("抄表记录单");
            add("总功率因数");
        }});
        headList.add(new ArrayList() {{
            add("抄表记录单");
            add("正向有功电能(kW.h)");
        }});
        headList.add(new ArrayList() {{
            add("项目");
            add("正向无功电能(kVar.h)");
        }});
        headList.add(new ArrayList() {{
            add("表单号");
            add("所属变压器");
        }});
        headList.add(new ArrayList() {{
            add("表单号");
            add("变压器温度(℃)");
        }});

        return headList;
    }

3. 数据逻辑

    List<HashMap> data = statisticmapper.runreport(statisticdto);
        for (int i = 0; i < data.size(); i++) {
            String id = data.get(i).get("id").toString();
            String name = data.get(i).get("equipment_name").toString();
            if (data.get(i).get("paramid") == null) continue;
            String paramid = data.get(i).get("paramid").toString();
            String point_id = data.get(i).get("point_id").toString();
            String value = data.get(i).get("value").toString();
            switch (paramid) {
                case "e2fa092c-6146-11eb-82f0-6c2b59bec1ff":
                    temperature = value + "℃ ";
                    continue;
                case "70536171-6147-11eb-82f0-6c2b59bec1ff":
                    humidity = value + "%";
                    continue;
            }

            if (!devicecheck.contains(id)) {
                tosave.add(rowdata(name, "A相"));
                tosave.add(rowdata(name, "B相"));
                tosave.add(rowdata(name, "C相"));
                devicecheck.add(id);
            }

            if (dic.containsKey(id + point_id)) continue;
            dic.put(id + point_id, i);

            int index = devicecheck.indexOf(id) * 3;
            switch (paramid) {
                case "069d9c73-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(5, value);
                    tosave.get(index + 1).set(5, value);
                    tosave.get(index + 2).set(5, value);
                    break;
                case "068d17d6-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(7, value);
                    tosave.get(index + 1).set(7, value);
                    tosave.get(index + 2).set(7, value);
                    break;
                case "071ce9e3-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(8, value);
                    tosave.get(index + 1).set(8, value);
                    tosave.get(index + 2).set(8, value);
                    break;
                case "05b23645-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(9, value);
                    tosave.get(index + 1).set(9, value);
                    tosave.get(index + 2).set(9, value);
                    break;
                case "062ee255-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(10, value);
                    tosave.get(index + 1).set(10, value);
                    tosave.get(index + 2).set(10, value);
                    break;
                case "06dd3edb-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(11, value);
                    tosave.get(index + 1).set(11, value);
                    tosave.get(index + 2).set(11, value);
                    break;
                case "061fa440-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(2, value);
                    break;
                case "06fa867e-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(3, value + "kV");
                    break;
                case "05952cc2-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index).set(4, "Uab(" + value + "kV)");
                    break;
                case "06805737-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index + 1).set(2, value);
                    break;
                case "05f1ca1b-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index + 1).set(3, value + "kV");
                    break;
                case "06ab9dbf-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index + 1).set(4, "Uab(" + value + "kV)");
                    break;
                case "0604e966-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index + 2).set(2, value);
                    break;
                case "058946a7-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index + 2).set(3, value + "kV");
                    break;
                case "06b858c6-6148-11eb-82f0-6c2b59bec1ff":
                    tosave.get(index + 2).set(4, "Uab(" + value + "kV)");
                    break;
            }
        }

 public List<String> rowdata(String name, String type) {
        return new ArrayList() {{
            add(name);
            add(type);
            add("-");
            add("-");
            add("-");
            add("-");
            add("-");
            add("-");
            add("-");
            add("-");
            add("-");
            add("-");
            add("无");
            add("-");
        }};
    }

3.excel

   List<List<String>> head = ExcelHead();

        EasyExcel.write(fileUrl).head(head).sheet(sheetname)
           //     .registerWriteHandler(StyleStrategy())
           //     .registerWriteHandler(new ReportMergeStrategy(devicecheck.size()))//自定义合并 单元格
           //     .registerWriteHandler(new RowWriteHandler())
                .doWrite(tosave);

4. 结果

前言

导出excel，介绍如何使用LoopMergeStrategy实现简单的合并

效果

每隔2行，合并一次

横向合并

横向和纵向合并

实现

导入依赖

 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.google.guava/guava -->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>30.1-jre</version>
        </dependency>

        <!-- springboot test启动器 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

单元测试

/**
 * 合并单元格
 * @date 01/31/2021 03:11
 */
public class Demo {

    /**
     * 合并单元格
     */
    @Test
    public void testMyMergeWrite() {

        String fileName = "/Users/quanlinglong/Downloads/mergeDemo/" + "mymergeWrite" + System.currentTimeMillis() + ".xlsx";
        System.out.println("fileName = " + fileName);

        // 每隔2行合并一次，横向合并1列，从第0列开始
//        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1, 0);
        // 上面的等价于
//        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);

        // 从第columnIndex列开始，向右合并（columnExtend-1）列
//        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(1, 2, 0);

        // 合并2行2列
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 2, 0);

        EasyExcel.write(fileName, WriteDemo.DemoData.class)
                .registerWriteHandler(loopMergeStrategy)
                .sheet("aaa")
                .doWrite(data());
    }

    private List<List<Object>> data() {
        List<List<Object>> result = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            result.add(Lists.newArrayList("标题" + i, new Date(), i * 0.1));
        }
        return result;
    }
}

分析LoopMergeStrategy类，它继承AbstractRowWriteHandler，在afterRowDispose方法里对每行做了处理，LoopMergeStrategy的构造函数传参是用来设置合并范围CellRangeAddress，再添加到当前sheet。

public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        if (!isHead) {
            if (relativeRowIndex % this.eachRow == 0) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum(), row.getRowNum() + this.eachRow - 1, this.columnIndex, this.columnIndex + this.columnExtend - 1);
                writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);
            }

        }
    }

CellRangeAddress构造函数有4个参数
参数
0：合并起始行
1：合并终止行
2：合并起始列
3：合并终止列

 CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum(), row.getRowNum() + this.eachRow - 1, this.columnIndex, this.columnIndex + this.columnExtend - 1);
 writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);

构造函数传进来的eachRow, columnExtend, columnIndex，都用在这里了

相关链接
easyexcel 动态合并单元格