POI自定义表头（合并列）导出Excel工具类

来源：互联网发布：sql server union all 编辑：程序博客网时间：2024/05/20 09:05
POI自定义表头（合并列）导出Excel工具类
先看图，可以看出有很多合并列，此为经常用到的需求，为了简化需求，这个类应运而生。未经许可，禁止转载
http://www.xdemo.org/poi-customize-columns/ 未经许可，禁止转载
写在前面，之前发布了一个ExcelUtils工具类参见:
http://www.xdemo.org/java-excel-poi-import-export-annotation-reflect/
http://www.xdemo.org/java-poi-excel/
1. ExcelUtils由于代码较多，已经分开到三个类中，这个类已经不再推荐使用(不代表不可用，仍然可用)
具体使用如下三个类：
@see org.xdemo.superutil.thirdparty.excel.BeanToExcel
@see org.xdemo.superutil.thirdparty.excel.ExcelToBean
@see org.xdemo.superutil.thirdparty.excel.CustomizeToExcel
2.BeanToExcel是根据注解，将数据到处到Excel中

3.ExcelToBean是导入Excel，转化成相应的Bean

4.CustomizeToExcel这个类中的方法是新增的，支持自定义表头，主要有四个方法
/**
 * 获取工作簿对象
 * @param columns List<ExcelColumn> 列定义
 * @param list List<Map<String, Object>> 数据
 * @return Workbook
 * @throws IOException
 */
static org.apache.poi.ss.usermodel.WorkbookgetWorkbook(List<ExcelColumn> columns, List<Map<String,Object>> list)
/**
 * 获取工作簿对象
 * @param columnsJsonArray String 列定义，String类型的JSON数组
 * @param list List<Map<String, Object>> 数据
 * @return Workbook
 * @throws IOException
 */
static org.apache.poi.ss.usermodel.WorkbookgetWorkbook(String columnsJsonArray, List<Map<String,Object>> list) 
/**
 * 写入到文件
 * @param columns List<ExcelColumn> 列定义
 * @param list List<Map<String, Object>> 数据
 * @param filePath 文件路径
 * @throws Exception
 */
static void toFile(List<ExcelColumn> columns, List<Map<String,Object>> list, String filePath) 
/**
 * 写入到文件
 * @param columnsJsonArray String 列定义
 * @param list List<Map<String, Object>> 数据
 * @param filePath 文件路径
 * @throws Exception
 */
static void toFile(String columnsJsonArray, List<Map<String,Object>> list, String filePath) 

5. ExcelColumn类包含了title,field,width,children定义
//列名
private String title;
//列对应的数据中的field
private String field;
//列宽
private int width=0;
//子列
private List<ExcelColumn> children;

6. CustomizeToExcel具体使用示例代码
注意：
6.1 日期格式默认为yyyy-MM-dd HH:mm:ss
6.2 width宽度定义只对叶子节点有效
6.3 field定义只对叶子节点有效
6.4 默认固定列头
6.5 使用步骤
6.5.1 定义列头
6.5.2 定义数据List<Map<String,Object>>类型
6.6 关于合并列的说明
合并列，就是子节点的集合

节点A
    |-children:[{节点A1},{节点A2}]
    |-field
    |-width
    |-title
关键类CustomizeToExcel http://www.xdemo.org/poi-customize-columns/ 未经许可，禁止转载
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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
package org.xdemo.superutil.thirdparty.excel;
 
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
 
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.xdemo.superutil.j2se.RandomUtils;
import org.xdemo.superutil.thirdparty.gson.GsonTools;
 
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonParser;
 
/**
 * 自定义
 * 
 * @author Goofy 自定义表头的Excel
 */
public class CustomizeToExcel {
 
    // 创建工作簿
    private static Workbook wb = new SXSSFWorkbook();
    // 创建一个工作表sheet
    private static Sheet sheet = wb.createSheet();
 
    private static List<String> fields = new ArrayList<String>();
 
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
 
    /**
     * 获取工作簿对象
     * 
     * @param columns
     *            List<ExcelColumn> 列定义
     * @param list
     *            List<Map<String, Object>> 数据
     * @return Workbook
     * @throws IOException
     */
    public static Workbook getWorkbook(List<ExcelColumn> columns, List<Map<String, Object>> list) throws IOException {
        GsonTools gt = new GsonTools(false, null, null, null);
        return getWorkbook(gt.toJson(columns), list);
    }
 
    /**
     * 获取工作簿对象
     * 
     * @param columnsJsonArray
     *            String 列定义
     * @param list
     *            List<Map<String, Object>> 数据
     * @return Workbook
     * @throws IOException
     */
    public static Workbook getWorkbook(String columnsJsonArray, List<Map<String, Object>> list) throws IOException {
        init();
        JsonArray array = new JsonArray();
        JsonParser parser = new JsonParser();
        array = parser.parse(columnsJsonArray).getAsJsonArray();
        // 写标题
        writeTitles(array, 0, null);
        // 合并列
        mergeColumns();
        // 合并行
        mergeRows();
        // 写入行分隔符
        writeDelimiter();
        // 冻结表头
        freezeTitle();
        // 写数据
        writeData(list);
 
        return wb;
    }
 
    /**
     * 写入到文件
     * 
     * @param columns
     *            List<ExcelColumn> 列定义
     * @param list
     *            List<Map<String, Object>> 数据
     * @param filePath
     *            文件路径
     * @throws Exception
     */
    public static void toFile(List<ExcelColumn> columns, List<Map<String, Object>> list, String filePath) throws Exception {
        GsonTools gt = new GsonTools(false, null, null, null);
        toFile(gt.toJson(columns), list, filePath);
    }
 
    /**
     * 冻结表头
     */
    private static void freezeTitle() {
        int rowsCount = sheet.getPhysicalNumberOfRows();
        sheet.createFreezePane(0, rowsCount - 1);
    }
     
    /**
     * 初始化workbook,sheet,fields对象，防止多次连续调用发生错误
     */
    private static void init() {
        wb = new SXSSFWorkbook();
        sheet = wb.createSheet();
        fields = new ArrayList<String>();
    }
 
    /**
     * 写入到文件
     * 
     * @param columnsJsonArray
     *            String 列定义
     * @param list
     *            List<Map<String, Object>> 数据
     * @param filePath
     *            文件路径
     * @throws Exception
     */
    public static void toFile(String columnsJsonArray, List<Map<String, Object>> list, String filePath) throws Exception {
        FileOutputStream out = null;
        try {
            getWorkbook(columnsJsonArray, list);
            out = new FileOutputStream(filePath);
            wb.write(out);
            out.close();
        } catch (Exception e) {
            throw e;
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }
 
    /**
     * 写入行数据
     * 
     * @param list
     */
    private static void writeData(List<Map<String, Object>> list) {
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();
        // 列数
        int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
        Row row;
        Cell cell;
        CellStyle csTop = wb.createCellStyle();
        csTop.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
        CellStyle csBottom = wb.createCellStyle();
        csBottom.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM_DASHED);
        Map<String, Object> map = new HashMap<String, Object>();
        for (int r = 0; r < list.size(); r++) {
            row = sheet.createRow(r + rowsCount - 1);
            map = list.get(r);
            for (int c = 0; c < colsCount; c++) {
                cell = row.createCell(c);
                if (r == 0) {
                    cell.setCellStyle(csTop);
                }
                if (r == list.size() - 1) {
                    cell.setCellStyle(csBottom);
                }
                Object v = map.get(fields.get(c));
                if (v == null) {
                    cell.setCellValue("");
                } else {
                    if (v.getClass().equals(Date.class)) {
                        cell.setCellValue(sdf.format((Date) v));
                    } else {
                        cell.setCellValue(v.toString());
                    }
 
                }
 
            }
        }
    }
 
    /**
     * 写入列头下分隔符
     */
    private static void writeDelimiter() {
 
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();
        // 列数
        int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
        Cell cell;
        CellStyle cs = wb.createCellStyle();
        Row row = sheet.createRow(rowsCount);
        for (int c = 0; c < colsCount; c++) {
            cell = row.createCell(c);
            cs.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
            cell.setCellStyle(cs);
        }
 
    }
 
    /**
     * 合并行
     */
    private static void mergeRows() {
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();
        // 列数
        int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
        Row row = null;
 
        Cell cell = null;
        int rowSpan = 0;
        for (int c = 0; c < colsCount; c++) {
            rowSpan = 0;
            for (int r = rowsCount - 1; r > -1; r--) {
 
                row = sheet.getRow(r);
 
                cell = row.getCell(c);
 
                if (cell != null && r == rowsCount - 1) {
                    break;
                } else if (cell != null && r != rowsCount - 1) {
                    // 合并列
                    sheet.addMergedRegion(new CellRangeAddress(rowsCount - rowSpan - 1, rowsCount - 1, c, c));
                    break;
                } else {
                    // 行合并数+1
                    rowSpan++;
                }
            }
        }
    }
 
    /**
     * 合并列
     */
    private static void mergeColumns() {
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();
        // 列数
        int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
 
        Row row = null;
        Cell cell1 = null;
        Cell cell2 = null;
 
        int colSpan = 0;
 
        for (int r = 0; r < rowsCount; r++) {
            row = sheet.getRow(r);
            // 重置
            colSpan = 0;
            row = sheet.getRow(r);
            for (int c = 0; c < colsCount; c++) {
                cell1 = row.getCell(c);
                cell2 = row.getCell(c + 1);
                if (cell1 == null) {// 如果当前单元格是空的，跳过，继续当前行的后一个单元格查找
                    if (c == colsCount - 1) {
                        break;
                    } else {
                        continue;
                    }
                }
                if (cell2 == null) {// 说明当前行已经到最后一个单元格了
                    if (colSpan >= 1) {// 判断colSpan是否大于等于1，大于1就要合并了
                        // 合并行中连续相同的值的单元格
                        sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                        break;
                    }
                }
 
                if (cell1 != null && cell2 != null) {
                    // 如果当前单元格和下一个单元格内容相同，那么colSpan加1
                    if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                        colSpan++;
                    } else {
                        // 如果当前单元格和下一个不等，那么判断colSpan是否大于等于1
                        if (colSpan >= 1) {
                            // 合并行中连续相同的值的单元格
                            sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                            // 合并后重置colSpan
                            colSpan = 0;
                            continue;
                        }
                    }
                }
 
            }
        }
 
    }
 
    /**
     * 写入标题
     * 
     * @param array
     *            JSON数组
     * @param rowIndex
     *            行号
     * @param parentTitle
     *            父节点名称
     */
    private static void writeTitles(JsonArray array, int rowIndex, String parentTitle) {
        Iterator<JsonElement> it = array.iterator();
        Row row = sheet.getRow(rowIndex) == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);
        Row lastRow = rowIndex == 0 ? null : sheet.getRow(rowIndex - 1);
 
        int _colIndex = -1;
        // 遍历当前行，获取行最多的单元格的个数，因为如果之前行之前有单元格是空的，会对列索引造成问题
        int currentMaxColumns = 0;
        for (int x = 0; x < rowIndex; x++) {
            Row rr = sheet.getRow(x);
            if (rr.getPhysicalNumberOfCells() > currentMaxColumns) {
                currentMaxColumns = rr.getPhysicalNumberOfCells();
            }
        }
 
        // 查找上一级的列开始位置
        if (lastRow != null && parentTitle != null) {
            for (int i = 0; i < currentMaxColumns; i++) {
                if (lastRow.getCell(i) != null && lastRow.getCell(i).getStringCellValue() != null && lastRow.getCell(i).getStringCellValue().equals(parentTitle)) {
                    _colIndex = i;
                    break;
                }
            }
        }
        _colIndex = _colIndex == -1 ? 0 : _colIndex;
 
        Cell cell;
 
        while (it.hasNext()) {
 
            JsonElement e = it.next();
            // 是否是叶子节点
            boolean isLeaf = false;
            // 获取子节点数量
            int count = getSubNodesCount(e);
            // 没有子节点就是叶子节点，如果子节点为0，将count设置为1，是为了能够让下面的for循环执行一次，将标题写入
            if (count == 0) {
                count = 1;
                isLeaf = true;
            } else {
                isLeaf = false;
            }
 
            XSSFCellStyle cs = (XSSFCellStyle) wb.createCellStyle();
            // 水平居中
            cs.setAlignment(CellStyle.ALIGN_CENTER);
            // 垂直居中
            cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
 
            cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
            // 设置前景色
            cs.setFillForegroundColor(new XSSFColor(new java.awt.Color(RandomUtils.getRandomInt(0, 255), 185, RandomUtils.getRandomInt(0, 255))));
 
            // 字体样式
            Font font = wb.createFont();
            // 粗体
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            // 设置字体
            cs.setFont(font);
 
            // 根据子节点的数量，写入相应数量的父节点的名称，待完成后合并列
            for (int i = 0; i < count; i++) {
                cell = row.createCell(_colIndex++);
                if (isLeaf) {
 
                    // 如果没有子节点，就是浅红色的
                    cs.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 199, 206)));
                    if (e.getAsJsonObject().get("width") != null && e.getAsJsonObject().get("width").getAsInt() != 0) {
 
                        fields.add(e.getAsJsonObject().get("field").getAsString());
 
                        // 设置列宽，列宽是根据叶子节点来的，其他地方定义不生效,l列索引由于上面已经+1了，所以这里要减1
                        sheet.setColumnWidth(_colIndex - 1, e.getAsJsonObject().get("width").getAsInt() * 256);
                    }
                }
 
                cell.setCellStyle(cs);
                cell.setCellValue(e.getAsJsonObject().get("title").getAsString());
                // 判断是否写到最后一个父节点名称
                if (i == count - 1) {
                    // 如果有子节点,递归写入子节点
                    if (e.getAsJsonObject().get("children") != null && e.getAsJsonObject().get("children").getAsJsonArray().size() != 0) {
                        writeTitles(e.getAsJsonObject().get("children").getAsJsonArray(), rowIndex + 1, e.getAsJsonObject().get("title").getAsString());
                    }
                }
 
            }
 
        }
    }
 
    /**
     * 获取当前节点的子节点数量
     * 
     * @param e
     * @return
     */
    private static int getSubNodesCount(JsonElement e) {
        JsonArray array = e.getAsJsonObject().get("children") == null ? null : e.getAsJsonObject().get("children").getAsJsonArray();
        if (array == null)
            return 0;
 
        Iterator<JsonElement> it = array.iterator();
        int count = array.size();
        while (it.hasNext()) {
            int c = getSubNodesCount(it.next());
            count += c > 0 ? c - 1 : c;
        }
        return count;
    }
 
}
ExcelColumn列定义http://www.xdemo.org/poi-customize-columns/ 未经许可，禁止转载
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
package org.xdemo.superutil.thirdparty.excel;
 
import java.util.List;
 
/**
 * Excel列定义
 * @author Goofy
 */
public class ExcelColumn {
 
    //列名
    private String title;
    //列对应的数据中的field
    private String field;
    //列宽
    private int width=0;
    //子列
    private List<ExcelColumn> children;
 
    public ExcelColumn(){}
     
    public ExcelColumn(String title, String field, int width) {
        super();
        this.title = title;
        this.field = field;
        this.width = width;
    }
 
    //省略get和set
 
}
测试代码http://www.xdemo.org/poi-customize-columns/ 未经许可，禁止转载
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
public static void main(String[] args) throws Exception {
 
        List<ExcelColumn> columns = new ArrayList<ExcelColumn>();
 
        ExcelColumn a = new ExcelColumn("A", "A", 20);
 
        ExcelColumn b = new ExcelColumn("B", "B", 20);
 
        List<ExcelColumn> columns2 = new ArrayList<ExcelColumn>();
        columns2.add(new ExcelColumn("C", "C", 30));
        columns2.add(new ExcelColumn("D", "D", 30));
        columns2.add(new ExcelColumn("E", "E", 30));
 
        b.setChildren(columns2);
 
        columns.add(b);
 
        // a.setChildren(columns);
 
        List<ExcelColumn> cs = new ArrayList<ExcelColumn>();
        cs.add(a);
        cs.add(new ExcelColumn("F", "F", 12));
        cs.add(new ExcelColumn("G", "G", 22));
        cs.add(b);
 
        // 造数据
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
 
        Map<String, Object> map1 = new HashMap<String, Object>();
        map1.put("C", "CCC");
        map1.put("D", 22);
        map1.put("E", new Date());
        list.add(map1);
 
        Map<String, Object> map2 = new HashMap<String, Object>();
        map2.put("C", "11");
        map2.put("D", 321);
        map2.put("E", new Date());
        list.add(map2);
 
        Map<String, Object> map3 = new HashMap<String, Object>();
        map3.put("C", "3333");
        map3.put("D", 'd');
        map3.put("E", new BigDecimal(1111));
        list.add(map3);
 
        list.add(map1);
        list.add(map2);
        list.add(map3);
 
        CustomizeToExcel
                .toFile("[{\"title\":\"A\",\"field\":\"A\",\"width\":22},{\"title\":\"B\",\"field\":\"B\",\"width\":20,\"children\":[{\"title\":\"C\",\"field\":\"C\",\"width\":30},{\"title\":\"D\",\"field\":\"D\",\"width\":30},{\"title\":\"E\",\"field\":\"E\",\"width\":30,\"children\":[{\"title\":\"X\",\"field\":\"X\",\"width\":22},{\"title\":\"Y\",\"field\":\"Y\",\"width\":22,\"children\":[{\"title\":\"EE\",\"field\":\"EE\",\"width\":22},{\"title\":\"SS\",\"field\":\"SS\",\"width\":22,\"children\":[{\"title\":\"uu\",\"field\":\"uu\",\"width\":22},{\"title\":\"i\",\"field\":\"i\",\"width\":22},{\"title\":\"o\",\"field\":\"o\",\"width\":22}]}]}]}]},{\"title\":\"Z\",\"field\":\"Z\",\"width\":22}]",
                        list, "D:\\x1.xlsx");
        CustomizeToExcel.toFile(columns, list, "D:\\x2.xlsx");
 
    }


        0        1           
POI自定义表头（合并列）导出Excel工具类
POI自定义表头（合并列）导出Excel工具类
poi导出Excel报表多表头双层表头、合并单元格
poi导出excel合并单元格(包括列合并、行合并)
poi 导出Excel 工具类
POI导出Excel工具类
poi导出Excel工具类
poi导出excel工具类
项目中使用POI导出Excel的一些心得（包含复杂表头和动态页数与列数）
项目中使用POI导出Excel的一些心得（包含复杂表头和动态页数与列数）
POI导出Excel工具
Excel导出工具-POI
POI导出Excel--合并单元格
POI导出Excel 合并单元格
POI导出excel合并单元格
POI导出Excel--合并单元格
poi导出excel 之工具类（一）
Java使用POI导出Excel工具类（反射）
我的Golang学习记录
python 微信公众平台开发
用DirectShow写一个“合并左右声道”的Filter实现小结
linux centos7.2 下配置网络yum 的问题
luci　学习相关的地址
POI自定义表头（合并列）导出Excel工具类
android 6.0调用拨打电话所出现的问题
数据仓库-易错点
tensorflow的官方python的API地址
Flask 部署 heroku
对于如何让u盘考入大于4G的单个文件的问题
手动修改vip所在主机
clipToPadding和clipChildren
HTML5 基础（003_Web SQL Database_1）