Hello, we are trying to convert an excel sheet to PDF. We are using aspose cells 23.8. We have two questions:
We are seeing issues converting wide excel sheets to pdf and retaining their structure without seeing data get cut off. Is there a way we can correctly orient the page based on the size of the excel structure?
We are having problems working with macro enabled excel sheets. Is there a particular method we need to take to convert these to PDF?
Sure. Please see the attached. Notice, the excel sheet has thirty columns but the conversion only displays 9. As for macro enabled sheets, we can worry about that later - data being truncated is the priority for now. Thank you for your help.EXCEL.zip (19.0 KB)
Yes, it shows 9 columns on the first page but other columns are rendered into other pages, it renders 4 pages in total. If you could open your file into MS Excel manually and take the print preview of the sheet or save to PDF, you will also get those 4 pages (splitting data into multiple pages) similar to original Excel sheet.
To render all the columns into single page, you got to specify the relevant settings in code. For example, you may try to set AllColumnsInOnePagePerSheet option to true to accomplish your task. See the following sample code for your reference.
e.g. Sample code:
// Create and initialize an instance of Workbook
Workbook book = new Workbook("f:\\files\\wideExcel.xlsx");
// Create and initialize an instance of PdfSaveOptions
PdfSaveOptions saveOptions = new PdfSaveOptions();
// Set AllColumnsInOnePagePerSheet to true
saveOptions.setAllColumnsInOnePagePerSheet(true);
// Save Workbook to PDF fromat by passing the object of PdfSaveOptions
book.save("f:\\files\\output.pdf", saveOptions);
Apologies, I did not really articulate the issue correctly. Please see the updated zip. For wide data, such as wide images, we are seeing data get cut off in the same manner as the columns. While the columns are fine, pictures should be kept intact, and either resized or re oriented to fit the page. EXCEL.zip (723.9 KB)
After an initial test with your newly attached file, we apparently noticed the issue you have mentioned. The picture/image in your new (updated) Excel file is not rendered in the output PDF when using the suggested code segment. Let us evaluate your issue in details. We will get back to you soon.
I am able to reproduce the issue as you mentioned by using the following sample code with your newly attached file, I found image is missing in Excel to PDF conversion, there is something wrong with the image.
e.g. Sample code:
Workbook book = new Workbook("f:\\files\\wideExcel.xlsx");
// Create and initialize an instance of PdfSaveOptions
PdfSaveOptions saveOptions = new PdfSaveOptions();
// Set whole sheet to one page
// Save Workbook to PDF format by passing the object of PdfSaveOptions
book.save("f:\\files\\output.pdf", saveOptions);
We need to evaluate your issue in details. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-45646
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
On some machines, we could not find this issue (logged above as “CELLSJAVA-45646”). I used JDK1.8.0_271 on the machine where I found the issue, what is your JDK version where you find image missing (into output PDF) issue?
Hello, thanks for getting back to us. The JDK we are using is 17.0.8. As for the requirements, the desired output is this:
We don’t mind if the columns of the excel sheet with data in them are split to different pages, but any images or charts must be kept whole, ideally on their own page. If the code you provided will do this, please let me know.
This is not an issue with Aspose.Cells APIs. Since both Excel files you attached have same names and I was actually using your previous file in which image was not there, so I found the output PDF with no image at all. Now I tested your scenario/case again using your new Excel file (also attached in the zipped archive) and the image does rendered fine. So, there is nothing Aspose.Cells could do for your issue as there is no issue at all on our end. We tested and tried several JDKs, such as 1.6,1.8,11, 17, etc. and found the output PDFs are fine tuned. Here is the sample code segments that I am using. Also, find attached output PDFs for both examples for your reference.
1).
// Create and initialize an instance of Workbook
Workbook book = new Workbook("f:\\files\\wideExcel.xlsx");
// Create and initialize an instance of PdfSaveOptions
PdfSaveOptions saveOptions = new PdfSaveOptions();
// Set one page for the whole sheet
saveOptions.setOnePagePerSheet(true);
// Save Workbook to PDF format by passing the object of PdfSaveOptions
book.save("f:\\files\\out1.pdf", saveOptions);
Please find attached the input Excel file and output PDF using above code in the zipped archive for your reference. files1.zip (553.5 KB)
2).
// Create and initialize an instance of Workbook
Workbook book = new Workbook("f:\\files\\wideExcel.xlsx");
// Create and initialize an instance of PdfSaveOptions
PdfSaveOptions saveOptions = new PdfSaveOptions();
// Set all columns in one page
saveOptions.setAllColumnsInOnePagePerSheet(true);
// Save Workbook to PDF fromat by passing the object of PdfSaveOptions
book.save("f:\\files\\out2.pdf", saveOptions);
To render image/chart as a whole you got to use any of my code segments I shared in previous reply. Please note, if you will simply render to PDF without specifying the PdfSaveOptions, the image would be splitted, this is same as when you take the print preview of the sheet or render to PDF in MS Excel manually.
@drymers
If the columns of the excel sheet with data in them are split to different pages and you still want keep images or charts be whole, it’s the only way to modify the size of the image to fit the width and height of the Paper.