java poi实现Excel多级表头导出

最近碰到一个导出，比较繁琐，也查询了许多博客，在其中一篇博客的基础上修改，实现了最终想要的效果。话不多说，直接上效果图

 上代码：

1.主代码：

        
        List<Map<String, Object>> convertListMap = toListConvertListMap(tableTitleList, list);

        // 第一步，创建一个Workbook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet("sheet");
        // 第三步，设置样式以及字体样式
        XSSFCellStyle titleStyle = createTitleCellStyle(wb);
        XSSFCellStyle headerStyle = createHeadCellStyle(wb);
        XSSFCellStyle contentStyle = createContentCellStyle(wb);
        // 行号
        int rowNum = 0;
       
        // 创建第一页的第一行，索引从0开始
        XSSFRow row0 = sheet.createRow(rowNum++);
        row0.setHeight((short) 600);// 设置行高
        sheet.setColumnWidth(2, 8000);
        sheet.setColumnWidth(3, 8000);
        //第二行
        XSSFRow row2 = sheet.createRow(rowNum++);
        row2.setHeight((short) 700);
        // todo: 第三行
        XSSFRow row3 = sheet.createRow(rowNum++);
        row3.setHeight((short) 700);

        String[] row_one = null;
        String[] row_two = null;
        String[] row_three = null;
        int cellSize = 0;

        if (tableTitleList.size() == 2) {  // 全部
            row_one = new String[]{"序号", "所属模块", "单位名称", "情况统计", "合计", "", "",
                    "测试类型A", "", "", "", "", "", "", "", "", "", "", "",
                    "测试类型B", "", "", "", "", "", "", "", "", "", "", "",};
            row_two = new String[]{"", "", "", "", "合计A", "合计B", "合计C",
                    "测试类型A1", "", "", "",
                    "测试类型A2", "", "", "",
                    "测试类型A3", "", "", "",
                    "测试类型B1", "", "", "",
                    "测试类型B2", "", "", "",
                    "测试类型B3", "", "", ""};

            row_three = new String[]{"", "", "", "", "", "", "",
                    "起床", "洗漱", "吃饭", "上班",
                    "起床", "洗漱", "吃饭", "上班",
                    "起床", "洗漱", "吃饭", "上班",

                    "起床", "洗漱", "吃饭", "上班",
                    "起床", "洗漱", "吃饭", "上班",
                    "起床", "洗漱", "吃饭", "上班",};
            // 合并单元格
            extracted(sheet);
        }

        // 创建第一行单元格
        for (int i = 0; i < row_one.length; i++) {
            XSSFCell c00 = row0.createCell(i);
            c00.setCellValue(row_one[i]);
            c00.setCellStyle(headerStyle);
        }
        // 创建第二行单元格
        for (int i = 0; i < row_two.length; i++) {
            XSSFCell tempCell = row2.createCell(i);
            tempCell.setCellValue(row_two[i]);
            tempCell.setCellStyle(headerStyle);
        }
        // 创建第三行单元格
        for (int i = 0; i < row_three.length; i++) {
            XSSFCell tempCell = row3.createCell(i);
            tempCell.setCellValue(row_three[i]);
            tempCell.setCellStyle(headerStyle);
        }


        // 设置表头单元格的宽度
        tableTitleStyleColumnwidth(sheet);

        // todo： 填充数据
        approvalMethodfillInData(convertListMap, sheet, contentStyle, rowNum, row_three, tableTitleList);
//        approvalMethodfillInData(convertListMap, sheet, contentStyle, rowNum, cellSize, tableTitleList);
        //导出到浏览器下载
        buildExcelDocument("监测数据.xlsx", wb, response);

2.合并单元格

private static void extracted(XSSFSheet sheet) {
        // todo: 合并参数分别为： 起始行，结束行，起始列，结束列
        // 将第一列 的 第一行到第三行合并
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
        // 将第二列的 第一行到第三行合并
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 1, 1));
        //  将第三列的 第一行到第三行合并
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(0, 2, 3, 3));

        // 合计
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 6));
        // 合计下的单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 6, 6));

       
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 18));
     
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 30));

        
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 10));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 11, 14));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 18));

       
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 19, 22));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 23, 26));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 27, 30));
    }

3.设置表头单元格的宽度

private static void tableTitleStyleColumnwidth(XSSFSheet sheet) {

        sheet.setColumnWidth(2, 8000);
        sheet.setColumnWidth(3, 8000);

        sheet.setColumnWidth(7, 3000);
        sheet.setColumnWidth(8, 3000);
        sheet.setColumnWidth(9, 3000);
        sheet.setColumnWidth(10, 3000);

        sheet.setColumnWidth(11, 3000);
        sheet.setColumnWidth(12, 3000);
        sheet.setColumnWidth(13, 3000);
        sheet.setColumnWidth(14, 3000);

        sheet.setColumnWidth(15, 3000);
        sheet.setColumnWidth(16, 3000);
        sheet.setColumnWidth(17, 3000);
        sheet.setColumnWidth(18, 3000);

        sheet.setColumnWidth(19, 3000);
        sheet.setColumnWidth(20, 3000);
        sheet.setColumnWidth(21, 3000);
        sheet.setColumnWidth(22, 3000);

        sheet.setColumnWidth(23, 3000);
        sheet.setColumnWidth(24, 3000);
        sheet.setColumnWidth(25, 3000);
        sheet.setColumnWidth(26, 3000);

        sheet.setColumnWidth(27, 3000);
        sheet.setColumnWidth(28, 3000);
        sheet.setColumnWidth(29, 3000);
        sheet.setColumnWidth(30, 3000);
    }

4.填充数据（注：我这里的数据格式是List<Map<String, Object>>类型，可以根据自己的实际情况来封装数据）

private static void approvalMethodfillInData(List<Map<String, Object>> convertListMap, XSSFSheet sheet, XSSFCellStyle contentStyle, int rowNum, String[] cellSize, List<Object> tableTitleList) {

        for (Map<String, Object> map : convertListMap) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);

            // 判断选择类型
            if (tableTitleList.size() == 2 || "enterprise".equals(tableTitleList.get(0).toString())) {
                // 循环单元格填入数据
                for (int i = 0; i < cellSize.length; i++) {
                    //列宽自适应，j为自适应的列，true就是自适应，false就是不自适应，默认不自适应
                    sheet.autoSizeColumn(i, true);
                    XSSFCell tempCell = tempRow.createCell(i);
                    tempCell.setCellStyle(contentStyle);
                    String tempValue = "";
                    switch (i) {
                        case 0:
                            tempValue = map.get("indexNum").toString();
                            break;
                        case 1:
                            tempValue = map.get("platName").toString();
                            break;
                        case 2:
                            tempValue = map.get("deptName").toString();
                            break;
                        case 3:
                            tempValue = map.get("type").toString();
                            break;
                        case 4:
                            tempValue = map.get("totalProNum").toString();
                            break;
                        case 5:
                            tempValue = map.get("totalBidNum").toString();
                            break;
                        case 6:
                            tempValue = map.get("totalAmount").toString();
                            break;
                        case 7:   
                            tempValue = map.get("enterpriseEngineeringProNum").toString();
                            break;
                        case 8:
                            tempValue = map.get("enterpriseEngineeringBidNum").toString();
                            break;
                        case 9:
                            tempValue = map.get("enterprisePurchaseAmount").toString();
                            break;
                        case 10:
                            tempValue = map.get("planEnterprisePurchaseAmount").toString();
                            break;
                        case 11:   
                            tempValue = map.get("enterprisePurchaseProNum").toString();
                            break;
                        case 12:
                            tempValue = map.get("enterprisePurchaseBidNum").toString();
                            break;
                        case 13:
                            tempValue = map.get("enterprisePurchaseAmount").toString();
                            break;
                        case 14:
                            tempValue = map.get("planEnterprisePurchaseAmount").toString();
                            break;
                        case 15:  
                            tempValue = map.get("enterpriseServiceProNum").toString();
                            break;
                        case 16:
                            tempValue = map.get("enterpriseServiceBidNum").toString();
                            break;
                        case 17:
                            tempValue = map.get("enterpriseServiceAmount").toString();
                            break;
                        case 18:
                            tempValue = map.get("planEnterpriseServiceAmount").toString();
                            break;

                        case 19:  
                            tempValue = map.get("groupEngineeringProNum").toString();
                            break;
                        case 20:
                            tempValue = map.get("grouppurchaseBidNum").toString();
                            break;
                        case 21:
                            tempValue = map.get("groupPurchaseAmount").toString();
                            break;
                        case 22:
                            tempValue = map.get("palnGroupPurchaseAmount").toString();
                            break;
                        case 23: 
                            tempValue = map.get("groupPurchaseProNum").toString();
                            break;
                        case 24:
                            tempValue = map.get("groupEngineeringBidNum").toString();
                            break;
                        case 25:
                            tempValue = map.get("groupEngineeringAmount").toString();
                            break;
                        case 26:
                            tempValue = map.get("planGroupEngineeringAmount").toString();
                            break;
                        case 27: 
                            tempValue = map.get("groupServiceProNum").toString();
                            break;
                        case 28:
                            tempValue = map.get("groupServiceBidNum").toString();
                            break;
                        case 29:
                            tempValue = map.get("groupServiceAmount").toString();
                            break;
                        case 30:
                            tempValue = map.get("planGroupServiceAmount").toString();
                            break;
                    }
                    tempCell.setCellValue(tempValue);
                }
            }
}

5.web下载，导出到浏览器

private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
        try {
            response.setContentType("application/octet-stream");
            // 可自行定义编码格式
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            //清除jsp编译html文件的空白,防止excel出现空行
            response.flushBuffer();
            //写出
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }

6.提供表头样式，内容样式以及标题样式

 /**
     * 创建标题样式
     *
     * @param wb
     * @return
     */
    private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色

        XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
        return cellStyle;
    }

    /**
     * 创建表头样式
     *
     * @param wb
     * @return
     */
    private static XSSFCellStyle createHeadCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    /**
     * 创建内容样式
     *
     * @param wb
     * @return
     */
    private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成12号字体
        XSSFFont font = wb.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        return cellStyle;
    }

到此，大功告成！如果有好的实现方法，欢迎交流！！！

参考博客：java导出功能（多级表头，复杂表头）_java导出excel复杂表头_IT行业小趴菜的博客-CSDN博客