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

    /*导出服务*/
    @RequestMapping(value = "/exportMaintenance.html")
    public void exportMaintenance(HttpServletRequest request, HttpServletResponse response) {
        String ids = request.getParameter("ids");
        List<Map> sourceData = null;
        //判断是否导出所有
        if (StringFacs.isEmpty(ids)) {
            //ids为空则导出所有
            sourceData = greenBasiceService.exportMaintenance(request);
        } else {
            sourceData = greenBasiceService.exportMaintenance2(ids);
        }
        CellRangeAddress cellRangeAddress =null;
        //创建Excel管理对象，可读写.xls或.xlsx文件,与MS-Office版本2007或更高版本兼容
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建新的工作簿
        XSSFSheet sheet = workbook.createSheet();
        String[] baseKeyArray = {"序号", "xzqh", "yhpq", "people", "zyrc", "jxtb", "jxczrs", "yhcl", "time", "username", "number", "work", "work_num", "unit"};
        String[] titleData = null;
        titleData = new String[]{"序号", "行政区划", "养护片区", "片区负责人", "作业人次", "机械台班", "机械承载人数", "养护材料", "上报时间", "上报人员", "编号", "工作内容", "工程量", "单位"};
        CTWorksheet sheetX = sheet.getWorkbook().getSheetAt(0).getCTWorksheet();
        //设置首行样式
        XSSFFont fontRow1 = (XSSFFont) workbook.createFont();
        PoiUtils.setXSSFFont(fontRow1, (short) 11, "微软雅黑", true, false, null);
        XSSFCellStyle styleRow1 = (XSSFCellStyle) workbook.createCellStyle();
        PoiUtils.setXSSFCellStyle(styleRow1, fontRow1, true);
        //首行
        XSSFRow row0 = sheet.createRow(0);
        for (int i = 0; i < titleData.length; i++) {
            XSSFCell cell = row0.createCell(i);
            cell.setCellStyle(styleRow1);
            cell.setCellValue(titleData[i]);
        }
        //设置第正文样式
        XSSFFont fontRowMain = (XSSFFont) workbook.createFont();
        PoiUtils.setXSSFFont(fontRowMain, (short) 11, "微软雅黑", false, false, null);
        XSSFCellStyle styleRowMain = (XSSFCellStyle) workbook.createCellStyle();
        PoiUtils.setXSSFCellStyle(styleRowMain, fontRowMain, true);
        if (sourceData.size()>0) {
            for (int i = 0; i < sourceData.size(); i++) {
                Map data = sourceData.get(i);
                XSSFRow mainRow = sheet.createRow(i + 1);
                for (int j = 0; j < titleData.length; j++) {
                    XSSFCell mainCell = mainRow.createCell(j);
                    if (j == 0) {
                        mainCell.setCellValue(i + 1);
                    } else {
                        String value = data.get(baseKeyArray[j]) == null ? "" : data.get(baseKeyArray[j]).toString();
                        mainCell.setCellValue(value);
                    }
                    mainCell.setCellStyle(styleRowMain);
                }
                if (i<sheet.getLastRowNum()) {
                    String stringCellValue = sheet.getRow(i).getCell(10).getStringCellValue();//获取第i行的编号值
                    String stringCellValue2 = sheet.getRow(i+1).getCell(10).getStringCellValue();//获取第i+1行的编号值
                    if(stringCellValue.equals(stringCellValue2)){
                        //编号相同，进行合并
                        for(int l=0;l<11;l++){
                             cellRangeAddress = new CellRangeAddress(i, i+1, l, l);
                            // 重写合并的方法
                            addMergedReigon(sheetX, cellRangeAddress);
                        }

                    }
                }

            }
        }
        OutputStream ouputStream = null;
        String fileName = "日养台账管理" + DateFacs.getMsTime();
        try {
            ouputStream = response.getOutputStream();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1") + ".xlsx");
            response.setHeader("Set-Cookie", "fileDownload=true;path=/");
            workbook.write(ouputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                ouputStream.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                ouputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /*
     * 重写合并单元格方法*/
    private static void addMergedReigon(CTWorksheet sheetX, CellRangeAddress cellRangeAddress) {
        CTMergeCells ctMergeCells;
        if (sheetX.isSetMergeCells()) {
            ctMergeCells = sheetX.getMergeCells();
        } else {
            ctMergeCells = sheetX.addNewMergeCells();
        }

        CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
        ctMergeCell.setRef(cellRangeAddress.formatAsString());
    }