9

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):
  # ???
parsecer
  • 4,758
  • 13
  • 71
  • 140
Anis Khadhri
  • 177
  • 1
  • 2
  • 12

3 Answers3

11

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:

enter image description here

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • 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
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
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
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 :)

Seven
  • 177
  • 1
  • 5