Add a page break in excel at end of each page in sheet when converting excel workbook to pdf

Aspose.Cells Product Family
,
1

Hello,

I am working on a Project to convert excel files to pdf with Aspose.Cells version 25.6.
Currently I am using below code for conversion:

final PageSetup pgSetup = curSheet.getPageSetup();
if (pgSetup != null && strLastRowNum != null && strLastColName != null) {
    final String strPrintArea = (strFirstColName + strFirstRowNum + ":" + strLastColName + strLastRowNum);

    pgSetup.setPrintArea(strPrintArea);
	
} else {
    LOGGER.error("strLastRowNum OR strLastColName - is null");
}

where curSheet is a com.aspose.cells.Worksheet.

However, if multiple pages exist in the same sheet the pages get merged which is not desired. Every page in the excel should be converted to individual page in pdf.

How do I add a page break at the end of each page in the sheet?

Thanks,
Aakanksha

2

@aakanksha76

To add a page break in an Excel sheet when converting it to PDF using Aspose.Cells, you can set the page breaks programmatically. This ensures that each page in the Excel sheet is treated as a separate page in the resulting PDF.

Here’s how you can do it:

  1. Set the Page Breaks: Use the addHorizontalPageBreak and addVerticalPageBreak methods to specify where the page breaks should occur.

  2. Convert to PDF: After setting the page breaks, proceed with the conversion to PDF.

Here’s a sample code snippet demonstrating this:

// Assuming curSheet is your Worksheet object
final PageSetup pgSetup = curSheet.getPageSetup();

// Set the print area if needed
if (pgSetup != null && strLastRowNum != null && strLastColName != null) {
    final String strPrintArea = (strFirstColName + strFirstRowNum + ":" + strLastColName + strLastRowNum);
    pgSetup.setPrintArea(strPrintArea);
}

// Add horizontal page breaks after specific rows
curSheet.getHorizontalPageBreaks().add(5); // Add a page break after the 5th row
curSheet.getHorizontalPageBreaks().add(10); // Add another page break after the 10th row

// Convert to PDF
PdfSaveOptions saveOptions = new PdfSaveOptions();
saveOptions.setPrintingPageType(PrintingPageType.IGNORE_BLANK);
curSheet.getWorkbook().save("output.pdf", saveOptions);

In this example, page breaks are added after the 5th and 10th rows. Adjust the row numbers based on your specific requirements. This will ensure that the pages in the Excel sheet are preserved as separate pages in the PDF output.

3

@aakanksha76
When exporting a worksheet containing multiple pages, each page will be a separate PDF page.

If you encounter situations where several pages are merged into a single PDF page, please check your PDF export code to see if the “OnePagePerSheet” property is set to true.

PdfSaveOptions saveOptions = new PdfSaveOptions();
saveOptions.setOnePagePerSheet(true);

You can also refer to the following sample code to add page breaks.

// Add horizontal page breaks after specific rows
// Add a page break after the 5th row
curSheet.getHorizontalPageBreaks().add(5); 
// Add another page break after the 10th row
curSheet.getHorizontalPageBreaks().add(10); 

// Add vertical page breaks after specific rows
// Add a page break after the 5th column
curSheet.getVerticalPageBreaks().add(5);
// Add a page break after the 10th row
curSheet.getVerticalPageBreaks().add(10);

If you still have issues, please provide complete and runnable test code and sample file, so we can reproduce the issue. We will check it soon.

4

Thank you for your response…

OnePagePerSheet property is set to true, but output pdf contains the merged pages.

And, not able to use,
curSheet.getHorizontalPageBreaks().add(5);

as, there are many files to be processed by same application and the position of page break is not known.

Please find sample excel file here:
sample.zip (10.6 KB)

Thanks,
Aakanksha

5

@aakanksha76
Please comment out the code that sets the OnePagePerSheet property. If the OnePagePerSheet property is set to true, only one PDF page will be exported from each worksheet. Setting OnePagePerSheet to true will result in page merging.

By testing on the latest version v25.10 using the following sample code, we can obtain the correct results. Please refer to the attachment. out_java.pdf (44.9 KB)

PdfSaveOptions saveOptions = new PdfSaveOptions();
//saveOptions.setOnePagePerSheet(true);

Workbook book = new Workbook(filePath + "sample.xlsx");

book.save(filePath + "out_java.pdf", saveOptions);

Hope helps a bit.

6

Thank you for your response…

I tried removing the OnePagePerSheet property but did not get the desired results the code we are using is customised to handle multiple excel types.

Since I am using a little customized way to convert xlsx to pdf,
I am unable to directly use the,
book.save(filePath + “out_java.pdf”, saveOptions);
but I agree that It gives the desired results.

I would like to know if there is a way to find out where a page break is and apply it there in the current sheet?

Thanks,
Aakanksha

7

@aakanksha76,

See the following sample code on how to get different pages coordinates or cells areas (based on printing page breaks) for your reference.
e.g.,
Sample code:

Workbook workbook = new Workbook("d:\\files\\sample.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);

        CellArea[] cellAreas = null;
        try {
            cellAreas = sheet.getPrintingPageBreaks(new ImageOrPrintOptions());
        } catch (Exception e) {

            e.printStackTrace();
        }
        for(CellArea cellArea : cellAreas)
        {
            System.out.println("Cell Area:" + cellArea);
        }

Hope, this helps a bit.

8

Thank you for your response…

I tried using

CellArea[] cellAreas = null;
try {
    cellAreas = curSheet.getPrintingPageBreaks(new ImageOrPrintOptions());
} catch (Exception e) {

    e.printStackTrace();
}
for(CellArea cellArea : cellAreas)
{
    curSheet.getHorizontalPageBreaks().add(cellArea.EndRow);
    System.out.println("Pagebreak added at Row: "+cellArea.EndRow);
}

but,

curSheet.getHorizontalPageBreaks().add(cellArea.EndRow);

doesn’t add a page break at the end of the row provided. And output is same as earlier.
Below is the code I am using to set the print area:

final PageSetup pgSetup = curSheet.getPageSetup();
if (pgSetup != null && strLastRowNum != null && strLastColName != null) {
    final String strPrintArea = (strFirstColName + strFirstRowNum + ":" + strLastColName + strLastRowNum);
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Print Area - " + strPrintArea);
    }
      curSheet.getHorizontalPageBreaks().add(39); 
    pgSetup.setPrintArea(strPrintArea);
} else {
    LOGGER.error("strLastRowNum OR strLastColName - is null");
}

In above code also,

curSheet.getHorizontalPageBreaks().add(39);

doesn’t seem to make a difference.

Can you please help me understand why a page break is not getting added here?

Thanks,
Aakanksha

9

@aakanksha76
By changing the position of the inserted horizontal page break and testing it on v25.10 using the following code, we can obtain the expected results. Please refer to the attachment. out_java.pdf (44.9 KB)

Workbook workbook = new Workbook(filePath + "sample.xlsx");
Worksheet curSheet = workbook.getWorksheets().get(0);

CellArea[] cellAreas = null;
try {
    cellAreas = curSheet.getPrintingPageBreaks(new ImageOrPrintOptions());
} catch (Exception e) {

    e.printStackTrace();
}


for(CellArea cellArea : cellAreas)
{
	System.out.println("Cell Area:" + cellArea);
	int pageBreak = cellArea.EndRow + 1;
    curSheet.getHorizontalPageBreaks().add(pageBreak);
    System.out.println("Pagebreak added at Row: "+pageBreak);
}

PdfSaveOptions saveOptions = new PdfSaveOptions();
workbook.save(filePath + "out_java.pdf", saveOptions);

The output:

Cell Area:Aspose.Cells.CellArea(A1:L39)[0,0,38,11]
Pagebreak added at Row: 39
Cell Area:Aspose.Cells.CellArea(A40:L78)[39,0,77,11]
Pagebreak added at Row: 78

If you still have questions, please provide complete and executable test code, result files, and expected PDF files. You can manually insert page breaks in Excel and export the expected result file, and we will check it soon.

11

@aakanksha76
By deleting unknown classes and variables, commenting out the following code, and testing on v25.10, we can obtain the correct results. Please refer to the attachment. out_java.pdf (47.8 KB)

final com.aspose.cells.PdfSaveOptions pdfSaveOptions = new com.aspose.cells.PdfSaveOptions();
// all columns in one page
// If OnePagePerSheet is true , all content of one sheet will output to only one
// page in result. The paper size of page setup will be invalid, and the other
// settings of page setup will still take effect.
//pdfSaveOptions.setOnePagePerSheet(true);
//pdfSaveOptions.setWarningCallback(warningCallbackCells);
//pdfSaveOptions.setPageCount(curSheetIdx);

If the page count setting is retained, only one page will be exported. Please refer to the attachment. out_java_2.pdf (35.4 KB)

final com.aspose.cells.PdfSaveOptions pdfSaveOptions = new com.aspose.cells.PdfSaveOptions();
// all columns in one page
// If OnePagePerSheet is true , all content of one sheet will output to only one
// page in result. The paper size of page setup will be invalid, and the other
// settings of page setup will still take effect.
//pdfSaveOptions.setOnePagePerSheet(true);
//pdfSaveOptions.setWarningCallback(warningCallbackCells);
pdfSaveOptions.setPageCount(curSheetIdx);