XLS table of contents based on the number of Printed pages

Aspose.Cells Product Family
1

Hi,


I’m producing a large report in xls - most tabs contain multiple page breaks.

When it gets printed (or in Print preview), I need to produce a Table of Contents that takes into account the multiple pages/page breaks.

I can’t find any information on this site anywhere - is there anywhere you could point me that might have some info on this?

I’m using Aspose.Cells version 5.1.4.0

Many thanks
Anne
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.1.6

If you want to get rid of page breaks and print your worksheet into a single page, then please use

ImageOrPrintOptions.OnePagePerSheet = true

It will then render your worksheet into a single page.

3

Hi Anne,


Thank you for using Aspose.Cells.

Can you please elaborate your requirements with the help of a screenshot? You can do so by using MS Excel and capturing the screen. We would like to know your requirements exactly and how it is done using MS Excel. This will help us assist you further in a better way.

4

Hi,


I don’t want to change the number of printed pages -

I want to COUNT the page breaks that each tab has - then produce a TABLE OF CONTENTS (which will display how many pages are printed)

For example, the printed workbook will have these “contents”:

Tab1: “Commentary” ----> pages 1-3
Tab2: “Report A”--------> pages 4-5
Tab3: “Charts 1-4” ------> pages 6-9

My report, when printed, may extend to 50 or so pages, but the user needs to see on what page number each new “section” (Worksheet) starts.

What is the easiest way to iterate through each Worksheet & count the NUMBER OF PAGES THAT will be printed for that worksheet?

thanks
Anne

5
amcc:
Hi,

What is the easiest way to iterate through each Worksheet & count the NUMBER OF PAGES THAT will be printed for that worksheet?

thanks
Anne

Hi,

You can calculate how many pages will a sheet take when it will be rendered using SheetRender class.

Please see the following code and try it and let me know your feedback.

C#
string filePath = @"F:\Shak-Data-RW\Downloads\Source.xlsx";

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];


ImageOrPrintOptions opts = new ImageOrPrintOptions();


SheetRender sr = new SheetRender(worksheet, opts);


int n= sr.PageCount; // Number of Pages this sheet will take


6

Hello again!!

I've tried the above code - it brings out some very large numbers for the SheetRender.PageCount...

I've looked at the Print Preview of a tab, which would print onto 14 pages - the page count gives it a value of 88 pages!!

For other pages that expand downwards when the Smart markers are filled in, the page count is coming out as exactly double the number of printed sheets - like its expanding horizontally to the right as well (which the Print Preview is not).

is there another property that I need to be setting in either ImageOrPrintOptions or the SheetRender classes that will make the page count more accurate?

I've also checked the template for "hidden" page breaks - all the smart markers sit on one single page.

Please advise

Thanks

Anne

7

Hi,

It could be a bug of Aspose.Cells for .NET.

Could you please provide me sample input xls/xlsx files for testing?

Please also download and use the latest version:

Aspose.Cells for .NET 7.2.2

8

Hi shakeel,

we are 2 weeks from the end of a HUGE project, I cannot upgrade any external libraries at this stage - the regression testing would be impossible!

Please advise on what might be causing this?

I'm using the code you sent

thanks

Anne

9

Hi Anne,


I have checked the code as provided by Shakeel at my end with a sample file, and it returns exactly the same number of pages as populated in the worksheet. To reproduce the issue and assist you further in a productive way, we need your sample excel file that is raising the issue. Please provide us with your sample file, so that we can look into it.

To mention, I am using Aspose.Cells V 7.2.2.
10

Hi,

I think, you are using the older version that’s why you are not getting the page count correctly.

We have tested the page count issue at our end with sample files and it is returning the correct number of pages.

Also, other customers did not get any problem using the above code, they successfully used it without any problems.

Please download and use the latest version:

Aspose.Cells for .NET 7.2.2

11

Like I said - no upgrading this close to the end of a project.

Last time we did an upgrade of Aspose, it took us a week to fix all the obsolete references - the backwards compatibility was very bad & broke many of our reports!

What would the backwards compatibility be like between v7.2.2 & the version we are using: v5.1.4.0 ?

thanks

Anne

12

Hi,

We will think of a workaround and share with you.

Please note, there are not any major changes between 7.2.2 and 5.1 version, so you will be able to upgrade quickly. You can take the backup of your existing code and just add the reference to latest version and see what problems you face.

I think, there are no API changes also between these two versions.

13

Hi,

has anyone managed to come up with a solution/workaround for this bug?

I've attached an example of one of out report outputs:

1. Register Structure History = 3 printable pages, according to the Print Preview.

But the int renderedPages gives me a value of 1.

2. Contact History Details = 2 printable pages, according to the Print Preview.

But the int renderedPages gives me a value of 5.

The code used is below, where sheet is the worksheetName:

int renderedPages = 0;

ImageOrPrintOptions options = new ImageOrPrintOptions();

SheetRender sr = new SheetRender(sheet, options);

renderedPages = sr.PageCount

Please help me in a workaround, without having to upgrade - this will not be possible.

Many thanks

Anne

14
Hi,

I can find the second issue using v7.2.2 i.e.:

2. Worksheet: "Contact History Details" = 2 printable pages, according to the Print Preview.

But the int renderedPages gives me a value of 4 and not 5.

I could not find the first issue as it works fine and gives 3 as page count for the "Register Structure History" worksheet.

Sample code (v7.2.2):

Workbook book = new Workbook("e:\\test2\\Aspose+sample.xls");

ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();

Worksheet sheet = book.Worksheets["Contact History Details"];

SheetRender sr = new SheetRender(sheet, imgOptions);
int j = sr.PageCount; //Gives 4 instead of 2

I have logged a ticket with an id: CELLSNET-40769. We will look into it soon.


Well, we can only recommend you to kindly upgrade to latest versions as we cannot provide any fix based on the older version that you are using, it is against our internal policy. Also, I am afraid since you are using older version of the product and there is no workaround available to cope with the issue.

Sorry for any inconvenience caused!


Thank you.
15

Hi Amjad,

thanks for your reply.

If we were to upgrade at this point - the current Aspose version will not work anyway, as you have told me you lodged a bug.

My question is - when will you put out a patch/fix for this??

I must get a solution to this issue by early next week - do you think you'll be able to help us out by releasing Tuesday or Wednesday?

Thanks

Anne

16

Hi,


We are working over your issue. We will try out level best to fix it within the next 3-4 working days. Once it is resolved, we will attach the fix immediately here.

Hopefully the fix will be provided in the early next week.

Thank you.
17

Hello Amjad,

How is the fix going?

I haven't upgraded because you had found the bug - I won't be able to do anything after today. This is the final day of development befor stabilisation & upgradign a library after today will be impossible.

Can you PLEASE let me know if we are any closer to a fix for this bug?

Many thanks for your help

Anne

18

Hi,

Thanks for your patience.

I have added your comment in our database against the issue id: CELLSNET-40769

We will try to provide you a fix as soon as possible.

19

Hi,


We are working over your issue and hopefully, we can provide you the final fix in 1-2 days.

Sorry for any inconvenience caused!
20
Hi,

Could you try to set the following options:
e.g
ws.PageSetup.FitToPagesWide = 1;
ws.PageSetup.FitToPagesTall = 0;

Give us your feedback, if this does not solve your issue, we can provide a hotfix today/tomorrow.

Thank you.