服务器之家

服务器之家 > 正文

Java实现生成Excel树形表头完整代码示例

时间:2021-03-01 13:46     来源/作者:zhangzeyuaaa

本文主要分享了Java实现生成Excel树形表头完整代码示例,没有什么好解释的,直接看看代码过程。

源数据格式:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
String[] targetNames = {
        "指标名称"
        "单位"
        "xx_yy1"
        "xx_yy2_zz1"
        "xx_yy2_zz2",
        "2017年5月_主营业务收入_累计", "2017年5月_主营业务收入_同比"
        "2017年5月_主营业务收入_本月", "2017年5月_主营业务收入_环比"
        "2017年5月_利润_累计", "2017年5月_利润_同比", "2017年5月_利润_本月", "2017年5月_利润_环比",
        "2017年6月_主营业务收入_累计", "2017年6月_主营业务收入_同比"
        "2017年6月_主营业务收入_本月", "2017年6月_主营业务收入_环比"
        "2017年6月_利润_累计", "2017年6月_利润_同比", "2017年6月_利润_本月", "2017年6月_利润_环比"
      };

生成如下Excel:

Java实现生成Excel树形表头完整代码示例

第一行不属于树形表头。

代码

SplitCell:

?
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
package com.zzj.excel;
public class SplitCell {
    private String key;
    private String parentKey;
    private String value;
    private int columnIndex;
    private int rowIndex;
    public SplitCell() {
    }
    public SplitCell(String key, String value) {
        this.key = key;
        this.value = value;
    }
    public SplitCell(String key, String parentKey, String value, 
          int columnIndex, int rowIndex) {
        this.key = key;
        this.parentKey = parentKey;
        this.value = value;
        this.columnIndex = columnIndex;
        this.rowIndex = rowIndex;
    }
    public String getKey() {
        return key;
    }
    public void setKey(String key) {
        this.key = key;
    }
    public String getParentKey() {
        return parentKey;
    }
    public void setParentKey(String parentKey) {
        this.parentKey = parentKey;
    }
    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }
    public int getColumnIndex() {
        return columnIndex;
    }
    public void setColumnIndex(int columnIndex) {
        this.columnIndex = columnIndex;
    }
    public int getRowIndex() {
        return rowIndex;
    }
    public void setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
    }
    @Override
      public String toString() {
        return "CellContent [key=" + key + ", parentKey=" + parentKey + ", value=" + value + ", columnIndex="
                + columnIndex + ", rowIndex=" + rowIndex + "]";
    }
}

MergedCell:

?
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
package com.zzj.excel;
public class MergedCell {
    private String key;
    private String parentKey;
    private String value;
    private int startC;
    private int endC;
    private int startR;
    private int endR;
    private Boolean leaf = true;
    // 默认叶子节点
    public String getKey() {
        return key;
    }
    public void setKey(String key) {
        this.key = key;
    }
    public String getParentKey() {
        return parentKey;
    }
    public void setParentKey(String parentKey) {
        this.parentKey = parentKey;
    }
    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }
    public int getStartC() {
        return startC;
    }
    public void setStartC(int startC) {
        this.startC = startC;
    }
    public int getEndC() {
        return endC;
    }
    public void setEndC(int endC) {
        this.endC = endC;
    }
    /**
   * 单元格合并结束列索引自增
   */
    public void incEndC(){
        this.endC++;
    }
    public int getStartR() {
        return startR;
    }
    public void setStartR(int startR) {
        this.startR = startR;
    }
    public int getEndR() {
        return endR;
    }
    public void setEndR(int endR) {
        this.endR = endR;
    }
    public Boolean isLeaf() {
        return leaf;
    }
    public void setLeaf(Boolean leaf) {
        this.leaf = leaf;
    }
    @Override
      public String toString() {
        return "CellInfo [key=" + key + ", parentKey=" + parentKey + ", value=" + value + ", startC=" + startC
                + ", endC=" + endC + ", startR=" + startR + ", endR=" + endR + ", leaf=" + leaf + "]";
    }
}

CellTransformer:

?
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
package com.zzj.excel;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class CellTransformer {
    private final List<SplitCell> cellContents;
    private final int firstRowIndex;
    private final int rowSize;
    private Map<String, MergedCell> cellInfoMap = new LinkedHashMap<String, MergedCell>();
    public CellTransformer(List<SplitCell> cellContents, int firstRowIndex, int rowSize) {
        this.cellContents = cellContents;
        this.firstRowIndex = firstRowIndex;
        this.rowSize = rowSize;
    }
    public Map<String, MergedCell> transform(){
        cellInfoMap.clear();
        for (SplitCell cellContent : cellContents) {
            MergedCell cellInfo = cellInfoMap.get(cellContent.getKey());
            if (cellInfo == null) {
                cellInfo = convertToCellInfo(cellContent);
                cellInfoMap.put(cellInfo.getKey(), cellInfo);
            } else {
                /* 单元格出现多少次,则该单元格就合并多少列 */
                cellInfo.incEndC();
                // 列结束索引自增(用于列合并)
                cellInfo.setLeaf(false);
                // 只要重复出现,则为非叶子节点
            }
        }
        // 行合并
        for (MergedCell cellInfo : cellInfoMap.values()) {
            if (cellInfo.isLeaf()) {
                // 如果为叶子节点,则一定合并到最后一行
                cellInfo.setEndR(firstRowIndex + rowSize - 1);
            }
        }
        return cellInfoMap;
    }
    private MergedCell convertToCellInfo(SplitCell cellContent){
        MergedCell cellInfo = new MergedCell();
        cellInfo.setKey(cellContent.getKey());
        cellInfo.setParentKey(cellContent.getParentKey());
        cellInfo.setValue(cellContent.getValue());
        cellInfo.setStartC(cellContent.getColumnIndex());
        // 结束索引默认为开始索引
        cellInfo.setEndC(cellContent.getColumnIndex());
        cellInfo.setStartR(cellContent.getRowIndex());
        // 结束索引默认为开始索引
        cellInfo.setEndR(cellContent.getRowIndex());
        return cellInfo;
    }
}

测试

?
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
package com.zzj.excel;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class Main {
    private static final String SEPARATOR = "_";
    public static void main(String[] args) throws Exception {
        String[] targetNames = {
                "指标名称"
                "单位"
                "xx_yy1"
                "xx_yy2_zz1"
                "xx_yy2_zz2",
                "2017年5月_主营业务收入_累计", "2017年5月_主营业务收入_同比"
                "2017年5月_主营业务收入_本月", "2017年5月_主营业务收入_环比"
                "2017年5月_利润_累计", "2017年5月_利润_同比", "2017年5月_利润_本月", "2017年5月_利润_环比",
                "2017年6月_主营业务收入_累计", "2017年6月_主营业务收入_同比"
                "2017年6月_主营业务收入_本月", "2017年6月_主营业务收入_环比"
                "2017年6月_利润_累计", "2017年6月_利润_同比", "2017年6月_利润_本月", "2017年6月_利润_环比"
              };
        // 设第一行不属于树形表头
        String[] extraNames = new String[targetNames.length];
        for (int i = 0; i < extraNames.length; i++) {
            extraNames[i] = "extra" + i;
        }
        final int firstTreeRowIndex = 1;
        int rowSize = getRowSize(targetNames);
        List<SplitCell> cellContents = new ArrayList<>();
        for (int i = 0; i < targetNames.length; i++) {
            String[] values = targetNames[i].split(SEPARATOR);
            for (int j = 0; j < values.length; j++) {
                String value = values[j];
                String key = getKey(values, j);
                String parentKey = getParentKey(values, j);
                SplitCell cellContent = new SplitCell(key, parentKey, value, 
                            i, j + firstTreeRowIndex);
                cellContents.add(cellContent);
            }
        }
        WritableWorkbook workbook = Workbook.createWorkbook(new File("F:\\template.xls"));
        CellFormat cellFormat = getCellFormat();
        WritableSheet sheet = workbook.createSheet("template", 0);
        // 第一行
        for (int i = 0; i < extraNames.length; i++) {
            Label label = new Label(i, 0, extraNames[i], cellFormat);
            sheet.addCell(label);
        }
        // 树形表头
        CellTransformer cellInfoManager = new CellTransformer(cellContents, firstTreeRowIndex, rowSize);
        Map<String, MergedCell> map = cellInfoManager.transform();
        for (MergedCell cellInfo : map.values()) {
            Label label = new Label(cellInfo.getStartC(), 
                      cellInfo.getStartR(), cellInfo.getValue(), cellFormat);
            if (cellInfo.getStartC() != cellInfo.getEndC()
                      || cellInfo.getStartR() != cellInfo.getEndR()) {
                sheet.mergeCells(cellInfo.getStartC(), cellInfo.getStartR(), 
                            cellInfo.getEndC(), cellInfo.getEndR());
            }
            sheet.addCell(label);
        }
        workbook.write();
        workbook.close();
        System.out.println("导出成功!");
    }
    private static CellFormat getCellFormat() throws Exception{
        WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
        WritableCellFormat cellFormat = new WritableCellFormat();
        cellFormat.setFont(font);
        cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
        cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        cellFormat.setWrap(false);
        return cellFormat;
    }
    private static int getRowSize(String[] targetNames) {
        int rowSize = 0;
        for (String t : targetNames) {
            rowSize = Math.max(rowSize, t.split(SEPARATOR).length);
        }
        return rowSize;
    }
    private static String getKey(String[] values, int index){
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < (index + 1); i++) {
            sb.append(values[i] + SEPARATOR);
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }
    private static String getParentKey(String[] values, int index){
        if (index == 0) {
            return null;
        }
        return getKey(values, index - 1);
    }
}

总结

以上就是本文关于Java实现生成Excel树形表头完整代码示例的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题,如有不足之处,欢迎留言指出。感谢朋友们对本站的支持!

原文链接:http://blog.csdn.net/zhangzeyuaaa/article/details/74906579

标签:

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
返回顶部