java poi实现Excel多级表头导出(多级表头,复杂表头)

文章讲述了如何使用Java编程语言,通过ApachePOI库操作XSSFWorkbook和XSSF类,实现Excel表格的导出,包括创建工作簿、添加sheet、设置样式、合并单元格、填充数据以及处理多级和复杂表头。
摘要由CSDN通过智能技术生成

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

 上代码:

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博客

实现动态表头导出,你需要先确定表头的数据和结构,然后使用 POI 库动态生成表头和数据。 以下是实现动态表头导出的基本步骤: 1. 创建工作簿对象 XSSFWorkbook。 ```java XSSFWorkbook workbook = new XSSFWorkbook(); ``` 2. 创建工作表对象 XSSFSheet。 ```java XSSFSheet sheet = workbook.createSheet("Sheet1"); ``` 3. 创建表头行对象 XSSFRow。 ```java XSSFRow headerRow = sheet.createRow(0); ``` 4. 创建表头单元格对象 XSSFCell。 ```java XSSFCell headerCell = headerRow.createCell(0); ``` 5. 设置表头单元格的值和样式。 ```java headerCell.setCellValue("表头1"); XSSFCellStyle headerCellStyle = workbook.createCellStyle(); headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerCellStyle.setAlignment(HorizontalAlignment.CENTER); headerCell.setCellStyle(headerCellStyle); ``` 6. 动态生成表头和数据。 ```java // 假设表头和数据都保存在一个 Map 对象中 Map<String, List<String>> dataMap = new HashMap<>(); // 生成表头 int columnIndex = 0; for (String header : dataMap.keySet()) { XSSFCell headerCell = headerRow.createCell(columnIndex++); headerCell.setCellValue(header); XSSFCellStyle headerCellStyle = workbook.createCellStyle(); headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerCellStyle.setAlignment(HorizontalAlignment.CENTER); headerCell.setCellStyle(headerCellStyle); } // 生成数据 int rowIndex = 1; for (List<String> dataList : dataMap.values()) { XSSFRow dataRow = sheet.createRow(rowIndex++); columnIndex = 0; for (String data : dataList) { XSSFCell dataCell = dataRow.createCell(columnIndex++); dataCell.setCellValue(data); } } ``` 7. 导出 Excel 文件。 ```java try (FileOutputStream outputStream = new FileOutputStream("data.xlsx")) { workbook.write(outputStream); } ``` 以上就是实现动态表头导出的基本步骤。你可以根据自己的需求自定义表头和数据的生成逻辑。
评论 11
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值