devbox
小桥流水78
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

java poi实现Excel多级表头导出（多级表头，复杂表头）_java导出excel多级表头

作者：小桥流水78 | 2024-07-12 15:56:21

赞

踩

java导出excel多级表头

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

 上代码：

1.主代码：

  1.         
  2.         List<Map<String, Object>> convertListMap = toListConvertListMap(tableTitleList, list);
  3.  
  4.         // 第一步，创建一个Workbook，对应一个Excel文件
  5.         XSSFWorkbook wb = new XSSFWorkbook();
  6.         // 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet
  7.         XSSFSheet sheet = wb.createSheet("sheet");
  8.         // 第三步，设置样式以及字体样式
  9.         XSSFCellStyle titleStyle = createTitleCellStyle(wb);
  10.         XSSFCellStyle headerStyle = createHeadCellStyle(wb);
  11.         XSSFCellStyle contentStyle = createContentCellStyle(wb);
  12.         // 行号
  13.         int rowNum = 0;
  14.        
  15.         // 创建第一页的第一行，索引从0开始
  16.         XSSFRow row0 = sheet.createRow(rowNum++);
  17.         row0.setHeight((short) 600);// 设置行高
  18.         sheet.setColumnWidth(2, 8000);
  19.         sheet.setColumnWidth(3, 8000);
  20.         //第二行
  21.         XSSFRow row2 = sheet.createRow(rowNum++);
  22.         row2.setHeight((short) 700);
  23.         // todo: 第三行
  24.         XSSFRow row3 = sheet.createRow(rowNum++);
  25.         row3.setHeight((short) 700);
  26.  
  27.         String[] row_one = null;
  28.         String[] row_two = null;
  29.         String[] row_three = null;
  30.         int cellSize = 0;
  31.  
  32.         if (tableTitleList.size() == 2) {  // 全部
  33.             row_one = new String[]{"序号", "所属模块", "单位名称", "情况统计", "合计", "", "",
  34.                     "测试类型A", "", "", "", "", "", "", "", "", "", "", "",
  35.                     "测试类型B", "", "", "", "", "", "", "", "", "", "", "",};
  36.             row_two = new String[]{"", "", "", "", "合计A", "合计B", "合计C",
  37.                     "测试类型A1", "", "", "",
  38.                     "测试类型A2", "", "", "",
  39.                     "测试类型A3", "", "", "",
  40.                     "测试类型B1", "", "", "",
  41.                     "测试类型B2", "", "", "",
  42.                     "测试类型B3", "", "", ""};
  43.  
  44.             row_three = new String[]{"", "", "", "", "", "", "",
  45.                     "起床", "洗漱", "吃饭", "上班",
  46.                     "起床", "洗漱", "吃饭", "上班",
  47.                     "起床", "洗漱", "吃饭", "上班",
  48.  
  49.                     "起床", "洗漱", "吃饭", "上班",
  50.                     "起床", "洗漱", "吃饭", "上班",
  51.                     "起床", "洗漱", "吃饭", "上班",};
  52.             // 合并单元格
  53.             extracted(sheet);
  54.         }
  55.  
  56.         // 创建第一行单元格
  57.         for (int i = 0; i < row_one.length; i++) {
  58.             XSSFCell c00 = row0.createCell(i);
  59.             c00.setCellValue(row_one[i]);
  60.             c00.setCellStyle(headerStyle);
  61.         }
  62.         // 创建第二行单元格
  63.         for (int i = 0; i < row_two.length; i++) {
  64.             XSSFCell tempCell = row2.createCell(i);
  65.             tempCell.setCellValue(row_two[i]);
  66.             tempCell.setCellStyle(headerStyle);
  67.         }
  68.         // 创建第三行单元格
  69.         for (int i = 0; i < row_three.length; i++) {
  70.             XSSFCell tempCell = row3.createCell(i);
  71.             tempCell.setCellValue(row_three[i]);
  72.             tempCell.setCellStyle(headerStyle);
  73.         }
  74.  
  75.  
  76.         // 设置表头单元格的宽度
  77.         tableTitleStyleColumnwidth(sheet);
  78.  
  79.         // todo： 填充数据
  80.         approvalMethodfillInData(convertListMap, sheet, contentStyle, rowNum, row_three, tableTitleList);
  81. //        approvalMethodfillInData(convertListMap, sheet, contentStyle, rowNum, cellSize, tableTitleList);
  82.         //导出到浏览器下载
  83.         buildExcelDocument("监测数据.xlsx", wb, response);

2.合并单元格

  1. private static void extracted(XSSFSheet sheet) {
  2.         // todo: 合并参数分别为： 起始行，结束行，起始列，结束列
  3.         // 将第一列 的 第一行到第三行合并
  4.         sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
  5.         // 将第二列的 第一行到第三行合并
  6.         sheet.addMergedRegion(new CellRangeAddress(0, 2, 1, 1));
  7.         //  将第三列的 第一行到第三行合并
  8.         sheet.addMergedRegion(new CellRangeAddress(0, 2, 2, 2));
  9.         sheet.addMergedRegion(new CellRangeAddress(0, 2, 3, 3));
  10.  
  11.         // 合计
  12.         sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 6));
  13.         // 合计下的单元格
  14.         sheet.addMergedRegion(new CellRangeAddress(1, 2, 4, 4));
  15.         sheet.addMergedRegion(new CellRangeAddress(1, 2, 5, 5));
  16.         sheet.addMergedRegion(new CellRangeAddress(1, 2, 6, 6));
  17.  
  18.        
  19.         sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 18));
  20.      
  21.         sheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 30));
  22.  
  23.         
  24.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 10));
  25.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 11, 14));
  26.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 18));
  27.  
  28.        
  29.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 19, 22));
  30.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 23, 26));
  31.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 27, 30));
  32.     }

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

  1. private static void tableTitleStyleColumnwidth(XSSFSheet sheet) {
  2.  
  3.         sheet.setColumnWidth(2, 8000);
  4.         sheet.setColumnWidth(3, 8000);
  5.  
  6.         sheet.setColumnWidth(7, 3000);
  7.         sheet.setColumnWidth(8, 3000);
  8.         sheet.setColumnWidth(9, 3000);
  9.         sheet.setColumnWidth(10, 3000);
  10.  
  11.         sheet.setColumnWidth(11, 3000);
  12.         sheet.setColumnWidth(12, 3000);
  13.         sheet.setColumnWidth(13, 3000);
  14.         sheet.setColumnWidth(14, 3000);
  15.  
  16.         sheet.setColumnWidth(15, 3000);
  17.         sheet.setColumnWidth(16, 3000);
  18.         sheet.setColumnWidth(17, 3000);
  19.         sheet.setColumnWidth(18, 3000);
  20.  
  21.         sheet.setColumnWidth(19, 3000);
  22.         sheet.setColumnWidth(20, 3000);
  23.         sheet.setColumnWidth(21, 3000);
  24.         sheet.setColumnWidth(22, 3000);
  25.  
  26.         sheet.setColumnWidth(23, 3000);
  27.         sheet.setColumnWidth(24, 3000);
  28.         sheet.setColumnWidth(25, 3000);
  29.         sheet.setColumnWidth(26, 3000);
  30.  
  31.         sheet.setColumnWidth(27, 3000);
  32.         sheet.setColumnWidth(28, 3000);
  33.         sheet.setColumnWidth(29, 3000);
  34.         sheet.setColumnWidth(30, 3000);
  35.     }

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

  1. private static void approvalMethodfillInData(List<Map<String, Object>> convertListMap, XSSFSheet sheet, XSSFCellStyle contentStyle, int rowNum, String[] cellSize, List<Object> tableTitleList) {
  2.  
  3.         for (Map<String, Object> map : convertListMap) {
  4.             XSSFRow tempRow = sheet.createRow(rowNum++);
  5.             tempRow.setHeight((short) 500);
  6.  
  7.             // 判断选择类型
  8.             if (tableTitleList.size() == 2 || "enterprise".equals(tableTitleList.get(0).toString())) {
  9.                 // 循环单元格填入数据
  10.                 for (int i = 0; i < cellSize.length; i++) {
  11.                     //列宽自适应，j为自适应的列，true就是自适应，false就是不自适应，默认不自适应
  12.                     sheet.autoSizeColumn(i, true);
  13.                     XSSFCell tempCell = tempRow.createCell(i);
  14.                     tempCell.setCellStyle(contentStyle);
  15.                     String tempValue = "";
  16.                     switch (i) {
  17.                         case 0:
  18.                             tempValue = map.get("indexNum").toString();
  19.                             break;
  20.                         case 1:
  21.                             tempValue = map.get("platName").toString();
  22.                             break;
  23.                         case 2:
  24.                             tempValue = map.get("deptName").toString();
  25.                             break;
  26.                         case 3:
  27.                             tempValue = map.get("type").toString();
  28.                             break;
  29.                         case 4:
  30.                             tempValue = map.get("totalProNum").toString();
  31.                             break;
  32.                         case 5:
  33.                             tempValue = map.get("totalBidNum").toString();
  34.                             break;
  35.                         case 6:
  36.                             tempValue = map.get("totalAmount").toString();
  37.                             break;
  38.                         case 7:   
  39.                             tempValue = map.get("enterpriseEngineeringProNum").toString();
  40.                             break;
  41.                         case 8:
  42.                             tempValue = map.get("enterpriseEngineeringBidNum").toString();
  43.                             break;
  44.                         case 9:
  45.                             tempValue = map.get("enterprisePurchaseAmount").toString();
  46.                             break;
  47.                         case 10:
  48.                             tempValue = map.get("planEnterprisePurchaseAmount").toString();
  49.                             break;
  50.                         case 11:   
  51.                             tempValue = map.get("enterprisePurchaseProNum").toString();
  52.                             break;
  53.                         case 12:
  54.                             tempValue = map.get("enterprisePurchaseBidNum").toString();
  55.                             break;
  56.                         case 13:
  57.                             tempValue = map.get("enterprisePurchaseAmount").toString();
  58.                             break;
  59.                         case 14:
  60.                             tempValue = map.get("planEnterprisePurchaseAmount").toString();
  61.                             break;
  62.                         case 15:  
  63.                             tempValue = map.get("enterpriseServiceProNum").toString();
  64.                             break;
  65.                         case 16:
  66.                             tempValue = map.get("enterpriseServiceBidNum").toString();
  67.                             break;
  68.                         case 17:
  69.                             tempValue = map.get("enterpriseServiceAmount").toString();
  70.                             break;
  71.                         case 18:
  72.                             tempValue = map.get("planEnterpriseServiceAmount").toString();
  73.                             break;
  74.  
  75.                         case 19:  
  76.                             tempValue = map.get("groupEngineeringProNum").toString();
  77.                             break;
  78.                         case 20:
  79.                             tempValue = map.get("grouppurchaseBidNum").toString();
  80.                             break;
  81.                         case 21:
  82.                             tempValue = map.get("groupPurchaseAmount").toString();
  83.                             break;
  84.                         case 22:
  85.                             tempValue = map.get("palnGroupPurchaseAmount").toString();
  86.                             break;
  87.                         case 23: 
  88.                             tempValue = map.get("groupPurchaseProNum").toString();
  89.                             break;
  90.                         case 24:
  91.                             tempValue = map.get("groupEngineeringBidNum").toString();
  92.                             break;
  93.                         case 25:
  94.                             tempValue = map.get("groupEngineeringAmount").toString();
  95.                             break;
  96.                         case 26:
  97.                             tempValue = map.get("planGroupEngineeringAmount").toString();
  98.                             break;
  99.                         case 27: 
  100.                             tempValue = map.get("groupServiceProNum").toString();
  101.                             break;
  102.                         case 28:
  103.                             tempValue = map.get("groupServiceBidNum").toString();
  104.                             break;
  105.                         case 29:
  106.                             tempValue = map.get("groupServiceAmount").toString();
  107.                             break;
  108.                         case 30:
  109.                             tempValue = map.get("planGroupServiceAmount").toString();
  110.                             break;
  111.                     }
  112.                     tempCell.setCellValue(tempValue);
  113.                 }
  114.             }
  115. }

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

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

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

  1.  /**
  2.      * 创建标题样式
  3.      *
  4.      * @param wb
  5.      * @return
  6.      */
  7.     private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
  8.         XSSFCellStyle cellStyle = wb.createCellStyle();
  9.         cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
  10.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
  11.         cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  12. //        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
  13.  
  14.         XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式
  15.         headerFont1.setBold(true); //字体加粗
  16.         headerFont1.setFontName("黑体"); // 设置字体类型
  17.         headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
  18.         cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
  19.         return cellStyle;
  20.     }
  21.  
  22.     /**
  23.      * 创建表头样式
  24.      *
  25.      * @param wb
  26.      * @return
  27.      */
  28.     private static XSSFCellStyle createHeadCellStyle(XSSFWorkbook wb) {
  29.         XSSFCellStyle cellStyle = wb.createCellStyle();
  30.         cellStyle.setWrapText(true);// 设置自动换行
  31.         cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
  32.         cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
  33.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
  34.         cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  35. //        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
  36.         cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
  37.         cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
  38.         cellStyle.setBorderRight(BorderStyle.THIN); //右边框
  39.         cellStyle.setBorderTop(BorderStyle.THIN); //上边框
  40.  
  41.         XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
  42.         headerFont.setBold(true); //字体加粗
  43.         headerFont.setFontName("黑体"); // 设置字体类型
  44.         headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
  45.         cellStyle.setFont(headerFont); // 为标题样式设置字体样式
  46.  
  47.         return cellStyle;
  48.     }
  49.  
  50.     /**
  51.      * 创建内容样式
  52.      *
  53.      * @param wb
  54.      * @return
  55.      */
  56.     private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
  57.         XSSFCellStyle cellStyle = wb.createCellStyle();
  58.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
  59.         cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
  60.         cellStyle.setWrapText(true);// 设置自动换行
  61.         cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
  62.         cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
  63.         cellStyle.setBorderRight(BorderStyle.THIN); //右边框
  64.         cellStyle.setBorderTop(BorderStyle.THIN); //上边框
  65.  
  66.         // 生成12号字体
  67.         XSSFFont font = wb.createFont();
  68.         font.setColor((short) 8);
  69.         font.setFontHeightInPoints((short) 12);
  70.         cellStyle.setFont(font);
  71.  
  72.         return cellStyle;
  73.     }

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

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小桥流水78/article/detail/814732
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号