Worksheet.GetPrintingPageBreaks() gives incorrect pagebreaks using Aspose.Cells for .NET in C#

Here’s my C# code:

public void Main() {
var workbook = new Workbook();
workbook.Worksheets.Add();
var worksheet = workbook.Worksheets[0];
for (var i = 0 ; i < 2000 ; i++) {
worksheet.Cells[i, 0].Value = “A”;
}
worksheet.RescaleToPrintMinRowsPerPage(100, 40);
workbook.Save(“C:\test.xlsx”);
}

public static void RescaleToPrintMinRowsPerPage(this Worksheet worksheet, int minRowsPerPage, int minScale = 75) {
if (minRowsPerPage < 1)
throw new ArgumentException(“Minimum number of rows per page must be positive”);
var maxDataRow = worksheet.Cells.MaxDataRow;
if (maxDataRow == -1)
return;
var pageSetup = worksheet.PageSetup;
var startingIsPercentScale = pageSetup.IsPercentScale;
var startingZoom = pageSetup.Zoom;
pageSetup.IsPercentScale = true;
pageSetup.Zoom = Math.Max(pageSetup.Zoom, 100);
var options = new ImageOrPrintOptions();
var pageBreaks = worksheet.GetPrintingPageBreaks(options);
var maxDataPageArea = pageBreaks.Single(pb => maxDataRow >= pb.StartRow && maxDataRow <= pb.EndRow);
var lastPageRowCount = maxDataRow - maxDataPageArea.StartRow + 1;
while (pageSetup.Zoom > minScale && lastPageRowCount < minRowsPerPage) {
pageSetup.Zoom–;
pageBreaks = worksheet.GetPrintingPageBreaks(options);
maxDataPageArea = pageBreaks.Single(pb => maxDataRow >= pb.StartRow && maxDataRow <= pb.EndRow);
lastPageRowCount = maxDataRow - maxDataPageArea.StartRow + 1;
}

When I run the Main() method, I notice that the last time the while loop in the rescale method runs, pageBreaks is an array of 20 CellAreas running from rows 1 to 100, 101 to 200, …, 1901 to 2000, exactly the desired result of the rescale method. However, when I save and open the workbook, the page breaks are actually placed at intervals of 99 rows, and the last print area is from rows 1981 to 2000. Can anyone please replicate the issue and tell me the reason for this behavior?

@stockysailor,

Thanks for your query.

I have tried to execute the above sample code but could not compile it due the following line in the last while loop.

pageSetup.Zoom–;

Please correct this line of code and send us code again. Also send us an output XLSX file created by Excel which is actually desired. It will help us to compare the results of this code with the actual file which is required at the end.

@ahsaniqbalsidiqui

Thank you for your help. There should be two minus signs after pageSetup.Zoom (which is actually what I originally typed: for some reason the two minus signs do not show up with any space between them on your forum). The desired output is simple-- it’s a workbook with a single sheet which is blank other than the letter “A” in the first column from rows 1 through 2000. If you try to print with default options, the last printed page should contain at least 100 rows. As I explained above, while debugging, after the last iteration of the while loop has run, when you use Aspose to look at the cell areas printed within each page, it says that there are 20 pages which each contain exactly 100 rows. But then when I save the workbook (having made no additional changes to it), open it, and display the printing page breaks, I see 21 pages, the first 20 with 99 rows each and the last one with 20 rows.

@stockysailor,

We are working on this query and will provide our feedback soon.

@stockysailor,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46257 -Incorrect Print page breaks returned by Worksheet.GetPrintingPageBreaks()

@stockysailor

We have investigated it and have found out that, if you also save the workbook to PDF using Aspose.Cells API, you will find the output PDF file having 20 pages with 100 rows per page. In other words, Aspose.Cells calculated that one page can contain 100 rows, but because of little difference, Microsoft Excel calculated that one page can only contain 99 rows. Fix for this behavior will take some time. We will let you know as soon as it will be fixed.

@ahsaniqbalsidiqui

Thanks for following up, and please keep me posted on the status of the fix.

@stockysailor,

You are welcome.