How to set automatically the width of a column in xlsxwriter

Question

I'm getting lost of using worksheet.set_column all the time. Is there any possibility of setting the width of all columns automatically?

What would be the Python function that simulates it? (using only xlsxwriter library):

def autofit(filename, worksheet_name):
  # ???

Can you provide the actual code you are currently using to set the column widths? — alecxe, Mar 01 '16 at 16:46

score 9 · Accepted Answer · edited Jun 20 '20 at 09:12

9

Is there any possibility of setting the width of all columns automatically?

Unfortunately, not.

From the XlsxWriter FAQ:

Q. Is there an "AutoFit" option for columns?

Unfortunately, there is no way to specify "AutoFit" for a column in the Excel file format. This feature is only available at runtime from within Excel. It is possible to simulate "AutoFit" in your application by tracking the maximum width of the data in the column as your write it and then adjusting the column width at the end.

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 01 '16 at 16:47

jmcnamara

33,735
6
80
93

1

actually, i am storing the max length of each column and adjusting them after I insert all the data, but the problem if u adjust with the length of data some widths will be too large. – Anis Khadhri Mar 01 '16 at 17:01
1

@AnisKhadhri: I do not understand what you mean my "some widths will be too large" -- are you adjusting all the columns to the same size? – Ethan Furman Jul 20 '21 at 21:20

score 2 · Answer 2 · answered Mar 01 '16 at 16:39

I only know of a way to do this with COM.

import contextlib, os, win32com.client

@contextlib.contextmanager
def load_xl_file(xlfilepath):
    ''' Open an existing Excel file using a context manager 
        `xlfilepath`: path to an existing Excel file '''
    xl = win32com.client.DispatchEx("Excel.Application")
    wb = xl.Workbooks.Open(xlfilepath)
    try:
        yield wb
    finally:
        wb.Close(SaveChanges=True)
        xl.Quit()
        xl = None # this actually ends the process 

def xlautofit(xlfilepath,skip_first_col=False):
    ''' relies on win32com.client to autofit columns on data sheets 

        remember that this is using COM so sheet numbers start at 1 (not 0), 
        so to avoid requiring the caller to remember this, we increment 

        returns full path (including dir) to file '''
    if os.path.splitext(xlfilepath)[1] not in ('.xls','.xlsx'):
        raise 
        return -1

    autofitbegcol = 1
    if skip_first_col:
        autofitbegcol += 1

    # Autofit every sheet 
    with load_xl_file(xlfilepath) as wb:
        for ws in wb.Sheets:
            autofitendcol = ws.UsedRange.Columns.Count
            ws.Range(ws.Cells(1, autofitbegcol), 
                     ws.Cells(1, autofitendcol)).EntireColumn.AutoFit()
    return xlfilepath

unfortunately, I don't want to reopen the file – Anis Khadhri Mar 01 '16 at 17:02 — Anis Khadhri, Mar 01 '16 at 17:02

score 1 · Answer 3 · answered Jun 16 '18 at 00:12

1

If you just want column autofit, maybe this will help:

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(r'file.xlsx')
ws = wb.Worksheets("Sheet1")
ws.Columns.AutoFit()
wb.Save()
excel.Application.Quit()

More detail pls check https://stackoverflow.com/a/33665967/1383521 :)

answered Jun 16 '18 at 00:12

Seven

147
1
5

this is solid. any chance you found a platform independent version since you posted this? – Alvin Wanda Dec 04 '21 at 16:55

How to set automatically the width of a column in xlsxwriter

3 Answers3