Long Pages when AllColumnsInOnePagePerSheet is set to true

Aspose.Cells Product Family
1

Hi,

Scenario:
It appears that when generating PDF files from excel, the page height was being set wrongly when the PdfSaveOptions AllColumnsInOnePagePerSheet is set to true.

When the PdfSaveOptions OnePagePerSheet is set to true, the content displayed are being sized correctly such that when there are only few rows of data, the page height is set to only display the few rows.

However, OnePagePerSheet does not cater for long sheets such that when one sheet containing over 15000 rows of data, the page will be over-sized and hard to be viewed.

Below are my questions:

  1. Is there any way to set the page height dynamically caters both when there are many data and when there is too less data?
    Such that use generated page height when there are too many data and display only few rows of data like OnePagePerSheet when there is too less data. (Handling sheets differently)

  2. Example [excel]: Sheet1 contains 5 rows of data. Sheet2 contains 15000 rows of data.
    Expected output: Page1’s page height dynamically sized according to number of rows. Page2 automatically separates the data into multiple pages. (Or at least displaying it with minimum page size of what is being set by Excel’s paper size).

Sample Code:
var pdfSaveOptions = new PdfSaveOptions
{
OnePagePerSheet = false,
AllColumnsInOnePagePerSheet = true,
CalculateFormula = false
};
workbook.Save(pdfPath, pdfSaveOptions);

Attachments:
None.pdf have both option OnePagePerSheet and AllColumnsInOnePagePerSheet disabled
AllColumnsInOnePagePerSheet.pdf have OnePagePerSheet disabled and AllColumnsInOnePagePerSheet enabled
OnePagePerSheet.pdf have both option OnePagePerSheet and AllColumnsInOnePagePerSheet enabled
Sample Excel and output.zip (102.0 KB)

Aspose.Cells version 21.6.0

Hope my questions are clear.

Thanks in advance.

2

@cson,

Thanks for the sample documents.

It seems you want to use PdfSaveOptions.AllColumnsInOnePagePerSheet, and want the page to be set height to adapt the content when there are only few rows to be rendered. And, yes, OnePagePerSheet option adjusts paper size, i.e., height, widths based on contents but
AllColumnsInOnePagePerSheet does not do that. Please note, AllColumnsInOnePagePerSheet keeps the paper height set as per the page setup settings for the sheet. We have to follow this, so we cannot figure out any better way to match with your custom requirements.

To choose when to use these options (OnePagePerSheet, etc.), you may instantiate ImageOrPrintOption and evaluate its PageCount attribute to know how many pages would be rendered, so you may better judge if you should use the option or not.

3

Hi Amjad_Sahi,

Thanks for your prompt reply.

Another question is that is it possible to use the option (OnePagePerSheet, etc.) at worksheet level instead of applying it to entire workbook?

Thanks a lot.

4

@cson,

OnePagePerSheet works at Workbook level and cannot be affective for single sheet only. But there are Fit to Pages tall and wide options in MS Excel. For your specific needs, you may try to use/specify fit to pages tall and fit to pages wide PageSetup options for your desired sheet only, see the sample lines of code for your reference:
e.g.
Sample code:

..........
//Get first worksheet
Worksheet ws = workbook.Worksheets[0];
ws.PageSetup.FitToPagesTall = 1;
ws.PageSetup.FitToPagesWide = 1;
............

Hope, this helps a bit.

5

@Amjad_Sahi,

Thanks a lot for the solution. :smile:

6

@cson,

Good to know that the suggested solution works for your needs. Feel free to write us back if you have further queries or issue, we will be happy to assist you soon.