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?
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:
Set the Page Breaks: Use the addHorizontalPageBreak and addVerticalPageBreak methods to specify where the page breaks should occur.
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.
@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.
@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);
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?
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:
@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)
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.
@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);
I am iterating over the number of pages in a WorkSheet of a WorkBook and setting,
pgSetup.setPrintArea(strPrintArea);
after applying some cropping in each iteration for each page of the worksheet.
However the firstpage of the sheet is not getting added in the output pdf.
Means if a sheet contains 2 pages, only last page per sheet will be added in the pdf.
Please find the reference here: Ref.zip (1011 Bytes)
Please let me know how all the pages can be added to output pdf.
Can you give an example of :
setFitToPages(int wide, int tall)
Thank you for providing the code segment for cropping. However, we are unclear about your data, objects, and the underlying logic. It seems the issue might be related to your own code. To assist you effectively, we kindly request a standalone sample Java program or application, including the complete source code (e.g., .java files) that runs without compilation errors, along with a template Excel file. This will allow us to reproduce the issue on our end, evaluate your sample code, and potentially suggest adjustments to resolve the problem.
@aakanksha76
Please try worksheet.GetPrintingPageBreaks(）to get print areas, then set your excepted area as print area, the following codes show how to set a single page or multi pages to print area:
worksheet.PageSetup.PrintArea = null;
CellArea[] cas = worksheet.GetPrintingPageBreaks(sheetRenderOptions);
if(cas.Length >0)
{
//print one page
{
CellArea ca = cas[cas.Length - 1];
worksheet.PageSetup.PrintArea = CellsHelper.CellIndexToName(ca.StartRow, ca.StartColumn) + ":" + CellsHelper.CellIndexToName(ca.EndRow, ca.EndColumn);
}
//print multi pages
{
StringBuilder sb = new StringBuilder();
int count = 2;
for (int i = 0; i < cas.Length; i++)
{
CellArea ca = cas[i];
sb.Append(CellsHelper.CellIndexToName(ca.StartRow, ca.StartColumn) + ":" + CellsHelper.CellIndexToName(ca.EndRow, ca.EndColumn));
sb.Append(",");
}
worksheet.PageSetup.PrintArea = sb.ToString(0, sb.Length - 1);
}
}
I used the following simplest sample code using Aspose.Cells for Java only (without involving Aspose.PDF) with your original template Excel file. It works absolutely fine and the output PDF is fine tuned.
e.g., Sample code:
Workbook workbook = new Workbook("d:\\files\\sample.xlsx");
Worksheet curSheet = workbook.getWorksheets().get(0);
ImageOrPrintOptions printOption = new ImageOrPrintOptions();
printOption.setPrintingPage(PrintingPageType.DEFAULT); // Or other options like ALL_PAGES, ONLY_FIRST_PAGE
SheetRender sr = new SheetRender(curSheet, printOption );
CellArea[] cas = curSheet.getPrintingPageBreaks(printOption);
StringBuilder sb = new StringBuilder();
if(cas.length >0)
{
//print multi pages
int count = 2;
for (int i = 0; i < cas.length; i++)
{
CellArea ca = cas[i];
sb.append(CellsHelper.cellIndexToName(ca.StartRow, ca.StartColumn) + ":" + CellsHelper.cellIndexToName(ca.EndRow, ca.EndColumn));
sb.append(",");
}
}
System.out.println("Final Print area-> "+sb.substring(0, sb.length() - 1));
PageSetup pgSetup = curSheet.getPageSetup();
pgSetup.setPrintArea(sb.substring(0, sb.length() - 1));
PdfSaveOptions saveOptions = new PdfSaveOptions();
workbook.save("d:\\files\\out_java1.pdf", saveOptions);
Please find attached the output PDF file which has two pages as per your expectations. out_java1.pdf (26.6 KB)
Please run the above code snippet and get the output PDF file by Aspose.Cells for Java only and check if the PDF file is fine tuned or not. I guess since you are also using Aspose.PDF to add page labels to generate the final output PDF file, so the issue might be in the relevant code snippet.
Using the simplest code below also gives the desired results,
Workbook workbook = null;
workbook = new Workbook(sourceFilePath);
workbook.save(outputFilePath);
but for conversion of other excel files of different types we need to add the cropping code which I provided in above response.
I used the code you provided here but it is not working with my cropping code. Can you please try running with code I provided and help identify the issue?
I checked and tested your code snippet and here are my findings:
1). There is some issue with your logic with applyCropping() method and you should refine your logic and fix it accordingly. I spotted the line of code:
which is wrong and it does not covert whole printable area. it should give:
A1:L39, A40:L78
to set the whole printable area for both the pages to be rendered.
2). Moreover, for the line pdfSaveOptions.setPageCount(curSheetIdx); —> here the curSheetIdx variable has 1 value. Where as you need to render two pages and not single page.
Fixing/testing
a). I replaced the following line of code: