Excel page break not reflected in generated pdf

Aspose.Cells Product Family
1

Hi,

I am evaluating your product to see if it can replace Excel Interop Print to PDF functionality. I have some excel documents with page breaks which are not reflected in the resulting PDF document. The Excel Print Preview and resulting PDF document do not match up. Is there some setting I missed to force the page breaks to be used? Please advise, here is the code that I am using:

    public void PrintToPdf(string filePath)
    {
        var workbook = new Workbook(filePath);

        var printOptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();
        foreach (var sheet in workbook.Worksheets)
        {
            // These page breaks do not match what is set in the Excel document
            var pageBreaks = sheet.GetPrintingPageBreaks(printOptions);
        }

        var pdfOptions = new PdfSaveOptions()
        {
            CalculateFormula = true
        };

        string pdfFileName = GetFilePathWithoutFileType(filePath) + ".pdf";
        workbook.Save(pdfFileName, pdfOptions);
    }

    public string GetFilePathWithoutFileType(string filePath)
    {
        string[] filePathParts = filePath.Split('.');
        return filePathParts[0];
    }

Thanks,
John

2

@joneilbc,

Thanks for the sample code segment and details.

Could you provide template Excel file(s) and resultant PDF document(s), we will evaluate your issue on our end.

4

@joneilbc,

Thanks for the template file.

After an initial test, I am able to reproduce the issue as you mentioned by simply converting your template file to PDF file format. I found that MS Excel page breaks are not reflected properly in the output PDF:
e.g
Sample code:

 Workbook WrkBook = new Workbook(stringFileName);
            Aspose.Cells.PdfSaveOptions PdfSaveOptions = new Aspose.Cells.PdfSaveOptions();
            WrkBook.Save("e:\\test2\\out1.pdf", PdfSaveOptions);

I have logged a ticket with an id “CELLSNET-46691” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

5

@joneilbc,

We evaluated your issue further. As you can see in Microsoft Excel(see attachment “Microsoft_Excel_calculated_scale.png”), 1 page width and two pages height is set, Microsoft Excel calculates the page scale is 60%. But when you take printview it in Excel, you can find that there is much white space at the end of the second page(see attachment “Microsoft_Excel_white_space.png”, print gridlines is enabled).

However, we calculate the page scale is 66%, you can check it with the following code:

Workbook wb = new Workbook(srcFile);

SheetRender sr = new SheetRender(wb.Worksheets[0], new ImageOrPrintOptions());
Console.WriteLine(sr.PageScale);

Now, let’s check whether page scale is good for the page setup(1 page width and 2 pages height).
Firstly, reset all page breaks(see attachment “reset_page_breaks.png”), because there is a horizontal page break, if it is not being cleared, it will take effect while page scale is set. Secondly, set page scale to 66%(see attachment “set_page_scale.png”). Thirdly, take printview it in Excel, you will see the sheet is fit to two pages well. There is not too much white space at the end of second page. The page break is same as the one in our generated PDF.

Fourthly, you can also try to set page scale to 67%(same as the second step), you will find the sheet is printed to three page.

So, we think the 66% is good for the page setup(1 page width and 2 pages height), but 60% is too small.
Microsoft_Excel_calculated_scale.png (134.3 KB)
Microsoft_Excel_white_space.png (119.8 KB)
reset_page_breaks.png (89.9 KB)
set_page_scale.png (133.9 KB)

6

I do not have control over setting the page breaks of the documents that I need to process. My requirement is that the resulting PDF document must match what Excel Print to PDF produces.

Can you make the resulting PDF document match the what is shown in the Excel Print Preview?

Thanks,
John

7

@joneilbc,

Thank you for your comments. We are discussing this issue at our end and will write back here once nay feedback is ready to share.

8

@joneilbc,

We do not want you to do any page break changes before you process files. The previous comment was meant to let you do an experiment to prove that Microsoft Excel does wrong for this file: The file has 1 page width and 2 pages height set, but in the printview, there is much white space at the end of second page. That is Microsoft Excel calculated page scale (60%) is too small.
However, we calculated page scale is 66%, which makes the sheet fit to two pages well (only little white space at the end of second page).

In other words, the API can not do the same wrong thing as Microsoft Excel does.