Bug 64360 - autoSizeColumn Incorrect on Ubuntu (With Correct Fonts Installed)
Summary: autoSizeColumn Incorrect on Ubuntu (With Correct Fonts Installed)
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.1.2-FINAL
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2020-04-16 22:25 UTC by schmoboy
Modified: 2020-04-19 06:46 UTC (History)
0 users

attachment contains screenshot and code and fonts list (82.59 KB, application/x-zip-compressed)
2020-04-16 22:25 UTC, schmoboy
Image with same results under JDK 14 on Ubuntu (45.37 KB, image/png)
2020-04-17 14:44 UTC, schmoboy
Comparison of Windows 10 to Ubuntu JDK 14 (72.80 KB, image/png)
2020-04-17 16:11 UTC, schmoboy

Note You need to log in before you can comment on or make changes to this bug.
Description schmoboy 2020-04-16 22:25:29 UTC
Created attachment 37177 [details]
attachment contains screenshot and code and fonts list

The same code correctly autosizes columns on Windows 10, but makes the columns a bit small when on Ubuntu.  The longer the text the more the difference.  All required fonts are available on the Ubuntu system as you can see by the attached screenshot properly rendering the fonts and the included ubuntufonts.txt fc-list output.

The attached zip file contains the screenshot comparison of the two xlsx files, a text file containing the output of fc-list on the Ubuntu system, and the source code used to produce the test which is a slightly modified version of the POI WorkingWithFonts.java example.

POI 4.1.2 and Java 1.8 latest version on both Windows and Ubuntu systems.
Comment 1 Axel Howind 2020-04-17 05:32:29 UTC
Font dimensions are calculated using AWT. If they are not correct, I think the problem rather lies within the JDK. Could you try if the problem also persists with a recent Java version (i.e. JDK 14)?
Comment 2 schmoboy 2020-04-17 14:44:36 UTC
Created attachment 37181 [details]
Image with same results under JDK 14 on Ubuntu
Comment 3 Axel Howind 2020-04-17 15:58:32 UTC
To me it looks like Arial and Times New Roman look correct now. The yellow symbols also look a little bit better. A difference of a single pixel due to rounding issues in the font rasteriser might not be possible to fix within POI as that happens inside JDK.

I have tried to find some information on this. From what I found, in JDK 8, freetype was used - on windows systems it was bundled with the JDK and on Linux the installed version was used. That could lead to different results due to differing versions of the library being used.

Starting with JDK 9, a bundled version should be used on all platforms, eliminating most differences. So it might be that the windows JDK 14 version renders the yellow symbols the same as it does on Linux. Could you check that? The new screenshot just shows the Ubuntu version.

If the yellow symbols show the same result on windows, I'd say that's another (minor?) issue.

The Courier definitely looks as if the wrong font was used on Ubuntu. And you actually have different versions of that font on your system (note the file names differ "Courier_New.ttf" vs "your.ttf") that both define the "Normal" or "Regular" style:

line 28: "Courier New.ttf"
/usr/share/fonts/truetype/msttcorefonts/Courier_New.ttf: Courier New:style=Regular,Normal,obyčejné,Standard,Κανονικά,Normaali,Normál,Normale,Standaard,Normalny,Обычный,Normálne,Navadno,thường,Arrunta

line 69 "cour.ttf":
/usr/share/fonts/truetype/msttcorefonts/cour.ttf: Courier New:style=Regular,Normal,obyčejné,Standard,Κανονικά,Normaali,Normál,Normale,Standaard,Normalny,Обычный,Normálne,Navadno,thường,Arrunta

I don't know which of those is the correct one, but I think this is caused by Java choosing the wrong one. Please try out what result you get when you remove either of those.
Comment 4 schmoboy 2020-04-17 16:11:49 UTC
Created attachment 37182 [details]
Comparison of Windows 10 to Ubuntu JDK 14

cour.ttf is a symbolic link to Courier_New.ttf in /usr/share/fonts/truetype/msttcorefonts so they are the same font.  I believe the slight diff you are seeing in the two screenshots is just the difference in the resulting compression/resolution of the screenshot.  I have attached a new image that shows the two workbooks together and you can see the font is the same on both.
Comment 5 schmoboy 2020-04-17 16:31:15 UTC
Interesting note that you pointed out is that the variable fonts (Arial, Times) now seem to be correct with JDK 14, but the mono font (Courier) seems to still be incorrect.  Not that concerned with the symbol fonts as I just included them to make sure the same fonts were being used on both systems.

One curious note is that SheetUtils.getCellWidth grabs the style from the cell to create the Rectangle2D bounds.  But SheetUtils.getDefaultCharWidth uses the first font in the workbook?  By default that is Calibri?  Does that cause an issue?
Comment 6 Axel Howind 2020-04-17 17:55:21 UTC
I think then the same error would also show up on windows. I think we should create a simple swing application that displays the text and the bounding box around it for the "Courier New" font and compare results between ubuntu and windows. I have the feeling that different fonts are used on both systems.

I myself only have Mac and Windows, so I cannot test on Ubuntu.
Comment 7 schmoboy 2020-04-17 18:04:31 UTC
I only have headless Ubuntu server installs.  I might be able to whip up a docker container.  Been a while since I've done any swing stuff.
Comment 8 schmoboy 2020-04-17 18:13:30 UTC
Also, even if it IS a different font, it should autosize correctly regardless?
Comment 9 schmoboy 2020-04-17 19:17:11 UTC
(In reply to schmoboy from comment #8)
> Also, even if it IS a different font, it should autosize correctly
> regardless?

Answer is no.  If the Windows Courier font is wider than the Ubuntu Courier font, when you open the xlsx on Windows then it will not be sized correctly.  Obviously. ;)
Comment 10 schmoboy 2020-04-18 23:02:54 UTC
I installed the specific Courier ttf from Windows on my test Ubuntu container and the column sized correctly.

I think we can close this as needing to use a JRE > 8. I know that JRE 8 is listed as the minimum supported, but there may need to be a footnote to that for this issue.

Axel, thank for talking me through this and helping me get to the point of getting it to work with a more recent JRE.
Comment 11 Axel Howind 2020-04-19 06:46:45 UTC
Closing this as it the result of a misconfiguration (see previous comment). Minor differences in Java 8 are the result of pre-JDK 9 implementation differences that can be solved by using a more recent JDK.