question

JohannPetrak-7739 avatar image
1 Vote"
JohannPetrak-7739 asked CarrTaliaferro-2068 commented

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

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?

office-js-devopenspecs-office
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HungChunYu-3579 avatar image
0 Votes"
HungChunYu-3579 answered CarrTaliaferro-2068 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you!

Sadly Microsoft does not seem to support this at all in the online version of Excel.

0 Votes 0 ·

Back in the Dark Ages, double clicking the bottom row divider line in Excel would autofit the row. Doesn't work anymore....

0 Votes 0 ·