Page Numbering of Excel files with multiple worksheets(ECDCTS-10372)

rnara · December 29, 2023, 11:19am

Hi Team,

When trying to transform excel to pdf, rendition is not generated as expected.

Input file has multiple worksheets and after rendition(using below Aspose code), the total number of pages in input file mis-matched with the Aspose rendered pdf file.

I have also verified with MS Excel 2019 by saving the file as pdf and the output is not in-line with Aspose.

I have used following code:
String fileName = “C:\Docs\9742\DMRS-02321-LLB.xlsx”;
Workbook document = new Workbook(fileName);
document.save(“C:\Docs\9742\DMRS-02321-LLB.pdf”);

I have attached the input file(DMRS-02321-LLB.xlsx), aspose output(DMRS-02321-LLB_Aspose.pdf) and save as pdf(DMRS-02321-LLB_SaveAsPdf.pdf).
DMRS-02321-LLB.zip (4.0 MB)

Thanks,
Bhupali

amjad.sahi · December 29, 2023, 11:57am

@rnara,

It might be due to your printer and page settings on your system/environment. On my end, I rendered the Excel file to PDF via Aspose.Cells for Java v23.12 and the output PDF has 111 pages. When I opened your Excel file and then saved it as PDF manually in MS Excel 2019, I got output PDF which has 103 pages in total. So, different environments with default printer/page settings produce different results.
Here is my sample code:
e.g.
Sample code:

String fileName = "f:\\files\\DMRS-02321-LLB.xlsx";
Workbook document = new Workbook(fileName);
document.save("f:\\files\\DMRS-02321-LLB.pdf");

Please find attached the zipped archive containing your output PDF files.
files1.zip (3.9 MB)

rnara · January 3, 2024, 6:23am

That’s what my question is -

Actual total page count in attached Excel file contains 125 pages.
Aspose.cells(using sample code) produces 115 pages.
Save excel as pdf in MS Excel 2019, produces 88 pages.

In your case also, both Aspose.cells(111 pages) and Save excel as pdf in MS Excel 2019(103 pages) are not inline.

Why either above option 1 and option 2 have the same page count or option 2 and option 3 have the same page count ?

Please let me if there is any additional setting to make it inline with the options combination I have given.

Thanks,
Bhupali

peyton.xu · January 3, 2024, 8:11am

@rnara
Generally, for Microsoft Excel, option 1 and option 3 should have the same page count. The paper size of your shared Excel saved pdf(“DMRS-02321-LLB_SaveAsPdf.pdf”) is “Letter”, however, the paper size set in the source xlsx is “A4”. This may be caused by the set that the default paper size is “Letter” of your default printer.

For different display settings of one machine, Excel may produce different results. At 100% display setting, Excel produces 114 pages.
ExcelPrintviewAndOutputPdf.zip (2.5 MB)

Aspose.Cells follows the 100% display setting. You will also get 114 pages with following code:

Workbook wb = new Workbook("DMRS-02321-LLB.xlsx");

for(int i = 0; i < wb.getWorksheets().getCount(); i++)
{
    //it is 1200 in the source file
    wb.getWorksheets().get(i).getPageSetup().setPrintQuality(600);
}


wb.save("output.pdf");

amjad.sahi · January 3, 2024, 8:53am

I re-tested your scenario/case. I download your source Excel file again from your post and converted the Excel file to PDF via Aspose.Cells for Java. I got 115 pages in count now in the output PDF (attached).
DMRS-02321-LLB_new1.pdf (2.0 MB)

I am not sure why the output PDF was different last time. Sorry for the inconvenience caused. Also, I checked rendered (printable) pages in MS Excel manually and as @peyton.xu told, I also got 114 pages in total as per his screenshot.

Please use 100% display settings and use the sample code (set print quality to 600) shared by @peyton.xu and you will also get 114 pages as MS Excel renders to PDF file.

rnara · January 4, 2024, 5:53am

I have saved the input Excel file using 100% display settings. And After manually saved as pdf in MSExcel2019 produces 88 pages and Using Aspose sample code (set print quality to 600) produces 94 pages which is not inline again. Please refer the attached input file and both output files. We seek an explanation. Thanks!
DMRS-02321-LLB.zip (4.0 MB)

peyton.xu · January 4, 2024, 7:05am

@rnara

It is similar for your new attached file, the paper size of your shared Excel saved pdf(“DMRS-02321-LLB_MSExcel2019SaveAsPdf.pdf”) is not right.

Could you please share us the page count when you printviewing the entire workbook in Excel?

rnara · January 8, 2024, 7:08am

Keeping 100% display settings, page count varies from one machine to another. I have shared the page count from our customer though I could see different count in my machine.

Excel -Microsoft 365
There are 4 sheets having total of 98 pages

DMR Index - 2 pages
Revision History - 1 Page
DMR Template - 2 pages
Immulite 2000 DMR - 93 pages

peyton.xu · January 8, 2024, 7:59am

@rnara

When you open the source in Excel with different default editing/authoring language or different printer, the page count may be different. So,

Please use the same printer.
Please use the same default editing/authoring language in Excel.(Excel->Options->Language)

default_editing_language.png1645×1350 127 KB

rnara · January 16, 2024, 9:29am

Hi, could you please provide a justification for the following question?
I’ve attached an Excel file with 19 pages and when saving as a PDF in Office 365, it generates 19 pages. However, Aspose produces only 17 pages. Can you explain why the behavior of Office and Aspose are not consistent?
test page numbering.zip (404.3 KB)

amjad.sahi · January 16, 2024, 9:53am

@rnara,

I opened your Excel file into MS Excel 2019 manually and checked the Print view for whole workbook (rendered) pages, it gives me 17 pages in count which is equal to the output PDF by Aspose.Cells for Java API (I also tested using your file), see the screenshot for your reference.
sc_shot1.png (70.9 KB)

Could you please open your Excel file into MS Excel take the print view (same as my attached screenshot) for the entire workbook and check total number of pages to be rendered. Give us the screenshot here.

rnara · January 16, 2024, 10:20am

Does Aspose cells follow Office 365?

amjad.sahi · January 16, 2024, 12:22pm

@rnara,

Aspose.Cells supports various versions of Microsoft Excel, including Excel 2007, 2010, 2013, 2016, and 2019. Aspose.Cells is not directly tied to Office 365, but it can work well with Excel files created in Office 365. Therefore, it is compatible with both Office 365 and the standalone versions of Excel from 2007 to 2019.

rnara · January 16, 2024, 1:48pm

The above excel fails to operate with Office 365 and Aspose though it works with Excel 2019.
Anyway, I shared another excel earlier which exhibits inconsistency. Notably, the input excel has 98 pages, Excel 2019 generates 88 pages, while Aspose produces 115 pages. The inconsistency in behavior between Excel 2019 and Aspose is evident. I’ve also shared a screenshot of the Excel print view for your reference.
Please provide a justification for this disparity.
DMRS-02321-LLB.zip (4.0 MB)

peyton.xu · January 16, 2024, 4:22pm

@rnara

peyton.xu:

Generally, for Microsoft Excel, option 1 and option 3 should have the same page count. The paper size of your shared Excel saved pdf(“DMRS-02321-LLB_SaveAsPdf.pdf”) is “Letter”, however, the paper size set in the source xlsx is “A4”. This may be caused by the set that the default paper size is “Letter” of your default printer.

For different display settings of one machine, Excel may produce different results. At 100% display setting, Excel produces 114 pages.
ExcelPrintviewAndOutputPdf.zip (2.5 MB)

Aspose.Cells follows the 100% display setting. You will also get 114 pages with following code:
Workbook wb = new Workbook("DMRS-02321-LLB.xlsx");

for(int i = 0; i < wb.getWorksheets().getCount(); i++)
{
    //it is 1200 in the source file
    wb.getWorksheets().get(i).getPageSetup().setPrintQuality(600);
}


wb.save("output.pdf");

After you setting default authoring/editing lanuage to English(US), you will get 114 pages at printview.
For page count in your Excel saved pdf, the issue is that the paper size is not right as described.
Screenshot_114pages.png (170.1 KB)

rnara · January 17, 2024, 5:42am

Though the above Aspose code produces 114 pages yet excel 2019 produces 88 pages only even after applying the settings mentioned above.
Please let me know the reason.
I have attached the screenshot of 100% display settings and “Letter” as page size for your reference.
DMR Index.PNG (62.0 KB)

DMR Template.PNG (95.1 KB)

Immulite 2000 DMR.PNG (114.1 KB)

PageSize.PNG (83.5 KB)

Revision History.PNG (85.1 KB)

Thanks!

peyton.xu · January 17, 2024, 6:48am

@rnara ,

100% display setting is the settings in Windows->Settings.
display_setting_100%.jpg (116.2 KB)
Please set “English(United States)” as default for authoring language in Excel->Options->Language.
Excel_default_authoring_language_English_US.png (106.4 KB)
reboot your machine, then open the source file in Excel, you will see 114 pages at Excel printview.(Please don’t change the paper size setting at Excel printview. As the paper size is set to A4 in every sheet.)
The page count at Excel printview should be same as the page count of Excel saved pdf file.
If not, please the change the default paper size to A4 of your default printer.

rnara · January 17, 2024, 2:42pm

Okay got it. But, is it possilbe to generate 114 pages by Aspose excluding below code?

for(int i = 0; i < wb.getWorksheets().getCount(); i++)
{
//it is 1200 in the source file
wb.getWorksheets().get(i).getPageSetup().setPrintQuality(600);
}

Any workaround?
Thanks!

amjad.sahi · January 17, 2024, 7:18pm

@rnara,

I am not sure that without using the suggested code segment you will be able to generate 114 pages on your end considering display settings, paper size and other factors. Anyways, we will look into it further and get back to you.

John.He · January 18, 2024, 1:11am

@rnara
By using the following code for testing, a PDF result of 115 pages can be generated. Please refer to the attachment. output_java.zip (1.8 MB)

The sample code as follows:

Workbook wb = new Workbook(filePath + "DMRS-02321-LLB.xlsx");

//for (int i = 0; i < wb.getWorksheets().getCount(); i++)
//{
//    //it is 1200 in the source file
//    wb.getWorksheets().get(i).getPageSetup().setPrintQuality(600);
//}


wb.save(filePath + "output_java.pdf");