I have dynamically generated data that I have exported to excel (after applying some formatting). Now before being able to print I need to apply the page break in such a way that any row of data is not going to be divided among different pages. Data that falls into one row has be to displayed continuously on one printed page. Rows have border properties. Sometimes data in a row can be very short and some time it my be very long and sometime not at all (in that case i am hiding the rows).
Any help is appreciated.
Hi Andy,
Thank you for contacting Aspose support.
I have studied your presented scenario. No doubt it could be difficult to calculate where to put the printing page breaks when you are populating the data dynamically. Unfortunately, there is no hard n fast rule on how to calculate the page breaks however here a few tips that might help you in achieving your goals.
- You can get the printing page breaks of an existing worksheet that will help you understand where the pages will break. You can use the Worksheet.GetPrintingPageBreaks method that will return an array of CellsArea which in turn could tell you where the current page breaks are. You can use this information to move the breaks at your desired locations. You can also call the aforesaid method just before saving the spreadsheet so that the API could calculate the page breaks according to the amount of data and considering the formatting (font size) as well as the height of rows and widths of the columns, in case you are calling AutoFitRows & AutoFitColumns.
- You can use the Worksheet.AddPageBreaks method or Add methods from HorizontalPageBreakCollection & VerticalPageBreakCollection classes to put the page breaks on the desired locations. At the bottom of this post, you will find the code snippet to add manual page breaks.
C#
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Clearing all page breaks
workbook.Worksheets[0].HorizontalPageBreaks.Clear();
workbook.Worksheets[0].VerticalPageBreaks.Clear();
//Add a page break at cell Y30
workbook.Worksheets[0].HorizontalPageBreaks.Add(“Y30”);
workbook.Worksheets[0].VerticalPageBreaks.Add(“Y30”);
That said, I suggest you to use the FitToPagesTall & FitToPagesWide options offered by PageSetup class. This way, you can fit the contents of the worksheet to specified number of printing pages. For instance, if you set the PageSetup.FitToPagesWide as 1 and PageSetup.FitToPagesTall as 0, the resultant print will fit all columns of the worksheet to one page or you can tweak these settings to get desired results according to your data.
In case you still face any difficulty in achieving your goals, please provide us your sample spreadsheet (after populating the data and applying the formatting) for further investigation.
Thank you for pointing few methods. But it does not seem to help me in my scenario. How do I send you the spreadsheet for your review?
Hi Andy,
Thank you for writing back.
I have already received your sample spreadsheet via an email. Upon reviewing it, I was not able to find a dynamic way to insert the page breaks at the end of a section, however, I can suggest you a simpler solution, that is; to fit the contents of the worksheet on a single page of the printed document/PDF. This way, you do not have to worry about overflowing of the sections to the next page and the resultant printed/PDF page will be of same size as defined in the PageSetup of the worksheet.
Please try the following piece of code on your side and let us know if you are satisfied with the outcome.
C#
var book = new Aspose.Cells.Workbook(dir + “aspose formatting.xls”);
book.Worksheets[0].PageSetup.FitToPagesTall = 1;
book.Worksheets[0].PageSetup.FitToPagesWide = 0;
book.Save(dir + “output.pdf”, new PdfSaveOptions());
Thank you for your response. This works if I am limited to few page documents. If I have to print more pages and constraining to fit all in one page does not come out good. In fact the report is unreadable.
Hi Andy,
That is correct, the provided solution is appropriate if there are a few printing page. In case there are more printing pages, I currently do not have a synamic solution to determine where to put manual page breaks so that contents are not overflowed to the next page. I would request you to please share another spreadsheet containing more data/printing pages. Please also insert manual page breaks (where you wish them to be) in the spreadsheet using Excel application and share it here along with details on how you have split the data on pages.
Thank you for your cooperation.
Hi,
Thanks for using Aspose.Cells.
It seems that you need a whole row to be displayed in one page. So you have two options while rendering to pdf.
1. Set
FitToPagesWide = 1
FitToPagesTall = 0
With this setting, we will shrink a row to one page width when the content of the row is larger than the page width. But this has the limit that the minimum shrink value is 10%, when the content of a row is too long, it may be divided to several pages.
2. Set in PdfSaveOptions
AllColumnsInOnePagePerSheet = true
With this setting, we will expand the page width when the content of a row is larger than the page width. The width of paper size of PageSetup will be ignored as our API document stated.
Thank you, I am sending you the spreadsheet. Hope this one helps.
here, the 4th section is very long (I know it will not fit in single page, but it should start in its own page. So, in this scenario I need a page break in after row 13. But like I said, this is dynamically generated content. There may be situation where this section is really small and other section may be even longer.
After opening the spreadsheet, when you try to print from file menu on (8.5*11)
This feature does not work well when i have more than 2 pages document. In my case it is random. Sometime it can be less than a page and sometime it can be 20 pages long.
Hi Andy
AndyCooper:
Thank you, I am sending you the spreadsheet. Hope this one helps.
here, the 4th section is very long (I know it will not fit in single page, but it should start in its own page. So, in this scenario I need a page break in after row 13. But like I said, this is dynamically generated content. There may be situation where this section is really small and other section may be even longer.
After opening the spreadsheet, when you try to print from file menu on (8.5*11)
Thank you for the sample spreadsheet. Just to make sure that we are on the same page, if the 4th section is too large, it may span over several pages. Is your requirement is to start the 4th section (or any other section) from a new page, regardless that a section may occupy more than pages to display all contents?
Hi again,
I believe you could use the discontinued print areas to achieve your goal. Please check the following piece of code and its resultant PDF. You will notice that each section is getting printed on a separate PDF page in predefined paper size.
Please note, the code is hard coding the print area settings, however, at the time of populating the data, you could extract this information and consequently formulate the string that could serve as PrintArea property. This is because, at the time of populating the data, you will have the information about the matrix (rows x columns) which is being effected.
C#
var book = new Workbook(dir + “aspose formatting.xls”);
var sheet = book.Worksheets[0];
var pageSetup = sheet.PageSetup;
pageSetup.PrintArea = “A4:F7,A9:F9,A11:F11,A15:F35,A37:F41,A43:F43”;
book.Save(dir + “output.pdf”, Aspose.Cells.SaveFormat.Pdf);
In case the above provided solution is not acceptable to you, please update you sample spreadsheet with Excel to put manual page breaks (at positions where you wish them to be) and share the sample with us. Moreover, please tell us what do you want to do with blank rows, that is; do you wish them to be printed or skipped.
That is correct. The section only need to start from new page if the section is not going to fit in that page. However if the section is very long and contain multiple page of data then it can break automatically.
I cannot have each section printed out on each page. I only need to print the section which is large enough to fit in single page. However if section is large and does not fit in one page then they can span over multiple pages with automatic page break.
Hi Andy,
The solution shared in my previous response is flexible to accommodate both of your concerns as detailed below.
- If you do not wish to render each section to separate page, you can merge those sections in a single print area. For instance, if you wish to 1, 2, 3 & 4 on single page, all you have do is to set the PrintArea property as “A1:F13,A15:F35,A37:F41,A43:F43”. Please note, the last two sections at the bottom will still be split with above PrintArea settings, however, you can also merge them as suggested above.
- In case a specified print area has contents spanning over multiple pages then such print area will automatically be split according to the predefined paper size, that is; as per automatic page breaks.
Please give this solution a try on your end and let us know your feedback.
That said, If you are still not satisfied with the solution, we need you to provide us an example spreadsheet showing how you wish to split the contents. You can open your sample in Excel application and put manual page breaks. More appropriately, take another sample containing more data/printing pages and use it to show your desired results.