网站导航
> 文章列表 > Java基于POI动态合并单元格

Java基于POI动态合并单元格

网友: 文章列表 2024-03-22 03:16:15

Java基于POI动态合并单元格

Java使用poi导出excel

  • 前言
    • 1.Excel和POI对象对应关系：
    • 2.POI创建Excel的步骤
  • 一、引入依赖
  • 二、示例
    • 1.准备数据
    • 2.创建Excel工作簿对象
    • 3.给excel创建表头
    • 4.填充数据
    • 5.浏览器访问下载excel
    • 6.完整代码

前言

有个需求需要后端将数据导出为excel。并且excel中需要合并单元格。于是在网上找到了poi这个包。

首先要根据数据或者excel模板构建出表头。然后将表头和数据想办法进行关联进行循环添加。

效果图：
Java基于POI动态合并单元格

1.Excel和POI对象对应关系：

Excel结构 POI对象
excel 的文档对象 HSSFWorkbook
excel 的表单（sheet） HSSFSheet
excel 的行 HSSFRow
excel 的格子单元 HSSFCell

2.POI创建Excel的步骤

  1. 生成文档对象HSSHWorkbook。
  2. 通过HSSFWorkbook生成表单HSSFSheet。
  3. 通过HSSFSheet生成行HSSFRow
  4. 通过HSSFRow生成单元格HSSFCell。

一、引入依赖

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency>

二、示例

1.准备数据

 String[] masterHead = {"学号","姓名","专业"};String[] childHead = {"课程名称","上课地点","任课教师","上课时间"};List<String[]> childList = new ArrayList<>();childList.add(new String[]{"Java程序设计","1号楼302","雷老师","2022/8/30 15:53:49"});childList.add(new String[]{"数据结构","1号楼305","雷老师","2022/8/30 9:18:28"});List<String[]> childList1 = new ArrayList<>();childList1.add(new String[]{"计算机网络","2号楼301","方老师","2022/8/30 15:53:49"});List<Map<String,List<String[]>>> masterList = new ArrayList<>();Map<String,List<String[]>> map = new HashMap();map.put("20210211-张晓-计算机与科学",childList);map.put("20210211-张2-计算机与科学",childList);map.put("20210212-于丽-电子信息工程",childList1);masterList.add(map);

2.创建Excel工作簿对象

 //创建Excel工作薄对象HSSFWorkbook workbook=new HSSFWorkbook();//创建Excel工作表对象HSSFSheet sheet = workbook.createSheet("wj");//设置单元格居中HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置边框cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框

3.给excel创建表头

然后根据需求一行行来给工作表格填充数据，首先是复杂表头，第一行是主表和子表，主表和子表的是合并列，根据主子表头的长度来确定，合并的列数；第二行是表头，根据主子表头的数组来填充。

 //创建行的单元格，从0开始HSSFRow row = sheet.createRow(0);//创建统计单元格HSSFCell masterCell=row.createCell(0);//赋值masterCell.setCellValue("主表");masterCell.setCellStyle(cellStyle);//合并列CellRangeAddress region=new CellRangeAddress(0, 0, 0, masterHead.length-1);sheet.addMergedRegion(region);//创建详情单元格  从统计单元格的后一格开始创建HSSFCell childCell = row.createCell(masterHead.length);//赋值childCell.setCellValue("子表");childCell.setCellStyle(cellStyle);//合并列region=new CellRangeAddress(0, 0, masterHead.length, masterHead.length+childHead.length-1);sheet.addMergedRegion(region);//表头 从1开始HSSFRow titleRow = sheet.createRow(1);//主表头for (int i = 0; i < masterHead.length ; i++) {HSSFCell msCell = titleRow.createCell(i);msCell.setCellStyle(cellStyle);msCell.setCellValue(masterHead[i]);}//子表头for (int i = 0; i < childHead.length; i++) {HSSFCell chcell = titleRow.createCell(masterHead.length+i);chcell.setCellStyle(cellStyle);chcell.setCellValue(childHead[i]);}

4.填充数据

    //填充数据int lastRowIndex = 2; //记录最后行位置for (Map<String,List<String[]>> m : masterList){for (String key : m.keySet()){String[] ms = key.split("-");List<String[]> chlist = m.get(key);HSSFRow valueRow = sheet.createRow(lastRowIndex);for (int i = 0; i < ms.length ; i++) {HSSFCell mscell = valueRow.createCell(i);mscell.setCellStyle(cellStyle);mscell.setCellValue(ms[i]);if (chlist.size()>1){ //子表数量大于1才进行 行合并region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, i, i);sheet.addMergedRegion(region);}}for (int i = 0; i < chlist.size(); i++) {String[] chstrs = chlist.get(i);HSSFRow chRow;if (i == 0){ //避免重复创建 覆盖主表数据chRow = valueRow;}else {chRow  = sheet.createRow(lastRowIndex);}lastRowIndex++;for (int j = 0; j < chstrs.length; j++) {HSSFCell chcell = chRow.createCell(ms.length+j);chcell.setCellStyle(cellStyle);chcell.setCellValue(chstrs[j]);}}}}

5.浏览器访问下载excel

        //设置下载头部文件信息response.setContentType("application/octet-stream; charset=utf-8");//以流的形式对文件进行下载response.setHeader("Content-Disposition", "attachment;filename="  + URLEncoder.encode("demo.xls", "UTF-8"));workbook.write(response.getOutputStream());response.getOutputStream().close();

6.完整代码

 @GetMapping("/")public void exportExcel(HttpServletResponse response) throws IOException {String[] masterHead = {"学号","姓名","专业"};String[] childHead = {"课程名称","上课地点","任课教师","上课时间"};List<String[]> childList = new ArrayList<>();childList.add(new String[]{"Java程序设计","1号楼302","雷老师","2022/8/30 15:53:49"});childList.add(new String[]{"数据结构","1号楼305","雷老师","2022/8/30 9:18:28"});List<String[]> childList1 = new ArrayList<>();childList1.add(new String[]{"计算机网络","2号楼301","方老师","2022/8/30 15:53:49"});List<Map<String,List<String[]>>> masterList = new ArrayList<>();Map<String,List<String[]>> map = new HashMap();map.put("20210211-张晓-计算机与科学",childList);map.put("20210211-张2-计算机与科学",childList);map.put("20210212-于丽-电子信息工程",childList1);masterList.add(map);//创建Excel工作薄对象HSSFWorkbook workbook=new HSSFWorkbook();//创建Excel工作表对象HSSFSheet sheet = workbook.createSheet("wj");//设置单元格居中HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框//创建行的单元格，从0开始HSSFRow row = sheet.createRow(0);//创建统计单元格HSSFCell masterCell=row.createCell(0);//赋值masterCell.setCellValue("主表");masterCell.setCellStyle(cellStyle);//合并列CellRangeAddress region=new CellRangeAddress(0, 0, 0, masterHead.length-1);sheet.addMergedRegion(region);//创建详情单元格  从统计单元格的后一格开始创建HSSFCell childCell = row.createCell(masterHead.length);//赋值childCell.setCellValue("子表");childCell.setCellStyle(cellStyle);//合并列region=new CellRangeAddress(0, 0, masterHead.length, masterHead.length+childHead.length-1);sheet.addMergedRegion(region);//表头 从1开始HSSFRow titleRow = sheet.createRow(1);//主表头for (int i = 0; i < masterHead.length ; i++) {HSSFCell msCell = titleRow.createCell(i);msCell.setCellStyle(cellStyle);msCell.setCellValue(masterHead[i]);}//子表头for (int i = 0; i < childHead.length; i++) {HSSFCell chcell = titleRow.createCell(masterHead.length+i);chcell.setCellStyle(cellStyle);chcell.setCellValue(childHead[i]);}//填充数据int lastRowIndex = 2; //记录最后行位置for (Map<String,List<String[]>> m : masterList){for (String key : m.keySet()){String[] ms = key.split("-");List<String[]> chlist = m.get(key);HSSFRow valueRow = sheet.createRow(lastRowIndex);for (int i = 0; i < ms.length ; i++) {HSSFCell mscell = valueRow.createCell(i);mscell.setCellStyle(cellStyle);mscell.setCellValue(ms[i]);if (chlist.size()>1){ //子表数量大于1才进行 行合并region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, i, i);sheet.addMergedRegion(region);}}for (int i = 0; i < chlist.size(); i++) {String[] chstrs = chlist.get(i);HSSFRow chRow;if (i == 0){ //避免重复创建 覆盖主表数据chRow = valueRow;}else {chRow  = sheet.createRow(lastRowIndex);}lastRowIndex++;for (int j = 0; j < chstrs.length; j++) {HSSFCell chcell = chRow.createCell(ms.length+j);chcell.setCellStyle(cellStyle);chcell.setCellValue(chstrs[j]);}}}}//设置下载头部文件信息response.setContentType("application/octet-stream; charset=utf-8");//以流的形式对文件进行下载response.setHeader("Content-Disposition", "attachment;filename="  + URLEncoder.encode("demo.xls", "UTF-8"));workbook.write(response.getOutputStream());response.getOutputStream().close();}
网络标签：

相关问题