Page breaks nothing inserting correctly

Aspose.Cells Product Family
1

Greetings Aspose support,

I have created an algorithm to manually generate pages based on a dynamic length of data, however, the page breaks are not inserting as I would expect them to.

I stamp the data manually by doing the following:

1) Stamp header
2) Stamp 30 entries from my datasource
3) Stamp footer
4) Insert page break
5) Repeat until all data from the data source is stamped

Each page is in landscape format with 45 rows per page. Every 40th row entry, I stamp the footer (which is 4 rows worth of data + 1 for a blank row) and then insert a page break on the 45th row.

A snippet is below:

if(rowNumber % 45 == 40)
{
AppendFooter(ref ws, (rowNumber + 1), new Dictionary<string, string>());
startOfNewPageRow = rowNumber + 5;
AppendHeader(ref ws, startOfNewPageRow, report.ReportType, new Dictionary<string, string>());
rowNumber = startOfNewPageRow + 8;

ws.HorizontalPageBreaks.Add(startOfNewPageRow);
}

What I am finding is that the first two pages are working as expected, (page breaks inserted on the 45th and 90th row), however on the third page it inserts the page break 134…instead of 135. Debugging it shows that I am adding ws.HorizontalPageBreaks.Add(135); to the collection as expected.

If you need more detail please let me know.

EDIT: I created a better snippet so you can see the issue easier…do the following, then look at the page layout in excel, you won’t see three page breaks.

Cell col = ws.Cells[1,1];
col.PutValue(“ARGH”);
col = ws.Cells[50, 1];
col.PutValue(“ARGH2”);
col = ws.Cells[131, 1];
col.PutValue(“ARGH3”);

ws.HorizontalPageBreaks.Add(45);
ws.HorizontalPageBreaks.Add(90);
ws.HorizontalPageBreaks.Add(135);


Dan

2

Hi Dan,

Thanks for your posting and using Aspose.Cells.

We have tested your issue with the following code. It inserts page breaks correctly. The first page break is at 45th row, second is at 90th row and third is at the 135th row.

I have attached the output xlsx file for your reference.

C#


Workbook workbook = new Workbook();


Worksheet ws = workbook.Worksheets[0];


Cell col = ws.Cells[136, 1];

col.PutValue(“ARGH3”);


ws.HorizontalPageBreaks.Add(45);

ws.HorizontalPageBreaks.Add(90);

ws.HorizontalPageBreaks.Add(135);


workbook.Save(“output.xlsx”);

Screenshot:
3

I copy and pasted the exact code into my instance and it doesn't work as your screenshot suggests.

Can I ask what version of Excel you are using to generate this file? Has this issue already been addressed in a past update?

The layout of the template is as follows:

45 rows per page - Landscape orientation.

Initial page layout boundary set to 45 rows..expands as you add data at every 45th row.

Thanks,

Dan

4

Hi Dan,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for .NET v7.7.1.4 it should fix your issue.

We have tested your issue with the above given code and we use MS-Excel 2010 to check the output xlsx file.

5

Hello, thanks for that. I tried the updated version and it didn't work, however, I am using MS 2013...I'll get one of the guys to try it with 2010 and report back.

Dan

6

No luck there, I used the latest version on Excel 2010 and it doesn't work. I'll try and put in some more investigation on my end to see if I can fix it, however, if there is anything else you can tell me to assist that would great, such as common pitfalls etc...

Dan

7

Hi Dan,

Thanks for your posting and using Aspose.Cells.

In order to put horizontal page break successfully at cell 135, you must insert something in any cell in row 135 or greater.

Please see the following code. I have inserted ARGH3 in cell B135 and you can see in screenshot that there is horizontal page break in 135th row.

C#


Workbook workbook = new Workbook();


Worksheet ws = workbook.Worksheets[0];


Cell col = ws.Cells[134, 1];

col.PutValue(“ARGH3”);


ws.HorizontalPageBreaks.Add(45);

ws.HorizontalPageBreaks.Add(90);

ws.HorizontalPageBreaks.Add(135);


workbook.Save(“output.xlsx”);

Screenshot:
8

Cool, well, I've finally managed to replicate it in a controlled environment! Exciting times.

I've attached the project for your consideration. Can you let me know if you see the issue?...I'd like to think I'm not going mad!

Thanks again,

Dan

9

Hi Dan,

Thanks for your sample project and using Aspose.Cells.

I was able to observe this issue. It is actually because of Print Area which is set to A1:AW45. If you delete it, then page breaks are inserted correctly and become visible.

Please try the following code at your end with your Example.xlsx file and see the results.

C#


Workbook workbook = new Workbook(“Example.xlsx”);

Worksheet ws = workbook.Worksheets[0];


ws.PageSetup.PrintArea = “”;


Cell col = ws.Cells[40, 1];

col.PutValue(“ARGH1”);

col = ws.Cells[80, 1];

col.PutValue(“ARGH2”);

col = ws.Cells[130, 1];

col.PutValue(“ARGH3”);


ws.HorizontalPageBreaks.Add(45);

ws.HorizontalPageBreaks.Add(90);

ws.HorizontalPageBreaks.Add(135);

workbook.Save(“output.xlsx”);

10

That was it, thanks a lot.

Did I miss that part in the documentation stating about removing the print area before adding page breaks? I can't remember seeing it.

Thanks again,

Dan

11

Hi Dan,

Thanks for your posting and using Aspose.Cells.

It is good to know that you were able to find the root cause of this issue. It is not actually mentioned in adding page breaks document but I observed, it is MS-Excel behavior. If you set the print area and then try to add page breaks, it will not work.

Let us know if you encounter any other issue, we will be glad to look into it and help you further.