Poi Excel 动态变化行高，动态创建Sheet

需求

• Excel 最终需要由A4纸打印出来
• 标题名称需要动态变化
• 行高自动变化
• 每页都需要保留标题

分析

基础需求即填充标题填充数据，设置样式，基础需求可以通过easyExcel或者Poi的API来实现，但是由于需求3、4，easyExcel并不支持，只能选择使用Apache Poi。

Apache POI没有直接的API来自动调整行高，所以行高需要手动计算

每页都需要保留标题，这个需求导致不能使用excel软件默认的分页，所以要求控制每页的数据总量填充后整体高度需要小于一张A4纸的高度，否则数据将被分页。

那么大量的数据手动分页，可以通过多sheet来实现，确保每个sheet只存一张A4纸能放下的数据，超过了则复制一个sheet然后继续填充。

代码

​ 以下是一个实现的示例，根据自己的需求进行参考变动即可

1. 获取要填充的数据

   List<ExcelEthicRecordData> records = this.getExcelEthicRecord(ethIds);
   
   

2. 获取模板文件创建workbook对象

   // 加载模板文件 获取模板文件流
           ClassPathResource templateResource = new ClassPathResource("excel-template/template.xlsx");
           InputStream templateStream = templateResource.getInputStream();
           Workbook workbook = WorkbookFactory.create(templateStream);
   

3. 设置标题样式以及拷贝样式（如果需要的话）

   /**
        * 设置标题以及将样式拷贝出来
        * @param records 记录 用于计算标题类型
        * @param workbook workbook
        * @return 样式
        */
       private static CellStyle setStyle(List<ExcelEthicRecordData> records, Workbook workbook) {
           // 计算总Sheet高度 后面需要保持总高不变
           Sheet templateSheet = workbook.getSheetAt(0);
   
           // 获取标题单元格
           Row titleRow = templateSheet.getRow(1);  // 第二行的索引为1
           Cell titleCell = titleRow.getCell(0);  // 假设标题在第一列
   
           // 替换标题的“【类型】”为type变量
           String title = titleCell.getStringCellValue();
           title = title.replace("类型", calculateType(records));
           titleCell.setCellValue(title);
   
   		// 从模板文件的标题列中获取样式，主要是边框
           CellStyle borderStyle = templateSheet.getRow(3).getCell(0).getCellStyle();
           // 创建新的字体样式
           Font font = workbook.createFont();
           font.setFontHeightInPoints((short) 9);  // 缩小字号为9号字
           borderStyle.setFont(font);
           borderStyle.setWrapText(true); // 自动换行
           return borderStyle;
       }
   

4. 动态变化行高和创建Sheet并且填充数据

   CellStyle borderStyle = setStyle(records, workbook);
   
           int currentSheetIndex = 0;
           int currentRowNumber = 4;  // 从第五行开始填充数据
           short defaultHeight = 40 * 20;  // 默认行高为40 可根据自己需要进行设置
   
           // 获取当前Sheet
           Sheet currentSheet = workbook.getSheetAt(currentSheetIndex);
           // 在POI库中，Excel的行高是以1/20个点为单位进行设定的， A4 纸包含边距 1150个点数，模板文件默认行高设置大约40个点数
           // 根据打印结果计算，行高40的情况下大概能打印12行 根据自己需要计算
           int maxRowsPerPage = defaultHeight * 12;
   
           int totalHeight = 0; // 记录当前页已使用的高度
   
           for (ExcelEthicRecordData record : records) {
               // 计算行高，这里假设每14个字符占一行
               int recordHeight = Math.max(record.getNum().length(), record.getSchemeName().length()) / 14 + 1;
               // 根据实际内容长度调整行高
               short height = (recordHeight > 1) ? (short) (256 * recordHeight) : defaultHeight;
   
               // 如果这条记录的高度使得当前Sheet的高度超过了最大高度，那么就创建一个新的Sheet
               if (totalHeight + height > maxRowsPerPage) { // 如果添加新的行会超过总高度
                   // 创建新的Sheet，并将模板Sheet的格式复制到新的Sheet
                   String sheetName = "Sheet" + ++currentSheetIndex;
                   cloneSheetStyle(workbook, sheetName);
                   // 重置当前高度和行数
                   totalHeight = 0;
                   currentRowNumber = 4; // 如果你的标题占用了4行，那么数据从第5行开始
                   currentSheet = workbook.getSheetAt(currentSheetIndex);
               }
               // 在当前Sheet的指定行创建新的行
               Row newRow = currentSheet.createRow(currentRowNumber);
               newRow.setHeight(height);
   
               // 应用样式到新的单元格上 填充数据
               for (int i = 0; i < 8; i++) {
                   Cell cell = newRow.createCell(i);
                   cell.setCellStyle(borderStyle);
                   if (i == 0) {
                       cell.setCellValue(record.getNum());
                   } else if (i == 1) {
                       cell.setCellValue(record.getSchemeName());
                   }
               }
               // 更新当前高度和行数
               totalHeight += height;
               currentRowNumber++;
           }
   

5. 克隆样式代码

   private void cloneSheetStyle(Workbook workbook, String sheetName) {
           // 获取模板sheet
           Sheet templateSheet = workbook.getSheetAt(0);
   
           // 复制模板sheet到目标sheet
           Sheet newSheet = workbook.cloneSheet(workbook.getSheetIndex(templateSheet));
   
           // 保留前四行，移除其他所有行
           for (int i = newSheet.getLastRowNum(); i >= 4; i--) {
               Row row = newSheet.getRow(i);
               if (row != null) {
                   newSheet.removeRow(row);
               }
           }
   
           // 重命名新建的sheet
           workbook.setSheetName(workbook.getSheetIndex(newSheet), sheetName);
       }
   

6. 相应代码

   private static ResponseEntity<byte[]> getResponseEntity(Workbook workbook) throws IOException {
           ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
           workbook.write(outputStream);
   
           // 构建下载响应
           HttpHeaders headers = new HttpHeaders();
           headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
           headers.setContentDispositionFormData("attachment", "xxxx.xlsx");
           byte[] fileBytes = outputStream.toByteArray();
   
           return ResponseEntity.ok()
                   .headers(headers)
                   .contentLength(fileBytes.length)
                   .body(fileBytes);
       }