Generating xlsx with auto size height cells using Apache POI

English
,
#1

Hi!
I’m using Apache POI to generate .xlsx documents.
I need to set auto size height for table cells and I’m using next methods:

org.apache.poi.ss.usermodel.CellStyle#setWrapText(true);
org.apache.poi.ss.usermodel.Row#setHeight((short) -1);

The auto size for cell height works fine in MS Excel. But it doesn’t work, if the document is opened in LibreOffice Calc.

Used versions:
Apache POI - 5.2.3 and 4.1.2
LibreOffice Calc - 7.0.3.1 and 7.4.3.2

I’ve noticed that resaving document using MS Excel or LibreOffice Calc in .xlsx or .ods format respectively helps. After that LibreOffice Calc sets auto size height for cells.

Question:
Is it possible to generate an .xlsx document with auto size height using Apache POI directly, without resaving the document, to work with in LibreOffice Calc?

JavaBooks.xlsx (3.8 KB) - document, generated with Apache POI. Auto size works in MS Excel, but not in LibreOffice Calc.

#2

tdf#34717, recently closed; not sure it was a correct close.

#3

They are refer to bug in xlsx-file, but it is not. As I inderstand, it is how Libre opens files - the heights aren’t recalculated.

#4

Needs more checking. The case on bugzilla was set to not-our-bug, because the rows are not flagged to have content, so they were not recalculated, but set to default width.
.
In this case there also seem to be a difference between the ApachePOI files and files generated by Excel:

So one question is, if there is an option to save “better” in your ApachePOI, another if LibreOffice should change behaviour here…

And “always recalculate” is not the best idea for everybody. I remember long loading of some of my files, before I re-set them to use fixed height…

#5

There are also open similar bug#32950.
The Apache POI can save only xlsx-files, not ods. I don’t now “better” ways to save it… To set auto-height I found there is only one option - set height to -1.

And “always recalculate” is not the best idea for everybody… before I re-set them to use fixed height

You mean, that it could be set in Calc properties? Or only for one file as Ctrl+A → Auto-height?

As I undrestand, the re-saving force it to set exact height, not auto-size. And after that it opens fine in both.

#6

Our usual approach to this kind of issues is: when there’s a difference how MS treats their file types (including generated ones), and how LibreOffice treats them, we assume that there is a bug in LibreOffice. Only very rarely we can discover (when doing a deep analysis), that the generators exploited a bug in MS Office; and then we still have a dilemma: should we be bug-to-bug compatible with MS Office in this aspect, or not. And when there is a reason to think that this bug is still an important widely-exploited thing, we often choose to follow MS in this…

The closing of the bug was done not by a developer; I am not convinced it was a correct thing to do.

#7

No, I know no solution to your problem. I could only suggest work-arounds. As you are obviously generating .xlsx-files you could use the “convert-to” -capabilities of LibreOffice to create a .ods from this, if working with .ods is a solution.
.
Thinking of it - even convert-to from xlsx to xlsx should be possible… But I never tried.

PS:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.