Converting excel file with large number of columns into pdf file using aspose cells

Aspose.Cells Product Family
1

hello


i am trying to convert excel file with large number of columns into pdf

if i try to view the print preview of the excel its not displaying complete page. and the generated pdf is also not fitting in same page its displaying some content in next page.

my target is to fit all the excel columns into one page of pdf

can any one help me .


Thanks
2

Hi,


Please see the document/article for your reference:
https://docs.aspose.com/display/cellsnet/Convert+Excel+Workbook+to+PDF

It may help you to accomplish you needs. I think you may try to set an option:
PdfSaveOptions.setOnePagePerSheet(true);

For your information, by default, Aspose.Cells will render/print PDF based on the print previews shown in MS Excel for different worksheets.

Thank you.

3

hi ajmad


i tried this option but no use and i am seeing weird behavior while converting excel to pdf.
my excel file has pivoted sheets and when i try to convert the excel to pdf its giving me improper data with weird look and feel.

i am attaching excel and pdf.

please suggest me idea to solve this issue.


thanks

4
i am using the following code.

Workbook workbook = new Workbook(excelFileName);
//execute workbook formulas before pdf generation
workbook.calculateFormula(true);
// Get the count of the worksheets in the workbook
int sheetCount = workbook.getWorksheets().getCount();
//loop through all wroksheets
for (int i = 1; i < sheetCount; i++) {
Worksheet ws = workbook.getWorksheets().get(i);
for(int k =0;k<ws.getPivotTables().getCount();k++){
System.out.println("in start..1111.. 2222222222222222222222222");
//ws.getPivotTables().get(k).setRefreshDataOnOpeningFile(true);
ws.getPivotTables().get(k).setRefreshDataFlag(true);
ws.getPivotTables().get(k).refreshData();
ws.getPivotTables().get(k).calculateData();
ws.getPivotTables().get(k).setRefreshDataFlag(false);
}
}
//save the workbook as pdf report. Save pdf with same name
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.setOnePagePerSheet(true);
workbook.save(getPdfFileName(excelFileName), pdfSaveOptions);
5

Hi,


Thanks for the template files and sample code.

I think since pivot table cannot be refreshed properly based on some external data ranges (e.g text file) which is not supported. MS Excel also cannot perform “Refresh All” operation for the pivot table. You may confirm this in MS Excel manually using your template file. When I also re-save the file to XLSX file format by Aspose.Cells using your sample code, the file is corrupted as well.

We need to investigate if this is an issue with the product or is it due to external data range for pivot table for which Ms Excel too cannot perform Refresh All operation. I have logged a ticket with an id: “CELLSJAVA-40406”. Once we do the analysis and have any update on it, we will let you know here.

Thank you.
6

thanks for your reply


but to let you know if i try to save .xlsx file as pdf its saving correctly with proper data but the format is not proper.


thanks
7

to answer your question we follow the below steps:


we will have a base template which is pivoted
we will write data into sheet1
data will be pivoted to the main sheet which is visible when the file is opened from sheet 1.


thanks
8

Hi,


Thanks for providing a sample PDF (saved from XLSX) and some steps involved for your scenario.

I have logged it to attach with your issue, we will look into it soon.

By the way, could you also provide the source XLSX file that you used to save your newly attached PDF, it may help a bit too.

Thank you.
9

hi amjad


i used the same XLSX file provided to you previously.
i just opened that file and saved that as pdf.

again i am attaching the file.

thanks
Pavan
10

Hi,


Thanks for the file again.

So, the PDF you posted is actually the file that you Saved it As “PDF” in MS Excel manually. Anyways, we have logged it and it might help a bit in analyzing the issue.

Once we have any update on it, we will let you know here.

Thank you.
11

The issues you have found earlier (filed as CELLSJAVA-40406) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
12

Hi

thanks for the update.

But i got one more issue .

when i try to generate pdf from excel which has more than 1000 rows its returning blank PDF

but if the data is less than 1000

i am attaching the excel file. and code snip is

Workbook workbook = new Workbook(excelFileName);

//execute workbook formulas before pdf generation

//workbook.calculateFormula(true);

// Get the count of the worksheets in the workbook

PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();

pdfSaveOptions.setOnePagePerSheet(true);

workbook.save(getPdfFileName(excelFileName), pdfSaveOptions);

thanks in advance for the help.

13

Hi,


Well, I checked your sheet in your template Excel file, it has more than 1000 records with certain columns. Now come to your line of code:
pdfSaveOptions.setOnePagePerSheet(true);
When you use this line it will try to generate one page and try to gather all the data on single page with all the columns which is not possible. Because if you do this in Ms Excel using PageSetup and other options, e.g you may try to use Fit to Page tall and wide options, when you see the data into print preview of Ms Excel, you will see it cannot read and still you got about 2/3 pages. Please do not use this option as you cannot read or even see the data in the rendered PDF file. We recommend you to kindly use PageSetup options e.g FitToPages tall/wide accordingly before converting to PDF, see the topic for reference:
Setting Page Options


The best way to cope with this, try using and playing upon the Page Setup options, when you feel it suits your needs a bit, then use Aspose.Cells PageSetup APIs to implement the features/options.

Thank you.

14

thanks for the quick reply amjad.



it rocks.
15

hi


i am facing problem with multi header data in excel while converting to pdf. Only the first row is appearing in PDF. i am attaching excel and pdf.

quick reply is highly appreciated.


thanks
16

Hi,

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

We have generated the pdf using the following code with the latest version: Aspose.Cells for .NET (Latest Version) . Please download it and highlight your issue in a screenshot by marking the problematic areas with red circles.

It will help us look into your issue precisely.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Copy+of+_Plan_of_Record_Report.xlsx”;


Workbook workbook = new Workbook(filePath);


PdfSaveOptions opts = new PdfSaveOptions();

opts.OnePagePerSheet = true;


workbook.Save(filePath + “.out.pdf”, opts);


17

hi


i have updated the excel file. the portion which is marked as yellow is not visible in pdf.



thanks
18

Hi,

Thanks for your posting and using Aspose.Cells for Java

Thanks for highlighting the issue. We have now logged your issue in our database. We will look into your issue and fix it. Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40432.

19

Hi,

Please set the flag “save source data with file” to true in pivot table’s options. The picture is attached here.

This issue is fixed in the latest this latest fix: Aspose.Cells for Java (Latest Version)

Let us know your feedback.

Thank you.

20

The issues you have found earlier (filed as CELLSJAVA-40432) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.