Java导出excel表格，相同行合并，动态合并单元格。

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
   /**
     * @param title 标题集合 tilte的长度应该与list中的model的属性个数一致
     * @param maps 内容集合
     * @param mergeIndex 合并单元格的列（0，1，2）代表前三列需要行合并。必须要传值，也必须从0开始传。
     */

    public static String createExcel(String[] title, Map<String/*sheet名*/, List<Map<String/*对应title的值*/, String>>> maps, int[] mergeIndex) {
        if (title.length == 0) {
            return null;
        }
        /*初始化excel模板*/
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = null;
        int n = 0;
        /*循环sheet页*/
        for (Map.Entry<String, List<Map<String/*对应title的值*/, String>>> entry : maps.entrySet()) {
            /*实例化sheet对象并且设置sheet名称，book对象*/
            try {
                sheet = workbook.createSheet();
                workbook.setSheetName(n, entry.getKey());
                workbook.setSelectedTab(0);
            } catch (Exception e) {
                e.printStackTrace();
            }
            /*初始化head，填值标题行（第一行）*/
            Row row0 = sheet.createRow(0);

            for (int i = 0; i < title.length; i++) {
                /*创建单元格，指定类型*/
                Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);
                cell_1.setCellValue(title[i]);
                让第二列和第三列的表头先合并。
              /*  if (i == 1) {
                    CellRangeAddress region = new CellRangeAddress(0, 0, 1, 2);
                    sheet.addMergedRegion(region);
                    cell_1.setCellValue(title[i]);
                } else {
                    cell_1.setCellValue(title[i]);
                }*/
            }
            /*得到当前sheet下的数据集合*/
            List<Map<String/*对应title的值*/, String>> list = entry.getValue();
            /*遍历该数据集合*/
            List<PoiModel> poiModels = new ArrayList();
            if (null != workbook) {
                Iterator iterator = list.iterator();
                int index = 1;/*这里1是从excel的第二行开始，第一行已经塞入标题了*/
                while (iterator.hasNext()) {
                    Row row = sheet.createRow(index);
                    /*取得当前这行的map，该map中以key，value的形式存着这一行值*/
                    Map<String, String> map = (Map<String, String>) iterator.next();
                    /*循环列数，给当前行塞值*/
                    for (int i = 0; i < title.length; i++) {
                        String old = "";
                        /*old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记*/
                        if (index > 1) {
                            old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();
                        }
                        Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                        MergedResult mergedRegion = MergeUtil.isMergedRegion(sheet, index, 1);//(!mergedRegion.isMerged)&&
                        //2-3列相同内容合并
                       /*  if (i==1&&map.get(title[i]).equals(map.get(title[i+1]))){
                            CellRangeAddress region = new CellRangeAddress(index, index, 1, 2);
                            sheet.addMergedRegion(region);
                            cell.setCellValue(map.get(title[i]));
                        }*/
                        cell.setCellValue(map.get(title[i]));
                        /*循环需要合并的列*/
                        for (int j = 0; j < mergeIndex.length; j++) {
                            if (index == 1) {
                                /*记录第一行的开始行和开始列*/
                                PoiModel poiModel = new PoiModel();
                                poiModel.setOldContent(map.get(title[i]));
                                poiModel.setContent(map.get(title[i]));
                                poiModel.setRowIndex(1);
                                poiModel.setCellIndex(i);
                                poiModels.add(poiModel);
                                break;
                            } else if (i > 0 && mergeIndex[j] == i) {/*这边i>0也是因为第一列已经是最前一列了，只能从第二列开始*/
                                /*当前同一列的内容与上一行同一列不同时，把那以上的合并, 或者在当前元素一样的情况下，前一列的元素并不一样，这种情况也合并*/
                                /*如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把下面条件中||poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i-1]))去掉就行*/
                                if ((!poiModels.get(i).getContent().equals(map.get(title[i])))) {
                                    /*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/
                                    CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                    //在sheet里增加合并单元格
                                    sheet.addMergedRegion(cra);
                                    /*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/
                                    poiModels.get(i).setContent(map.get(title[i]));
                                    poiModels.get(i).setRowIndex(index);
                                    poiModels.get(i).setCellIndex(i);
                                }
                            }
                            /*处理第一列的情况*/
                            if (mergeIndex[j] == i && i == 0 && !poiModels.get(i).getContent().equals(map.get(title[i]))) {
                                /*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/
                                CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                //在sheet里增加合并单元格
                                sheet.addMergedRegion(cra);
                                cell.setCellValue(title[i]);
                                /*重新记录该列的内容为当前内容，行标记改为当前行标记*/
                                poiModels.get(i).setContent(map.get(title[i]));
                                poiModels.get(i).setRowIndex(index);
                                poiModels.get(i).setCellIndex(i);
                            }

                            /*最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容*/
                            if (mergeIndex[j] == i && index == list.size()) {
                                CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                //在sheet里增加合并单元格
                                sheet.addMergedRegion(cra);
                            }
                        }
                        /*在每一个单元格处理完成后，把这个单元格内容设置为old内容*/
                        poiModels.get(i).setOldContent(old);
                    }
                    index++;
                }
            }
            n++;
        }
        
        /*生成临时文件*/
        FileOutputStream out = null;
        String localPath = null;
        File tempFile = null;
        String fileName = "测试装备";
        try {
            tempFile = new File("D://explord/", fileName + ".xlsx");
            localPath = tempFile.getAbsolutePath();
            out = new FileOutputStream(localPath);
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                out.flush();
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return localPath;
    }
    

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
public class PoiModel {
 
    private String content;
 
    private String oldContent;
 
    private int rowIndex;
 
    private int cellIndex;
 
    public String getOldContent() {
        return oldContent;
    }
 
    public void setOldContent(String oldContent) {
        this.oldContent = oldContent;
    }
 
    public String getContent() {
        return content;
    }
 
    public void setContent(String content) {
        this.content = content;
    }
 
    public int getRowIndex() {
        return rowIndex;
    }
 
    public void setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
    }
 
    public int getCellIndex() {
        return cellIndex;
    }
 
    public void setCellIndex(int cellIndex) {
        this.cellIndex = cellIndex;
    }
}

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

public class MergedResult {
    boolean isMerged;//是否合并单元格
    int rowIndex;//行下标
    int columnIndex;//列下标
    int firstRow;//合并的行 开始下标
    int lastRow;//合并的行 结束下标
    int firstColumn;//合并的列 开始下标
    int lastColumn;//合并的列 结束下标
    int rowMergeNum;//合并的行数
    int columnMergeNum;//合并的列数
 
    public boolean getIsMerged() {
        return isMerged;
    }
 
    public void setIsMerged(boolean merged) {
        isMerged = merged;
    }
 
    public int getRowIndex() {
        return rowIndex;
    }
 
    public void setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
    }
 
    public int getColumnIndex() {
        return columnIndex;
    }
 
    public void setColumnIndex(int columnIndex) {
        this.columnIndex = columnIndex;
    }
 
    public int getFirstRow() {
        return firstRow;
    }
 
    public void setFirstRow(int firstRow) {
        this.firstRow = firstRow;
    }
 
    public int getLastRow() {
        return lastRow;
    }
 
    public void setLastRow(int lastRow) {
        this.lastRow = lastRow;
    }
 
    public int getFirstColumn() {
        return firstColumn;
    }
 
    public void setFirstColumn(int firstColumn) {
        this.firstColumn = firstColumn;
    }
 
    public int getLastColumn() {
        return lastColumn;
    }
 
    public void setLastColumn(int lastColumn) {
        this.lastColumn = lastColumn;
    }
 
    public int getRowMergeNum() {
        return rowMergeNum;
    }
 
    public void setRowMergeNum(int rowMergeNum) {
        this.rowMergeNum = rowMergeNum;
    }
 
    public int getColumnMergeNum() {
        return columnMergeNum;
    }
 
    public void setColumnMergeNum(int columnMergeNum) {
        this.columnMergeNum = columnMergeNum;
    }
}