Java POI动态生成包含合并单元格的Excel

主要是根据poi包中的 CellRangeAddress 来使用 

现在需求是：根据一对多的两表关系生成一个带合并单元格的Excel，使两表在Excel里的数据表现形式也是一对多

类似于这种

解决方案，搜索了半天也只有添加CellRangeAddress这方法，只要计算好起始和终止的行号列号还是可以用的。因为其是直接添加到sheet类里，另外生成的合并单元格似乎也能自动把相同数据的单元格处理掉，所以我们可以先按正常的一对一添加完数据，再去添加合并单元格这两个互不干扰。

CellRangeAddress（int， int， int， int）

参数：起始行号，终止行号， 起始列号，终止列号

代码：

ExcelUtil.java

参数，os是等会要输出excel的流，excelExtName是文件后缀，data是数据（最外面一层Map是工作表名，下面一层List是工作表各行，再下一行List是一行里各单元格），regionData是合并单元格，因为只需要加到sheet层（工作表），map即可

public static void writeExcelWithCollap(OutputStream os,String excelExtName,Map<String,List<List<String>>>data,Map<String,List<CellRangeAddress>> regionsData)throws IOException {
        Workbook wb=null;
        try {
            if ("xls".equals(excelExtName)) {
                wb = new HSSFWorkbook();
            } else if ("xlsx".equals(excelExtName)) {
                wb = new XSSFWorkbook();
            } else {
                throw new Exception("当前文件不是excel文件");
            }
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            cellStyle.setWrapText(true);
            for (String sheetName : data.keySet()) {
                Sheet sheet = wb.createSheet(sheetName);
                sheet.setDefaultColumnWidth(40);
                List<CellRangeAddress> regions = regionsData.get(sheetName);
                List<List<String>> rowList = data.get(sheetName);
                for (int i=0; i < rowList.size(); i++) {
                    List<String> cellList = rowList.get(i);
                    Row row = sheet.createRow(i);
                    for (int j = 0; j < cellList.size(); j++) {
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(cellList.get(j));
                    }
                }
                for (CellRangeAddress region : regions) {
                    sheet.addMergedRegion(region);
                }
            }
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (wb != null) {
                wb.close();
            }
        }
    }

ExcelService.java 

关键是要计算好合并单元格的大小

    public void downloadCheckProject(ServletOutputStream servletOutputStream) throws IOException {
        Map<String, List<List<String>>> data = new HashMap<>();
        Map<String, List<CellRangeAddress>> regionData = new HashMap<>();
        List<List<String>> rows = new ArrayList<>();
        List<CellRangeAddress> regions = new ArrayList<>();

        //添加excel的行标题
        List<String> titles = new ArrayList<>();
        titles.add(ExcelTitle.CPROJECTNAME);
        titles.add(ExcelTitle.CLEADER);
        titles.add(ExcelTitle.CTASKNAME);
        titles.add(ExcelTitle.CDEPARTMENT);
        titles.add(ExcelTitle.CPROGRESS);
        titles.add(ExcelTitle.CFINISHED);
        rows.add(titles);

        //取出要导出的数据
        List<CheckProject> checkProjects = checkProjectMapper.selectList(new QueryWrapper<>());
        List<CheckProjectTask> checkProjectTasks = checkProjectTaskMapper.selectList(new QueryWrapper<>());
        //用于标识遍历的位置
        int index = 1;
        for (CheckProject checkProject : checkProjects) {

            //checkProject表通过taskList对应多个task
            String taskList = checkProject.getTaskList();
            Long id = checkProject.getId();
            if (!Util.isNullOrEmpty(taskList) && taskList.trim().length() > 0) {
                String[] taskLists = taskList.split(",");
                //判断对应task表的长度来决定合并单元格跨越的行数
                if (taskLists.length > 1) {
                    CellRangeAddress region = new CellRangeAddress(index, index - 1 + taskLists.length, 0, 0);
                    CellRangeAddress region2 = new CellRangeAddress(index, index - 1 + taskLists.length, 1, 1);
                    regions.add(region);
                    regions.add(region2);
                }
                //更新索引
                index += taskLists.length;
                 //task表通过workId 来对应 project，当然也可以用stream的collectors::groupingby(CheckProjectTask::getWorkId()) 方法
                List<CheckProjectTask> temp = checkProjectTasks.stream().filter(p -> p.getWorkId().equals(id)).collect(Collectors.toList());
                for (CheckProjectTask task : temp) {
                    List<String> row = new ArrayList<>();
                    row.add(checkProject.getProjectName());
                    row.add(checkProject.getLeader());
                    row.add(task.getTaskName());
                    row.add(task.getDepartment());
                    row.add(task.getProgress());
                    row.add(task.getFinished());
                    rows.add(row);
                }
            } else {
                List<String> row = new ArrayList<>();
                row.add(checkProject.getProjectName());
                row.add(checkProject.getLeader());
                rows.add(row);
                index++;
            }
        }
        //添加数据
        data.put(ExcelTitle.CCHECKPROJECT, rows);
        //添加合并单元格
        regionData.put(ExcelTitle.CCHECKPROJECT, regions);
        ExcelUtil.writeExcelWithCollap(servletOutputStream,"xlsx",data,regionData);
    }

 ExcelApi.java

    @PostMapping("/download")
    public void downloadCheckProject(HttpServletResponse response)throws IOException {
        response.setHeader("Content-disposition","attachment;filename="+
                new String("download.xlsx".getBytes("UTF-8"),"iso8859-1"));
        ServletOutputStream servletOutputStream = response.getOutputStream();
        excelService.downloadCheckProject(servletOutputStream);
        servletOutputStream.flush();
        servletOutputStream.close();
    }