Excel to PDF Conversion Bug

Aspose.Cells Product Family
1

When Aspose converts and XLSX file to a PDF, it is possible that the last digit of a number gets cut off. The issue seems to occur when text wrap is enabled on the cell and the cell is just big enough to fit the number. When Aspose converts the PDF, the number no longer fits in the cell. If text wrap is not enabled, the cell shows ##### which is acceptable but when text wrap is enabled, the last digit is cut off without any indication that it has happened.

I've attached a PDF showing an example of this behavior.

We're in the process of purchasing a license for Aspose, however this is a significant issue for us. Can you let me know if this is a known issue, if there's a work-around or mitigation, or if it's a new bug?

2

Hi,


Thanks for sharing the sample PDF file.

Could you provide us your underlying Excel file, we will check your issue and help you soon.

Thank you.
3

Hi Amjad -


XLSX attached.

Thanks,

Scott
4

Hi,

Please try our latest version/fix: Aspose.Cells for Java (Latest Version)

I have tested your scenario/case with v16.10.6 by simply converting your template file to PDF using the following sample code, it works fine and as expected. For your information, Aspose.Cells renders the Excel file to PDF as it is shown in Print preview of the sheet in MS Excel.

e.g

Sample code:

Workbook wb = new Workbook(“Aspose_bug.xlsx”);

wb.save(“out1.pdf”);

The output PDF file is attached. If you still find any issue, kindly do paste your sample code and output PDF file with details here, we will check it soon.

Thank you.

5

Hi Amjad,


We’ve been able to reproduce using 16.10.6 as provided - pdf attached. The key to reproducing the bug is to modify the cell widths.

The code is integrated into the application we’re building - code block is
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 9.0px Menlo; color: #a9b7c6; background-color: #2b2b2b} span.s1 {font-kerning: none; color: #808080} span.s2 {font-kerning: none; color: #808080; background-color: #ffffff} span.s3 {font-kerning: none} span.s4 {font-kerning: none; color: #cc7832} span.s5 {font-kerning: none; color: #cc7832; background-color: #ffffff} span.s6 {font-kerning: none; color: #9876aa} span.s7 {font-kerning: none; color: #6897bb}

// Load the document.
 Workbook doc = new Workbook(in);
 doc.calculateFormula(true);
 PdfSaveOptions options=new PdfSaveOptions();
 options.setAllColumnsInOnePagePerSheet(true);
 options.setPdfCompression(PdfCompressionCore.LZW);
 options.setEnableHTTPCompression(true);
 options.setOptimizationType(1);
doc.save (out,options);


6

Hi,


Thanks for providing us sample file.

I still could not find the issue on my pc (Windows 8) using your sample code with your original Excel file. The output (attached) is just the same as before. Could you try to use Worksheet.autoFitColumns() method, it should work for fine on your end (OSX server), see the sample code below:
e.g
Sample code:

Workbook doc = new Workbook(“Aspose_bug.xlsx”);
doc.calculateFormula(true);
doc.getWorksheets().get(0).autoFitColumns();
PdfSaveOptions options=new PdfSaveOptions();
options.setAllColumnsInOnePagePerSheet(true);
options.setPdfCompression(PdfCompressionCore.LZW);
options.setEnableHTTPCompression(true);
options.setOptimizationType(1);
doc.save (“out2.pdf”,options);

Let me know if you still have any issue.

Thank you.
7

Hi,


I thought this issue was resolved by autoFitColumns() but unfortunately it isn’t. It looks to me like auto-fit columns works correctly when the fonts used in the spreadsheet are available on the server.

The spreadsheet I created was created on OSX with the default Calibri font. If I run the conversion on OSX, it works correctly and the cells are widened to fit the content. When I run conversion on a unix box, Calibri is not available and the content does not fit in the columns. Using the Aspose API, I changed the spreadsheet default font to Arial and the conversion went through correctly. We really need the auto-fit columns feature to work regardless of the styling in the spreadsheet because we cannot be sure what styling will be used.

Would you be able to test this scenario?

Thanks
8

Hi James,


First of all, please go through the article on how Aspose.Cells APIs use TrueType fonts for rendering purposes. Please note, Aspose.Cells APIs need the fonts (used in spreadsheet for styling) for correct calculation of cell content’s size, and eventually the dimension of the cell it self. In case the required font is not available, the API will try to substitute the missing font with the available one. In this case, the rendered result will differ from the original document. You can avoid this situation in a number of way as detailed below.

  • Make the fonts available on the machine where conversion has to take place.
  • If you cannot install or place a font on your machine, you can direct the Aspose.Cells APIs to substitute a missing font with best available font. This way, you get to choose the best possible font instead of relying on Aspose.Cells’ font selection. Please check the article on how to configure font substitution.

Please note, AutoFitColumn(s) & AutoFitRow(s) methods rely on text size (in characters) as well as the cell styling, especially on the font and its aspects such as style, size and so on. Unfortunately, it is not possible to calculate correct cell size without the font used to style the contents.

Hope the above information helps.

9

The font substitution appears to work correctly but when a font is substituted, auto-fit is unreliable.

It feels like the font substitution is done after the auto-fit is calculated and hence is based on the wrong data.
I can certainly look at adding more fonts to the server but I would really like a solution regardless of the font that the user uses. I am perfectly happy for the font to be substituted but I can’t have 1000 converted to 100 without any indication that it has happened.

10

Hi James,


Thank you for writing back.

I have performed some tests on Linux (CentOS 6.5) against the latest version of Aspose.Cells for Java 16.10.6, and I am not able to observe the problem with autoFitColumns method with or without setting the font folder containing the Calibri TTF files. Please note, I have called the autoFitColumns (& autoFitRows) method just before rendering the spreadsheet to PDF format. Please find the resultant PDF files in the attached archive.

In order to further investigate the matter, please share the following details as well as the sample spreadsheet that you are currently using for testing along with its resultant PDF. Moreover, please always perform tests using the latest revision of the API, that is; 16.10.6 at the moment (download links shared in previous responses).

  • Operating system version & architecture
  • JDK vendor, version & architecture
  • JVM arguments, if any
  • Executable source code
11

Hi James & Scott,


This is to inform you that I have moved this thread to Aspose.Cells support forum because the inquiry involves only Aspose.Cells APIs, also I can track your responses more efficiently here.
12

I have attached a test class that I have been using. I have been building this on osx using:


p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo} span.s1 {font-variant-ligatures: no-common-ligatures}

java version "1.8.0_91"

Java(TM) SE Runtime Environment (build 1.8.0_91-b14)

Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)


I then sftp it to a t2 medium AWS instance:


p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo} span.s1 {font-variant-ligatures: no-common-ligatures}

Linux ip-10-42-10-55 3.13.0-48-generic #80-Ubuntu SMP Thu Mar 12 11:16:15 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux


The instance is running the following java version:


Java(TM) SE Runtime Environment (build 1.8.0_45-b14)

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo} span.s1 {font-variant-ligatures: no-common-ligatures}

Java HotSpot™ 64-Bit Server VM (build 25.45-b02, mixed mode)


I run test on the aws instance and then sftp the out.pdf file back so I can view it on osx.


If you run the following command you will see that despite setting autoFitColumns, the cells all show hashes.


p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo} span.s1 {font-variant-ligatures: no-common-ligatures}

java -cp aspose-cells-16.10.6.jar:. AsposeTest


The following command will change the default font for the sheet to Arial. This seems to work for my test but I don't know if it is a safe approach that will work for all spreadsheets.


p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo} span.s1 {font-variant-ligatures: no-common-ligatures}

java -DchangeFont=true -cp aspose-cells-16.10.6.jar:. AsposeTest


When I run the test I get the following output:


changeFont = true

default font style was Calibri

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ C4 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ C5 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ D5 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ E5 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ F5 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ G5 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ B6 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C6 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C6 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ D6 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ E6 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ F6 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ G6 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ B7 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C7 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C7 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ D7 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ E7 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ F7 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ G7 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ B8 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C8 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C8 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ D8 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ E8 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ F8 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ G8 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ B9 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C9 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C9 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ D9 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ E9 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ F9 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ G9 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ B10 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C10 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C10 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ D10 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ E10 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ F10 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ G10 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Bold ] has been substituted in Cell [ B11 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C11 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ C11 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ D11 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ E11 ] in Sheet [ Sheet1 ].

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ F11 ] in Sheet [ Sheet1 ].

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo} span.s1 {font-variant-ligatures: no-common-ligatures}

Font substitution: Font [ Calibri; Regular ] has been substituted in Cell [ G11 ] in Sheet [ Sheet1 ].


Thanks,


James

13

Hi James,


Thank you for the fresh sample and code. I am finally able to observe one problem on Ubuntu 15.10 against Oracle’s JDK 1.8.0_111. I have noticed that if the required font is not present on the machine and default font has not been changed, the numeric values in the cells render as string of # character (please check attached resultant PDF files for your reference). That also means that the Worksheet.autoFitColumns method has not correctly adjusted the cell widths. I have raised this issue with the product team under the ticket CELLSJAVA-42058. Please spare us little time to properly analyze the scenario and revert back with updates in this regard.
14

Great, thanks.


Where can I see that ticket or should I just wait for updates on this forum?

15

Hi James,


The aforementioned ticket is attached to this thread and its status can be viewed from the left hand pane of the original post (please check attached snapshot). However, the ticket details are not accessible publicly therefore we will post the updates on this thread as soon as product team share any.
16

Hi,


Is there any update?

Thanks,

James
17
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSJAVA-42058 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

18
Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java (Latest Version) and let us know your feedback.
19

That seems to work now thanks. Is this a supported version that we can run in production?


Thanks,

James
20
Hi,

Thanks for your feedback.

Good to know that it figures out your issue now. Well, you may use the fix/version (v16.11.3) as an official release of the product for production server, it contains all the features, fixes and enhancements full fledged. We are scheduled to release our next official version (v16.12.0) of the product in the third or fourth week of December 2016 (The date is not finalized though). This fix will also be included in the official release of the product (in the Downloads section). Generally we provide fixes (hot fixes) in the forums for the users every week while we publish official release of the product in Downloads module once per month.

Thank you.