Apache POI page break in excel

Question

Hi guys I just want to ask about how to remove the page break in column N in excel using the Apache poi in Java. I've already set up in my code that my print area is until column P. But when I try to print the generated excel it always set that the page break is in column N.

Generated Excel

How can I adjust the page break in column using Apache poi on java?

Looks like its only possible to set specific page sizes. Example — XtremeBaumer
@XtremeBaumer: No there is Sheet.setAutobreaks and Sheet.setFitToPage which then could be combinated with PrintSetup.setFitWidth and PrintSetup.setFitHeight. But the question is not clear about what exactly is the goal. There is not even code shown at all. — Axel Richter
@AxelRichter The goal is to have the page break on column P rather than column N. With the methods you mentioned, I can't think of any way to set the page size to something specific as columns, yet alone pixels. So I would be happy to see an answer from you achieving exactly that — XtremeBaumer

Axel Richter Axel Richter · Accepted Answer · 2019-03-18T14:13:30

If the goal is that at print all columns (A:P) fit to one page width, then one could solve this using Scale a worksheet.

The following code uses Sheet.setAutobreaks and Sheet.setFitToPage combinated with PrintSetup.setFitWidth and PrintSetup.setFitHeight to set the print setup to scale to fit. The width is set to fit all columns to 1 page width and the height is set automatic, so multiple pages are used when enough rows are present.

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*; 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateExcelAllColumnsFitToOnePageWidth {

 public static void main(String[] args) throws Exception {
  //Workbook workbook = new HSSFWorkbook();
  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet();

  for (int r = 0; r < 200; r++) {
   Row row = sheet.createRow(r);
   for (int c = 0; c < 16; c++) {
    Cell cell = row.createCell(c);
    cell.setCellValue("Lorem ipsum");
    if (r==0) sheet.autoSizeColumn(c);
   }
  }

  sheet.setAutobreaks(true);
  sheet.setFitToPage(true);
  PrintSetup printSetup = sheet.getPrintSetup();
  printSetup.setFitHeight((short)0);
  printSetup.setFitWidth((short)1);


  String fileout = (workbook instanceof HSSFWorkbook)?"Excel.xls":"Excel.xlsx";
  FileOutputStream out = new FileOutputStream(fileout);
  workbook.write(out);
  out.close();
 }
}

Apache POI page break in excel

1 Answers