Automatically autofit row heights for columns where the text wraps when loading an Excel sheet?

Question

I am creating an Excel sheet with the python openpyxl library (which is extremely cool and useful). The sheet contains two columns of some fixed, preset width which contains text. The format of these columns is set to auto-wrap, so if the text is longer than what fits into the width, it will autowrap into several lines and the height of the row should adapt accordingly. The problem is that after creating the file and loading into Excel or Office online, the rows only show a single, truncated line, with a tiny triangle at the right indicating that the text is overflowing. The only way to actually show this properly is by manually selecting the cells and doing "Autofit Row Height". This is extremely annoying and somewhat defying the idea of creating the sheet programmatically. Is there any way to tell Excel to automatically show the wrapped texts with the correct column heights when loading? Is there some other workaround to achieve this that anyone could point me to (some kind of on-load macro I could set or some other way?) Are there other ways to programmatically create a sheet where it is possible to do this directly?

Answer 1

HI Johann

Thank you for contacting Microsoft Open Specifications Support.

The auto-fit logic is something which is implemented by Microsoft Excel, and is not a part of the MS-XLSX File Format Open Specifications. Auto-fit involves measuring the width (or height) of the value in each cell and finding the maximum value.

Example From Stack Overflow might be what you are looking for, https://stackoverflow.com/questions/24023518/using-python-to-autofit-all-columns-of-an-excel-sheet. You just need some modification to make it auto-fit row instead.

Here is a VBA Marco that would do the entire workbook, with following Marco it will be run each time user open the workbook

Private Sub Workbook_Open()

Dim Count1 As Integer
Dim i As Integer

'Set Count1 equal to the number of worksheets in the active workbook.

Count1 = ActiveWorkbook.Worksheets.Count

For i = 1 To Count1

   ActiveWorkbook.Worksheets(i).Cells.EntireColumn.AutoFit
   ActiveWorkbook.Worksheets(i).Cells.EntireRow.AutoFit

Next
 

End Sub

I hope this helped.

HungChun Yu
Microsoft Open Specifications