如何在Apache POI的HSSFWorkbook中实现Excel横向分页？

云上老周

2026-7-23

在HSSFWorkbook中实现横向按列分页

我正在使用Apache POI，初始化代码如下：

HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
.......
.......

后续代码执行后数据显示正常，但行过长。客户要求将工作表按模板格式横向拆分页面（从左到右分成多页），如何在HSSFWorkbook中实现？

我尝试了以下方法：

sheet.setAutobreaks(true);
sheet.setFitToPage(true);

但均无效。我需要让用户在屏幕上看到的Excel工作表与模板格式一致（当前生成的工作表数据行全部在同一页面，模板效果为页面从左到右拆分）。

完整代码

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;  

public class XlsxMaker 
{         
    public static void Main (String [] args)
    {
     List<String> columnModelList = new ArrayList<String>();
     List<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
     String outXlsxFileName = "outXlsFile.xlsx";
       
     //添加测试列名
     columnModelList.add("Col1"); columnModelList.add("Col2");
     columnModelList.add("Col3"); columnModelList.add("Col4");
     columnModelList.add("Col5"); columnModelList.add("Col6");
     columnModelList.add("Col7"); columnModelList.add("Col8");
     columnModelList.add("Col9"); columnModelList.add("Col10");
     columnModelList.add("Col11"); columnModelList.add("Col12");
     columnModelList.add("Col13"); columnModelList.add("Col14");
     columnModelList.add("Col15"); columnModelList.add("Col16");
     columnModelList.add("Col17"); columnModelList.add("Col18");
     columnModelList.add("Col19"); columnModelList.add("Col20");
     columnModelList.add("Col21"); columnModelList.add("Col22");
       
       
     //添加测试数据行
     ArrayList<String> realData = new ArrayList<String>();

     realData.add("Data-1-01"); realData.add("Data-1-02");
     realData.add("Data-1-03"); realData.add("Data-1-04");
     realData.add("Data-1-05"); realData.add("Data-1-06");
     realData.add("Data-1-07"); realData.add("Data-1-08");
     realData.add("Data-1-09"); realData.add("Data-1-10");
     realData.add("Data-1-11"); realData.add("Data-1-12");
     realData.add("Data-1-13"); realData.add("Data-1-14");
     realData.add("Data-1-15"); realData.add("Data-1-16");
     realData.add("Data-1-17"); realData.add("Data-1-18"); 
     realData.add("Data-1-19"); realData.add("Data-1-20");
     realData.add("Data-1-21"); realData.add("Data-1-22");
      
     dataList.add(realData);
      
     realData = new ArrayList<String>();

     realData.add("Data-2-01"); realData.add("Data-2-02");
     realData.add("Data-2-03"); realData.add("Data-2-04");
     realData.add("Data-2-05"); realData.add("Data-2-06");
     realData.add("Data-2-07"); realData.add("Data-2-08");
     realData.add("Data-2-09"); realData.add("Data-2-10");
     realData.add("Data-2-11"); realData.add("Data-2-12");
     realData.add("Data-2-13"); realData.add("Data-2-14");
     realData.add("Data-2-15"); realData.add("Data-2-16");
     realData.add("Data-2-17"); realData.add("Data-2-18"); 
     realData.add("Data-2-19"); realData.add("Data-2-20");
     realData.add("Data-2-21"); realData.add("Data-2-22");
      
     dataList.add(realData);

     //生成Excel文件
     ByteArrayOutputStream baou =  combineXlsComponents(columnModelList, dataList);
     try(OutputStream outputStream = new FileOutputStream(outXlsxFileName)) {
        baou.writeTo(outputStream);
     } catch (IOException e) {
         e.printStackTrace();
     }
    }     
      
      private static ByteArrayOutputStream combineXlsComponents(List<String> columnsList, List<ArrayList<String>> dataList)
      {
       ByteArrayOutputStream baos = new ByteArrayOutputStream();
      
       try(HSSFWorkbook workbook = populateXlsData(dataList, prepareColomnHeaders(columnsList))){ 
            
            workbook.write(baos);
       }catch(IOException e){
            System.out.println("combineXlsComponents: "+e.getMessage());
        }
            
        return baos;
      }
   
    private static HSSFWorkbook  prepareColomnHeaders(List<String> columnsList) throws IOException
    { 
          HSSFWorkbook  workbook = new HSSFWorkbook ();
      
          Sheet sheet = workbook.createSheet();
            
          /*尝试设置打印时自动分页*/
          sheet.setAutobreaks(true);
          sheet.setFitToPage(true);
          sheet.getPrintSetup().setFitHeight((short)0);
          sheet.getPrintSetup().setFitWidth((short)1);
          
          sheet.setColumnBreak(6);
            
          /*冻结前2行*/
          sheet.createFreezePane(0,2);    
            
          /*合并第0行到21列*/
          sheet.addMergedRegion(new CellRangeAddress(0,0,0,21));  
             
          Row row0 = sheet.createRow(0);
          row0.setHeightInPoints(23);
          Cell cell1 = row0.createCell(0);
          cell1.setCellValue("For Official Use Only.");
          
          /*设置顶部保密行样式*/
          cell1.setCellStyle(styleFouoRow(workbook));
            
          /* -----------------设置表头样式------------------------*/
          Font boldFont = workbook.createFont();
          boldFont.setBold(true); 
          CellStyle styleForHeaders = workbook.createCellStyle();     
          styleForHeaders.setFont(boldFont); 
          styleForHeaders.setWrapText(true);  
          styleForHeaders.setVerticalAlignment(VerticalAlignment.CENTER);
            
          /*设置表头单元格颜色*/
          styleForHeaders.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); 
          styleForHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);       
          styleForHeaders.setFillBackgroundColor(IndexedColors.DARK_BLUE.getIndex());  
            
          /*设置表头单元格边框*/
          styleForHeaders.setBorderLeft(BorderStyle.THIN);
          styleForHeaders.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            
          styleForHeaders.setBorderRight(BorderStyle.THIN);
          styleForHeaders.setRightBorderColor(IndexedColors.BLACK.getIndex());
            
          styleForHeaders.setBorderTop(BorderStyle.THIN);
          styleForHeaders.setTopBorderColor(IndexedColors.BLACK.getIndex());
            
          styleForHeaders.setBorderBottom(BorderStyle.THIN);
          styleForHeaders.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            
          HSSFFont fontHeaders = workbook.createFont(); 
          fontHeaders.setColor(IndexedColors.WHITE.getIndex());
          fontHeaders.setFontHeightInPoints((short) 10);
          fontHeaders.setBold(true);
          styleForHeaders.setFont(fontHeaders);
      
          Row row = sheet.createRow(1);
          row.setHeightInPoints(25);          
            
          int nextCell = -1;
          for (String strObj: columnsList) 
          {  
            Cell cell = row.createCell(++nextCell);
            cell.setCellValue(strObj);
            cell.setCellStyle(styleForHeaders);
          }
            
        /*设置列宽*/
          for(int i = 0; i <= 21; i++){
            sheet.setColumnWidth(i, 6000); 
          }
            
          return workbook;  
      }
  
    private static HSSFWorkbook populateXlsData(List<ArrayList<String>> dataList, HSSFWorkbook workbook)
    {
      try 
     {
            if (dataList != null) 
            {
             Sheet sheet = workbook.getSheetAt(0);
             
             /*前两行（0和1）用于标题和表头，数据从第2行开始*/
               int rowNum = 2;
                
               CellStyle style = workbook.createCellStyle();
               style.setWrapText(true); 
               style.setVerticalAlignment(VerticalAlignment.CENTER);
               
               /*遍历数据行并写入工作表*/
               for (ArrayList<String> dataRow : dataList) 
               { 
                   if (dataRow != null) 
                   {
                    Row row = sheet.createRow(rowNum++);
                    
                    int nextCell = -1;
                    /*遍历每行的每个值*/
                    for (String nextString : dataRow){
                      Cell cell = row.createCell(++nextCell);
                          cell.setCellValue(nextString);
                          cell.setCellStyle(style);
                    }
                  }
               } 
               
               /*添加底部保密行*/
               sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,21));
               Row lastRow = sheet.createRow(rowNum);
               lastRow.setHeightInPoints(23);
               Cell cell1 = lastRow.createCell(0);
               cell1.setCellValue("For Official Use Only.");
               /*设置底部保密行样式*/
               cell1.setCellStyle(styleFouoRow(workbook));
            }
        }catch (Exception exp){
        throw exp;
      }
   return workbook; 
 }
 
    
    private static CellStyle styleFouoRow(HSSFWorkbook inWorkBook)
    {
       CellStyle styleForFouoRow = inWorkBook.createCellStyle();
       styleForFouoRow.setWrapText(true);
       styleForFouoRow.setVerticalAlignment(VerticalAlignment.CENTER);
       styleForFouoRow.setAlignment(HorizontalAlignment.CENTER);
           
       HSSFFont font = inWorkBook.createFont(); 
       font.setFontHeightInPoints((short) 11);
       font.setFontName("Calibri"); 
       styleForFouoRow.setFont(font); 
            
       return styleForFouoRow;    
    }
}

解决方案：实现横向按列分页

要实现横向拆分页面，需调整打印设置并正确配置列分页符，同时避免冲突参数：

1. 移除冲突的适配设置

你当前设置的sheet.getPrintSetup().setFitWidth((short)1)会强制所有列缩放到1页宽度，导致setColumnBreak失效。需删除该行，或设置为0（表示不强制宽度适配）。

2. 正确设置列分页符

使用sheet.setColumnBreak(int columnIndex)指定分页位置（列索引从0开始，setColumnBreak(6)表示在第6列和第7列之间插入分页符）。

3. 可选：设置横向打印

如果模板是横向布局，添加打印方向设置：

sheet.getPrintSetup().setLandscape(true);

4. 修改后的打印设置代码

在prepareColomnHeaders方法中替换原打印设置块：

/* 配置横向分页打印 */
sheet.setAutobreaks(true);
sheet.setFitToPage(true);
sheet.getPrintSetup().setFitHeight((short)0); // 不强制高度适配
sheet.getPrintSetup().setFitWidth((short)0); // 不强制宽度适配，让分页符生效
sheet.getPrintSetup().setLandscape(true); // 可选：横向打印

// 在第10列后插入分页符，可根据需求调整索引
sheet.setColumnBreak(10);

效果说明

设置setColumnBreak后，Excel会在指定列右侧插入分页符，浏览和打印时都会从该位置拆分页面。
确保列宽总和超过页面宽度，分页符才会显示预期效果；若列宽总和小于页面宽度，分页符不会生效。

内容的提问来源于stack exchange，提问作者prain99

如何在Apache POI的HSSFWorkbook中实现Excel横向分页？

完整代码 #

解决方案：实现横向按列分页 #

1. 移除冲突的适配设置 #

2. 正确设置列分页符 #

3. 可选：设置横向打印 #

4. 修改后的打印设置代码 #

效果说明 #