java 使用 poi 导出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
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
import com.maidanli.common.utils.DateUtil;
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.ByteArrayOutputStream;
import java.lang.reflect.Method;
import java.util.*;
import java.util.Map.Entry;

public class OfficeUtil {


    /**
     * @param dataList    数据
     * @param headNameMap 标题
     * @param type        类型 1 xls 2 xlsx
     * @param mergeIndex  需要合并的列 从1开始  0是序号
     * @param benchmarkColumn 基准列（就是以那一列为标准来决定合不合并 以下面的例子为说明 我输入1 就是以订单号为准 订单号合并才决定合并）
     * @return
     * @throws Exception
     */
    public static byte[] toExcelMergeCell(List<?> dataList, Map<String, String> headNameMap, int type, int[] mergeIndex, Integer benchmarkColumn) throws Exception {
        Workbook workbook;
        if (type == 1) {
            workbook = new XSSFWorkbook();
        } else if (type == 2) {
            workbook = new SXSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }
        List<Method> methodList = null;
        Sheet sheet = workbook.createSheet("数据列表");
        sheet.setColumnWidth(2, 25000);
        int index = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < dataList.size(); i++) {
            Object object = dataList.get(i);
            if (methodList == null) {
                Method[] methods = object.getClass().getMethods();
                methodList = new ArrayList<>();
                Row rowHead = sheet.createRow(index);
                rowHead.createCell(0).setCellValue("序号");
                Iterator<Entry<String, String>> iterator = headNameMap.entrySet().iterator();
                int c = 1;
                while (iterator.hasNext()) {
                    Entry<String, String> entry = iterator.next();
                    for (int m = 0; m < methods.length; m++) {
                        if (methods[m].getName().toLowerCase().equals(("get" + entry.getKey()).toLowerCase())) {
                            methodList.add(methods[m]);
                            Cell cell = rowHead.createCell(c);
                            setCellValue(cell, entry.getValue());
                            c++;
                        }
                        if (methods[m].getName().toLowerCase().equals(("getlist"))) {
                            Object invoke = methods[m].invoke(object);
                        }
                    }
                }
            }
            Row row = sheet.createRow(index + 1);
            row.createCell(0).setCellValue(i + 1);
            for (int m = 0; m < methodList.size(); m++) {
                Object value = methodList.get(m).invoke(object);
                Cell cell = row.createCell(m + 1);
                Object textValue = getValue(value);
                setCellValue(cell, textValue);

            }
            index++;
        }
        String str = null;
        int strBeginIndex;
        int strEndIndex;
        int j;
        int start;
        int end = 0;
        Map<Integer, Integer> benchmarkMap = new LinkedHashMap<>();
        for (int i = 0; i < mergeIndex.length; i++) {
            j = 0;
            start = 0;
            strBeginIndex = 0;
            strEndIndex = 0;
            if (mergeIndex[i] >= 11 && mergeIndex[i] <= 12) {
                for (Integer endIndex : benchmarkMap.keySet()) {
                    CellRangeAddress region = new CellRangeAddress(benchmarkMap.get(endIndex), endIndex, mergeIndex[i], mergeIndex[i]);
                    sheet.addMergedRegion(region);
                }
            }
            for (Row row : sheet) {
                if (j == 0) {
                    j++;
                    continue;
                }

                if (Objects.isNull(str)) {
                   
                    if (Objects.nonNull(row.getCell(mergeIndex[i]))) {
                        str = row.getCell(mergeIndex[i]).getStringCellValue();
                    }eles{
                        continue;
                    }
                    if (str.equals(sheet.getRow(2).getCell(1).getStringCellValue())) {
                        strBeginIndex = row.getRowNum();
                    }
                } else if (str.equals(row.getCell(mergeIndex[i]).getStringCellValue())) {
                    if (strBeginIndex == 0) {
                        strBeginIndex = sheet.getRow(j - 1).getRowNum();
                    }
                    strEndIndex = sheet.getLastRowNum();
                    end = strEndIndex;
                    if (sheet.getLastRowNum() == j) {
                        //末尾合并
                        if (mergeIndex[i] == benchmarkColumn) {
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        } else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {
                            consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);
                        }
                        if (mergeIndex[i] == 1) {
                            benchmarkMap.put(strEndIndex, strBeginIndex);
                        }
                    }
                } else if (!str.equals(row.getCell(mergeIndex[i]).getStringCellValue())) {
                    strEndIndex = row.getRowNum();
                    if (start == 0 && strBeginIndex > 0 && strEndIndex > 0) {
                        //首行合并
                        strEndIndex = strEndIndex - 1;
                        end = strEndIndex;
                        if (mergeIndex[i] == benchmarkColumn) {
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        } else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {
                            consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);
                        }
                        if (mergeIndex[i] == 1) {
                            benchmarkMap.put(strEndIndex, strBeginIndex);
                        }
                        strBeginIndex = 0;
                        start = 1;
                    } else if (strBeginIndex > 0 && strEndIndex > 0) {
                        //中间行合并
                        strEndIndex = strEndIndex - 1;
                        end = strEndIndex;
                        if (mergeIndex[i] == benchmarkColumn) {
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        } else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {
                            consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);
                        }
                        if (mergeIndex[i] == 1) {
                            benchmarkMap.put(strEndIndex, strBeginIndex);
                        }
                        strBeginIndex = 0;

                    }
                    str = row.getCell(mergeIndex[i]).getStringCellValue();
                }
                j++;
            }
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook.close();
        return baos.toByteArray();
    }

private static void consolidatedColumn(Map<Integer,Integer> benchmarkMap,Integer strBeginIndex,Integer strEndIndex,Integer mergeIndex,Integer end,Sheet sheet){
        for (Integer endIndex : benchmarkMap.keySet()) {
            if (strBeginIndex>=benchmarkMap.get(endIndex)&&strEndIndex>=endIndex&&strBeginIndex<endIndex){
                strEndIndex=endIndex;
                if (strBeginIndex<strEndIndex){
                    CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex,  mergeIndex);
                    sheet.addMergedRegion(region);
                }
                strBeginIndex=strEndIndex+1;
                strEndIndex=end;
            }else if (strBeginIndex>=benchmarkMap.get(endIndex)&&strEndIndex<=endIndex&&strBeginIndex<endIndex){
                if (strBeginIndex<strEndIndex){
                    CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex,  mergeIndex);
                    sheet.addMergedRegion(region);
                }
                strBeginIndex=strEndIndex+1;
                strEndIndex=end;
            }else if (strBeginIndex<benchmarkMap.get(endIndex)&&strEndIndex>=endIndex&&strBeginIndex<endIndex){
                strBeginIndex=benchmarkMap.get(endIndex);
                strEndIndex=endIndex;
                if (strBeginIndex<strEndIndex){
                    CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex,  mergeIndex);
                    sheet.addMergedRegion(region);
                }
                strBeginIndex=strEndIndex+1;
                strEndIndex=end;
            }else if (strBeginIndex<=benchmarkMap.get(endIndex)&&strEndIndex<=endIndex&&strBeginIndex<endIndex){
                if (!isSection(benchmarkMap,strBeginIndex)){
                    strBeginIndex=benchmarkMap.get(endIndex);
                    if (strBeginIndex<strEndIndex){
                        CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex,  mergeIndex);
                        sheet.addMergedRegion(region);

                    }
                    strBeginIndex=strEndIndex+1;
                    strEndIndex=end;
                }

            }
        }
    }

    private static Object getValue(Object value) {
        Object textValue = "";
        if (value != null) {
            if (value instanceof Boolean) {
                textValue = (Boolean) value ? "是" : "否";
            } else if (value instanceof Date) {
                textValue = DateUtil.format((Date) value, "yyyy-MM-dd HH:mm:ss");
            } else if (value instanceof String) {
                String val = (String) value;
                textValue = Objects.isNull(val) || "null".equalsIgnoreCase(val) ? "" : val;
            } else {
                textValue = value;
            }
        }
        return textValue;
    }
private static boolean isSection(Map<Integer,Integer> benchmarkMap,Integer value){
        for (Integer integer : benchmarkMap.keySet()) {
            if (value>=benchmarkMap.get(integer)&&value<=integer){
                return true;
            }
        }
        return false;
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value != null) {
            if (value instanceof Integer) {
                cell.setCellValue((Integer) value);
            } else if (value instanceof Boolean) {
                Boolean booleanValue = (Boolean) value;
                cell.setCellValue(booleanValue);
            } else if (value instanceof Date) {
                Date dateValue = (Date) value;
                cell.setCellValue(dateValue);
            } else if (value instanceof Float) {
                Float floatValue = (Float) value;
                cell.setCellValue(floatValue);
            } else if (value instanceof Double) {
                Double doubleValue = (Double) value;
                cell.setCellValue(doubleValue);
            } else if (value instanceof Long) {
                Long longValue = (Long) value;
                cell.setCellValue(longValue);
            }
            else {
                cell.setCellValue(value.toString());
            }
        }
    }

    //接口调用示例

    @ApiOperation("物流列表导出")
    @PostMapping("/on/line/export")
    public AccessResult<String> exportOrderOnLineListDTO(HttpServletResponse response, @RequestBody OrderOnLineExcelQueryDTO queryDTO) throws Exception {
        List<OrderOnLineExcelListDTO> list = orderManageService.getOrderOnLineListDTO(queryDTO);
        if (list.isEmpty()) {
            return AccessResult.initFailure("99999", "暂无数据");
        } else {
            Map<String, String> headNameMap = new LinkedHashMap<>();
            //key对象属性字段  valuel excel列标题
            headNameMap.put("orderNo", "订单编号");
            headNameMap.put("addTime", "下单时间");
            headNameMap.put("goodsProducerName", "厂家");
            headNameMap.put("goodsName", "商品名称");
            headNameMap.put("specItemName", "商品属性");
            headNameMap.put("quantitys", "商品数量");
            headNameMap.put("name", "收件人姓名");
            headNameMap.put("phone", "收件人手机");
            headNameMap.put("address", "收件人地址");
            headNameMap.put("courierSn", "快递单号");
            headNameMap.put("courierName", "快递公司名称");
            byte[] excelBytes = OfficeUtil.toExcelMergeCell(list, headNameMap, 1,new int[]{1,2,3,8,9,10,11,12},1);

            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("物流列表.xls", "utf-8"));
            response.getOutputStream().write(excelBytes);
            response.getOutputStream().flush();
            response.getOutputStream().close();
            return null;
        }
    }
}

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
public class Model {
    private String orderNo;
    private String changjia;
    private String goods;

    public Model(String orderNo, String changjia, String goods) {
        this.orderNo = orderNo;
        this.changjia = changjia;
        this.goods = goods;
    }

    public String getOrderNo() {        return orderNo;
    }

    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }

    public String getChangjia() {
        return changjia;
    }

    public void setChangjia(String changjia) {
        this.changjia = changjia;
    }

    public String getGoods() {
        return goods;
    }

    public void setGoods(String goods) {
        this.goods = goods;
    }
}