Is there some way to autofit rows for wrapped cells automatically?

[Johann Petrak]

I understand that apparently this cannot be done with openpyxl directly, but is there any trick or workaround that could be used to accomplish this?

I am able to load a pre-formattet empty sheet where the cells in one column are defined to wrap long text but those cells are empty initially. When I fill those cells with text that is so long that Excel should wrap them, save the sheet then load it into excel, the text is NOT shown wrapped, instead, part of the text is shown with a small triangle indicating that there is more.

Only if I manually select all those cells, then use Format-Autofit Row Heights are the wrapped cells shown correctly.

I am not really an Excel user and trying to create an automatically created sheet for others. But is there any other way to automatically adjust / autofit the rows so that the full wrapped text for all cells is shown?
For example would it be possible to do this with some kind of macro automatically when the document is loaded? Any other way?

Thanks,

  Johann

----

http://johann-petrak.github.io/

[Charlie Clark]

Having discussed this matter in detail with the OOXML-WG I can confirm that this is a function for the "consuming" application only. For some numerical values Excel will actually do this but to all intents and purposes you must set the column width manually. I normally set the alignment for the cell to wrapText. You can then approximately set the height based a combination of column width and length of text.

From one of my projects with a column width of 80 I use the following code to set the row height:

lines = min(len(note.value.split("\n\n")) - 1, 1)
dim = ws.row_dimensions[text.row]
dim.height = max(len(note.value)/60 + lines, 1.5) * 16

In theory, you can calculate this more accurately but life generally isn't long enough and minor differences in computer setups will break it anyway. With a little trial and error you should be able to come up with something acceptable.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Mobile: +49-178-782-6226