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?
For 1) and 2), please zip and attach your template Excel files and output PDF files. We will evaluate your issue(s) and assist you accordingly soon.
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);
Moreover, see the document for your reference.
https://docs.aspose.com/cells/java/fit-all-worksheet-columns-on-single-pdf-page/
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)
Thanks for the updated Excel file.
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.
When you open your source file in Excel, printview it, you will find that the wide image is split into three pages. Aspose.Cells does the same.
Also, you can use the following code to make all the columns into one page. The wide image will be in one page.
If the result is not the one you required, please share us your excpected result and some screenshots with highlights that explain your requirement.
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);
output.pdf (14.3 KB)
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.
Sorry for the confusion.
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);
out2.pdf (389.6 KB)
Let us know if you still find any issue or have any confusion.
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.