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):
  # ???

Slavin

Can you provide the actual code you are currently using to set the column widths? – Hamite

Answer 1

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.

Update from January 2023.

XlsxWriter 3.0.6+ now supports a autofit() worksheet method:

from xlsxwriter.workbook import Workbook

workbook = Workbook('autofit.xlsx')
worksheet = workbook.add_worksheet()

# Write some worksheet data to demonstrate autofitting.
worksheet.write(0, 0, "Foo")
worksheet.write(1, 0, "Food")
worksheet.write(2, 0, "Foody")
worksheet.write(3, 0, "Froody")

worksheet.write(0, 1, 12345)
worksheet.write(1, 1, 12345678)
worksheet.write(2, 1, 12345)

worksheet.write(0, 2, "Some longer text")

worksheet.write(0, 3, "http://ww.google.com")
worksheet.write(1, 3, "https://github.com")

# Autofit the worksheet.
worksheet.autofit()

workbook.close()

Output:

Hornbill

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. – Slavin

@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? – Bowyer

Answer 2

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 

Alba

unfortunately, I don't want to reopen the file – Slavin

Answer 3

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://mcmap.net/q/146019/-adjust-cell-width-in-excel :)

Gilbye

this is solid. any chance you found a platform independent version since you posted this? – Loathly