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
package com;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;


public class ExcelTest {
    /**
     * 写入excel数据
     *
     * @param path    excel保存路径
     * @param name    excel名称
     * @param version excel格式，支持xls、xlsx
     * @param rows    excel单元格信息（包含位置、值等信息,rows中每个Json必须包含位置和值的信息，
     *                位置:firstRow，lastRow，firstCol，lastCol，值:value）
     */
    public static void writeMergeExcel(String path, String name, String version,
                                       List<JSONObject> rows) {
        try {
            Workbook wb = produce(version);
            createSheet(wb, name, version, rows,null);
            String filePath = path + name;
            if (!filePath.endsWith(version)) {
                filePath = filePath +"."+ version;
            }
            FileOutputStream fileOut = new FileOutputStream(filePath);
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 写入excel数据
     *
     * @param path    excel保存路径
     * @param name    excel名称
     * @param version excel格式，支持xls、xlsx
     * @param rows    excel单元格信息（包含位置、值等信息）
     * @param rowKeys excel单元格字段信息（rows中每个Json必须包含位置和值的信息，如果存储的字段与工具中的字段不一致，做个映射，工具中的字段：:firstRow，lastRow，firstCol，lastCol，值:value）
     */
    public static void writeMergeExcel(String path, String name, String version,
                                       List<JSONObject> rows, JSONObject rowKeys) {
        try {
            Workbook wb = produce(version);
            createSheet(wb, name, version, rows,  rowKeys);
            String filePath = path + name;
            if (!filePath.endsWith(version)) {
                filePath = filePath +"."+ version;
            }
            FileOutputStream fileOut = new FileOutputStream(filePath);
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void createSheet(Workbook wb, String name, String version,
                                    List<JSONObject> rows, JSONObject rowKeys) {
        Integer max = getMaxRowNumber(version);
        int sheetNum = 1;
        Sheet sheet = wb.createSheet(name + sheetNum);

        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);//设置水平对齐方式
        style.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐方式

        String firstRowKey = rowKeys != null ? rowKeys.getString("firstRow"):"firstRow";
        String lastRowKey  = rowKeys != null ? rowKeys.getString("lastRow"):"lastRow";
        String firstColKey = rowKeys != null ? rowKeys.getString("firstCol"):"firstCol";
        String lastColKey  = rowKeys != null ? rowKeys.getString("lastCol"):"lastCol";
        String valueKey    = rowKeys != null ? rowKeys.getString("value"):"value";
        for (JSONObject rowJson : rows) {
            int lastRow  = rowJson.getInteger(lastRowKey);
            if (lastRow > max){
                List<JSONObject> subRows = rows.subList(lastRow,rows.size());
                createSheet(wb, name, version, subRows,  rowKeys);
            } else{
                int firstRow = rowJson.getInteger(firstRowKey);
                int firstCol = rowJson.getInteger(firstColKey);
                int lastCol  = rowJson.getInteger(lastColKey);
                System.out.println(firstRow+"_"+lastRow +"_"+ firstCol +"_"+ lastCol);
                if (lastCol - firstCol > 0 || lastRow - firstRow > 0 ){
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
                    sheet.addMergedRegion(cellRangeAddress);
                }
                Row row ;
                if (sheet.getRow(firstRow)!=null){
                    row = sheet.getRow(firstRow);
                } else {
                    row = sheet.createRow(firstRow);
                }
                Cell cell = row.createCell(firstCol);
                cell.setCellValue(rowJson.getString(valueKey));
                cell.setCellStyle(style);
            }
        }
    }
    public static Workbook produce(String version) {
        switch (version) {
            case "xls":
                return new HSSFWorkbook();
            case "xlsx":
                return new XSSFWorkbook();
            case "sxlsx":
                return new SXSSFWorkbook();
            default:
                return null;
        }
    }

    private static int maxXls = 65024;
    private static int maxXlsx = 1048576;
    public static Integer getMaxRowNumber(String version) {
        switch (version) {
            case "xls":
                return maxXls;
            case "xlsx":
                return maxXlsx;
            case "sxlsx":
                return maxXlsx;
            default:
                return null;
        }
    }
    public static void main(String[] args) {
        try {
            List<JSONObject> list = (List<JSONObject>) JSONObject.parse("[{"firstRow":0,"lastRow":6,"lastCol":0,"firstCol":0,"value":"秦时明月汉时关"},{"firstRow":0,"lastRow":0,"lastCol":1,"firstCol":1,"value":"万"},{"firstRow":1,"lastRow":1,"lastCol":1,"firstCol":1,"value":"里"},{"firstRow":2,"lastRow":2,"lastCol":1,"firstCol":1,"value":"长"},{"firstRow":3,"lastRow":3,"lastCol":1,"firstCol":1,"value":"征"},{"firstRow":4,"lastRow":4,"lastCol":1,"firstCol":1,"value":"人"},{"firstRow":5,"lastRow":5,"lastCol":1,"firstCol":1,"value":"未"},{"firstRow":6,"lastRow":6,"lastCol":1,"firstCol":1,"value":"还"},{"firstRow":3,"lastRow":3,"lastCol":2,"firstCol":2,"value":"但使龙城飞将在"},{"firstRow":0,"lastRow":0,"lastCol":3,"firstCol":3,"value":"不"},{"firstRow":1,"lastRow":1,"lastCol":3,"firstCol":3,"value":"教"},{"firstRow":2,"lastRow":2,"lastCol":3,"firstCol":3,"value":"胡"},{"firstRow":3,"lastRow":3,"lastCol":3,"firstCol":3,"value":"马"},{"firstRow":4,"lastRow":4,"lastCol":3,"firstCol":3,"value":"度"},{"firstRow":5,"lastRow":5,"lastCol":3,"firstCol":3,"value":"阴"},{"firstRow":6,"lastRow":6,"lastCol":3,"firstCol":3,"value":"山"}]");
            writeMergeExcel("E:\","mergeExcel", "xls" ,list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}