Openpyxl autosize column size

Question

Morning, I would like that when cells are added, they adjust to the width of the text, any ideas?

def excel():# Writing on a EXCEL FILE
                filename = (f"{myPath}/{day}.{month}.{year}.xlsx")
                try:
                    wb = load_workbook(filename)
                    ws = wb.worksheets[0]  # select first worksheet
                except FileNotFoundError:
                    headers_row = ['Name','Pers. Nummer','Kürse','Funktion','Dienstbeginn','Dienstende','Schitdauer','Bezahlte Zeit','Kommentar']
                    wb = Workbook()
                    ws = wb.active
                    ws.append(headers_row)              
                wb.save(filename)
                ws.append([userfinder,tagesinfo,emptycell,emptycell,emptycell,emptycell,emptycell,emptycell])
                for cols in ws.iter_cols(  ):
                     if cols[-1].value:
                        cols[-1].fill=(PatternFill(fgColor="D5D5D5", fill_type='solid'))
                        cols[-1].border = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))                
                wb.save(filename)
                wb.close()
                print(f'{userfinder}-{ivunumber} - {tagesinfo} OK')
            excel()

You **have** to adjust the column width manually. – Charlie Clark Feb 16 '22 at 15:56 — Charlie Clark, Feb 16 '22 at 15:56

score -1 · Answer 1 · answered Feb 17 '22 at 01:24

You can use python to resize column width and row height to specific values just by adding lines

ws.column_dimensions['A'].width = 30
ws.row_dimensions[1].height = 20

The above would resize column A to 30 and row 1 height to 20. There is no auto sizing so you have to figure out the values based on the font sixe and text length of whichever cell is largest (or is to be the determining width). If your using the headers and these never change then you should be able to determine this easily however if the text may change then you may need to calc text length and determine a formula based on that and font size.

There are discussions and examples in other questions that should help, for starters look at. Python - Automatically adjust width of an excel file's columns

Openpyxl autosize column size

1 Answers1