excel单元格合并策略

excel单元格合并策略

上一篇我们讲了excel动态列的导出，今天我们继续来填之前挖下的坑。

所以补一下excel的单元格合并策略

上一篇在这里excel动态列的导出

依赖

我们这里依然使用的是easyexcel来做导出

<dependency>
    <groupId>com.alibabagroupId>
    <artifactId>easyexcelartifactId>
    <version>2.2.6version>
dependency>
1
2
3
4
5

先看效果吧

给两个效果吧，在接下来的代码中会顺序讲解这两个效果的实现过程。

第一个效果是只有标题第一行的单元格合并，并且内容靠右

---

第二个效果是标题合并，第二行的数据要随着导出的数据所在公司的名称和日期进行变化
标题下的内容需要进行 一对多 指定单元格的合并

第一种效果

先看一下导出的excel对象,截了其中一部分字段
使用value= {标题,子标题}的形式定义了第一级和第二级的标题 ，第一级的内容需要相同，相同的内容会自动合并成一个单元格。

@Data
public class ImportPmsPriceSeaExcelDto {

    @ExcelProperty(value = {"填写须知：\n" +
            "1. 请勿修改表格结构；\n" +
            "2. 带*字段为必填项；\n" +
            "3. 开船日，截关日：填写数字，多个使用[/]隔开；\n" +
            "4. 生效日期，失效日期：请按YYYY-MM-DD的格式填写，如2023-01-01\n" +
            "5. 航程，免堆期：请填写数字，例如 2\n" +
            "6. 推荐价格：请填入「是」、「否」，若不填则默认为「否」；","合约号"})
    private String ctrNo;

    @ExcelProperty(value = {"填写须知：\n" +
            "1. 请勿修改表格结构；\n" +
            "2. 带*字段为必填项；\n" +
            "3. 开船日，截关日：填写数字，多个使用[/]隔开；\n" +
            "4. 生效日期，失效日期：请按YYYY-MM-DD的格式填写，如2023-01-01\n" +
            "5. 航程，免堆期：请填写数字，例如 2\n" +
            "6. 推荐价格：请填入「是」、「否」，若不填则默认为「否」；","开船日*"})
    @ColumnWidth(20)
    private String sailingWeekdays;

    @ExcelProperty(value = {"填写须知：\n" +
            "1. 请勿修改表格结构；\n" +
            "2. 带*字段为必填项；\n" +
            "3. 开船日，截关日：填写数字，多个使用[/]隔开；\n" +
            "4. 生效日期，失效日期：请按YYYY-MM-DD的格式填写，如2023-01-01\n" +
            "5. 航程，免堆期：请填写数字，例如 2\n" +
            "6. 推荐价格：请填入「是」、「否」，若不填则默认为「否」；","截关日"})
    private String customsCutoffWeekdays;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

---

定义完导出对象后，我们就可以接着写导出了。
为了控制第一行标题的样式，我们使用了 .registerWriteHandler(new PmsPriceSeaTemplateMergeStrategy()) 自定义合并策略，来控制excel的样式

---

@Override
    public void exportPmsPriceSeaTemplate(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-disposition", "attachment;filename=" + "全部数据.xlsx");

        List<ImportPmsPriceSeaExcelDto> excelVoList = createImportPmsPriceSeaExcelDto();

        EasyExcel.write(response.getOutputStream())
                .head(ImportPmsPriceSeaExcelDto.class)
                .excelType(ExcelTypeEnum.XLSX)
                .registerWriteHandler(new PmsPriceSeaTemplateMergeStrategy())
                .sheet("运单模板")
                .doWrite(excelVoList);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

---

自定义策略类

接着定义自定义策略
在titleHandle()方法中，我们将标题的所有列，设置了内容靠左，换行和字体大小

---

/**
 * @Author: tfxing
 * @Description: RowWriteHandler
 */
public class PmsPriceSeaTemplateMergeStrategy implements RowWriteHandler {


    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
        if (row.getRowNum() > 1) {
            return;
        }
        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        titleHandle(sheet);

        titleHandle2(sheet);

    }

    private void titleHandle2(Sheet sheet) {

        Row row = sheet.getRow(1);

        if(null == row) {
            return;
        }

        row.setHeightInPoints(45);
    }

    private void titleHandle(Sheet sheet) {
        Workbook workbook = sheet.getWorkbook();
        Font font = workbook.createFont();

        CellStyle cellStyle = workbook.createCellStyle();
        // 内容靠左
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setFont(font);
        // 是否换行
        cellStyle.setWrapText(true);

        Row row = sheet.getRow(0);
        for (int i = 0; i < 24; i++) {
            Cell cell0 = row.getCell(i);
            if(null == cell0) {
                continue;
            }
            cell0.setCellStyle(cellStyle);
        }

		// 设置字体大小
        row.setHeightInPoints(125);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

---

第二种效果

第一种效果的实现还是挺简单的是吧，那么要来实现第二种效果吧

复习一下

同样的我们先定义一下导出的对象,同样是截取了其中一部分

@Data
public class AcctSettingExcelVo {

    @ExcelProperty({"凭证列表","公司名称","日期"})
    @ColumnWidth(12)
    private String vchDateStr;

    @ExcelProperty({"凭证列表","公司名称","凭证字号"})
    @ColumnWidth(20)
    private String vchWordStr;

    @ExcelProperty({"凭证列表","公司名称","凭证类型"})
    @ColumnWidth(20)
    private String vchTypeStr;

    @ExcelProperty({"凭证列表","公司名称","摘要"})
    @ColumnWidth(12)
    private String enTryDesc;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

直接看合并策略吧

通过构造器传参，将三个关键的参数传递到类中。map，companyName,glpName,这三个参数分别是，需要合并的行列的map，公司名称，日期

map的处理是在调用处处理的的，我这里的处理过程就是通过将将数据需要合并的列和行解析出来，map的key就是行的下标，value就是列的下标。

具体实现我这里就不放出来了，每个人的业务不一样逻辑也不一样，大家自己想办法写吧。

通过titlehandle()方法将第二行的数据替换成companyName和glpName，公司名称和日期。

		Workbook workbook = sheet.getWorkbook();
        Font font = workbook.createFont();
        font.setBold(true); // 字体加粗
        font.setFontHeightInPoints((short)14); // 字体大小

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT); // 内容居左
        cellStyle.setFont(font);

		// 前7行的内容设置为公司，内容相同时单元格会自动合并，并设置上风格 内容居左
        for (int i = 0; i < 7; i++) {
            Cell cell0 = sheet.getRow(1).getCell(i);
            cell0.setCellValue(companyName);
            cell0.setCellStyle(cellStyle);
        }

        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.RIGHT); // 内容居右
        cellStyle1.setFont(font);

		// 第7行后的数据内容设置为日期，并设置上风格 内容居右
        for (int i = 7; i < 12; i++) {
            Cell cell7 = sheet.getRow(1).getCell(i);
            cell7.setCellValue(glpName);
            cell7.setCellStyle(cellStyle1);
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

---

decimalHandle() 方法是将小数转为千分位数显示的格式，三分一个分隔符。例如：10,010

---

前三行和最后两行需要单元格合并,

将map中的行和列取出来作为合并的参数。

new CellRangeAddress(rowNum, lastRowNum, i, i) 这个对象中的四个参数分别是：第一行，最后一行，第一列，最后一列

在我们往期的一篇博客中有详细说明，在这里

Integer lastRowNum = map.get(rowNum);
if(-1 == lastRowNum) {
    return;
}

for (int i = 0; i < 3; i++) {
    //合并单元格区域只有一个单元格时，不合并
    if (rowNum == lastRowNum) {
        return;
    }
    CellRangeAddress cellRangeAddress = new CellRangeAddress(rowNum, lastRowNum, i, i);
    sheet.addMergedRegionUnsafe(cellRangeAddress);
}

for (int i = 10; i < 12; i++) {
    //合并单元格区域只有一个单元格时，不合并
    if (rowNum == lastRowNum) {
        return;
    }
    CellRangeAddress cellRangeAddress1 = new CellRangeAddress(rowNum, lastRowNum, i, i);
    sheet.addMergedRegionUnsafe(cellRangeAddress1);
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

---

自定义策略详细代码

---

package com.yunwuyun.easy.settlement.strategy;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.yunwuyun.easy.commons.utils.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.lang.reflect.Field;
import java.util.*;

/**
 * @Author: tfxing
 * @Date: 2022/12/10/10:20
 * @Description: RowWriteHandler
 */
public class CustomMergeStrategy1 implements RowWriteHandler {


    private Map<Integer,Integer> map = new HashMap<>();

    private String companyName;

    private String glpName;


    public CustomMergeStrategy1(Map<Integer,Integer> map,String companyName,String glpName) {
        this.map = map;
        this.companyName = companyName;
        this.glpName = glpName;
    }


    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
        // 如果是标题,则直接返回
        if (aBoolean) {
            return;
        }

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        int rowNum = row.getRowNum();
        titleHandle(sheet,companyName,glpName);
        
        decimalHandle(sheet,integer,aBoolean);

        if (rowNum <= 2) {
            return;
        }
        Integer lastRowNum = map.get(rowNum);
        if(-1 == lastRowNum) {
            return;
        }
        
        //合并单元格区域只有一个单元格时，不合并
        for (int i = 0; i < 3; i++) {
            //合并单元格区域只有一个单元格时，不合并
            if (rowNum == lastRowNum && i == i) {
                return;
            }
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowNum, lastRowNum, i, i);
            sheet.addMergedRegionUnsafe(cellRangeAddress);
        }

        for (int i = 10; i < 12; i++) {
            //合并单元格区域只有一个单元格时，不合并
            if (rowNum == lastRowNum && i == i) {
                return;
            }
            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(rowNum, lastRowNum, i, i);
            sheet.addMergedRegionUnsafe(cellRangeAddress1);
        }
    }
    
    private void titleHandle(Sheet sheet, String companyName, String glpName) {
        Workbook workbook = sheet.getWorkbook();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short)14);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setFont(font);

        for (int i = 0; i < 7; i++) {
            Cell cell0 = sheet.getRow(1).getCell(i);
            cell0.setCellValue(companyName);
            cell0.setCellStyle(cellStyle);
        }

        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.RIGHT);
        cellStyle1.setFont(font);

        for (int i = 7; i < 12; i++) {
            Cell cell7 = sheet.getRow(1).getCell(i);
            cell7.setCellValue(glpName);
            cell7.setCellStyle(cellStyle1);
        }


    }

    /**
     * 金额列处理
     * @param sheet
     * @param integer
     * @param aBoolean
     */
    private void decimalHandle(Sheet sheet, Integer integer, Boolean aBoolean) {
        if(!aBoolean) {
            Workbook workbook = sheet.getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
            Cell cell = sheet.getRow(integer+3).getCell(6);
            cell.setCellStyle(cellStyle);
            handleDecimalValue(cell);

            Cell cell8 = sheet.getRow(integer+3).getCell(8);
            cell8.setCellStyle(cellStyle);
            handleDecimalValue(cell8);

            Cell cell9 = sheet.getRow(integer+3).getCell(9);
            cell9.setCellStyle(cellStyle);
            handleDecimalValue(cell9);
        }
    }

    private void handleDecimalValue(Cell cell) {
        String stringCellValue = cell.getStringCellValue();

        String[] split = stringCellValue.split("\\.");
        String preValue = split[0];
        char[] chars = preValue.toCharArray();

        String str = "";
        for (int i = chars.length - 1,j=1; i >= 0; i--,j++) {
            str += chars[i];
            if(j % 3 == 0 && i != 0) {
                str += ",";
            }
        }
        str = StringUtils.reverse(str);
        str = str+"."+split[1];
        cell.setCellValue(str);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163

下一篇咱们来写一下（我还没想好）吧

（相别容易见时难，别后相思独凄然，千山万水总是情，点个关注行不行）