Python - Automatically adjust width of an excel file's columns

Question

Newbie - I have a Python script that adjusts the width of different columns of an excel file, according to the values specified:

import openpyxl
from string import ascii_uppercase

newFile = "D:\Excel Files\abc.xlsx"

wb = openpyxl.load_workbook(filename = newFile)        
worksheet = wb.active

for column in ascii_uppercase:
    if (column=='A'):
        worksheet.column_dimensions[column].width = 30
    elif (column=='B'):
        worksheet.column_dimensions[column].width = 40            
    elif (column=='G'):
        worksheet.column_dimensions[column].width = 45            
    else:
        worksheet.column_dimensions[column].width = 15

wb.save(newFile)

Is there any way through which we can adjust the width of every column to its most optimum value, without explicitly specifying it for different columns (means, without using this "if-elif-elif-......-elif-else" structure)? Thanks!

Use a dictionary of column names and widths. As the file format requires values per column, there is no magic way to do this. — Charlie Clark, Sep 16 '16 at 18:00
Related: [Auto-adjust column widths with xlwt](http://stackoverflow.com/questions/6929115/python-xlwt-accessing-existing-cell-content-auto-adjust-column-width). The issues surrounding column widths are basically the same no matter what package you're using. The linked question has an answer based on widths for Arial 10 (the default font in older .xls files). A similar technique could be used for Calibri 11 (default for .xlsx files). — John Y, Sep 16 '16 at 22:14

score 63 · Accepted Answer · edited Jun 10 '21 at 17:01

for col in worksheet.columns:
     max_length = 0
     column = col[0].column_letter # Get the column name
     for cell in col:
         try: # Necessary to avoid error on empty cells
             if len(str(cell.value)) > max_length:
                 max_length = len(str(cell.value))
         except:
             pass
     adjusted_width = (max_length + 2) * 1.2
     worksheet.column_dimensions[column].width = adjusted_width

This could probably be made neater but it does the job. You will want to play around with the adjusted_width value according to what is good for the font you are using when viewing it. If you use a monotype you can get it exact but its not a one-to-one correlation so you will still need to adjust it a bit.

If you want to get fancy and exact without monotype you could sort letters by width and assign each width a float value which you then add up. This would require a third loop parsing each character in the cell value and summing up the result for each column and probably a dictionary sorting characters by width, perhaps overkill but cool if you do it.

Edit: Actually there seems to be a better way of measuring visual size of text: link personally I would prefer the matplotlib technique.

Hope I could be of help, my very first stackoverflow answer =)

[link](https://stackoverflow.com/questions/13197574/python-openpyxl-column-width-size-adjust?rq=1) this question basically covers your topic as well. — oldsea, Sep 16 '16 at 11:51
I think there's a bug here that prevent proper operation on numerical cell values. In line 8, instead of `max_length = len(cell.value)` I think it should be `max_length = len(str(cell.value))` — Roberto, Sep 28 '20 at 14:37
In what circumstance is the try / except is needed? I don't seem to reach the except block with empty values. — Noel Evans, Jan 04 '23 at 17:00

score 23 · Answer 2 · edited Mar 09 '22 at 18:27

23

Updated version as of openpyxl 3.0.0 (using .columns fails with TypeError: expected <class 'str'>:

for column_cells in ws.columns:
    length = max(len(str(cell.value)) for cell in column_cells)
    ws.column_dimensions[column_cells[0].column_letter].width = length

edited Mar 09 '22 at 18:27

Nam G VU

33,193
69
233
372

answered Sep 28 '19 at 21:55

Manuel G

1,523
1
21
34

3

adding the filter for unmerged cells as suggested by @virako `for column_cells in ws.columns:` `unmerged_cells = list(filter(lambda cell_to_check: cell_to_check.coordinate not in ws.merged_cells, column_cells))` `length = max(len(str(cell.value)) for cell in unmerged_cells)` `ws.column_dimensions[unmerged_cells[0].column_letter].width = length * 1.2` – Arvind Sridharan Nov 13 '19 at 07:41
3

@DušanAtanacković good to hear. I have put this as a github gist for easier readability - https://gist.github.com/arvsr1988/4468e7f9bb9c5f3aa514eea6a14804b6 – Arvind Sridharan Jul 21 '20 at 10:27
5

This works but I had to replace as_text() with str() – Greg G. Apr 20 '21 at 23:23
1

Not working for me – Nam G VU Mar 09 '22 at 18:27

score 9 · Answer 3 · answered Jun 30 '21 at 13:52

9

With the latest openpyxl you can use this:

from openpyxl.utils import get_column_letter
for idx, col in enumerate(worksheet.columns, 1):
    worksheet.column_dimensions[get_column_letter(idx)].auto_size = True

answered Jun 30 '21 at 13:52

Corvax

782
8
13

2

This doesn't seem to do anything at all; column is the same as before; see also first comment to [this question](https://stackoverflow.com/q/60248319) (`bestFit` seems to be an alias for `auto_size`). – ssc Feb 05 '22 at 14:10
3

Not working for me – Nam G VU Mar 09 '22 at 18:12
1

for me it hides the column(s) instead auto-size them. – Cristian F. Apr 24 '22 at 15:18
@CristianF. I faced the same for some versions. It's weird. – Corvax Jul 28 '22 at 15:47
I believe this instruction works perfectly for LibreOffice, but not for Excel. When the latter opens the file it doesn't understand it and hides column. – Corvax Jul 28 '22 at 15:58
For me it looked better when I also set the `cell.alignment.warp_text=True`. – ginger Jul 04 '23 at 13:30

score 5 · Answer 4 · edited Dec 24 '20 at 15:20

Based on the comment above and add this post openpyxl - adjust column width size. I succeeded, but the answer should be:

from openpyxl.utils import get_column_letter

for col in ws.columns:
    max_length = 0
    column = get_column_letter(col[0].column)  # Get the column name
    # Since Openpyxl 2.6, the column name is  ".column_letter" as .column became the column number (1-based)
    for cell in col:
        try:  # Necessary to avoid error on empty cells
            if len(str(cell.value)) > max_length:
                max_length = len(cell.value)
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2
    ws.column_dimensions[column].width = adjusted_width

i'm using openpyxl = "^3.0.5"

score 4 · Answer 5 · edited May 23 '17 at 12:10

I have a problem with merged_cells and autosize not work correctly, if you have the same problem, you can solve adding the next lines inside code of oldsea

for col in worksheet.columns:
    max_length = 0
    column = col[0].column # Get the column name
    for cell in col:
        if cell.coordinate in worksheet.merged_cells: # not check merge_cells
            continue
        try: # Necessary to avoid error on empty cells
            if len(str(cell.value)) > max_length:
                max_length = len(cell.value)
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2
    worksheet.column_dimensions[column].width = adjusted_width

score 2 · Answer 6 · answered Aug 11 '22 at 10:06

Just insert the below line of code in your file. That's it (install the mentioned libraries if you don't have)

# Imorting the necessary modules
try:
        from openpyxl.cell import get_column_letter
except ImportError:
        from openpyxl.utils import get_column_letter
        from openpyxl.utils import column_index_from_string
from openpyxl import load_workbook
import openpyxl
from openpyxl import Workbook



for column_cells in sheet.columns:
    new_column_length = max(len(str(cell.value)) for cell in column_cells)
    new_column_letter = (get_column_letter(column_cells[0].column))
    if new_column_length > 0:
        sheet.column_dimensions[new_column_letter].width = new_column_length*1.23

score 1 · Answer 7 · answered Oct 10 '22 at 10:04

for column_cells in sheet.columns:
    new_column_length = max(len(str(cell.value)) for cell in column_cells)
    new_column_letter = (get_column_letter(column_cells[0].column))
    if new_column_length > 0:
        sheet.column_dimensions[new_column_letter].width = new_column_length*1.23

score 0 · Answer 8 · answered Mar 29 '20 at 20:08

0

def auto_format_cell_width(ws):
    for letter in range(1,ws.max_column):
        maximum_value = 0
        for cell in ws[get_column_letter(letter)]:
            val_to_check = len(str(cell.value))
            if val_to_check > maximum_value:
               maximum_value = val_to_check
        ws.column_dimensions[get_column_letter(letter)].width = maximum_value + 1

answered Mar 29 '20 at 20:08

freddiemacintosh

21

3

Please do not post only code as an answer, but also include an explanation of what your code does and how it solves the problem of the question. Answers with an explanation are usually of higher quality and are more likely to attract upvotes. – Arco Bast Mar 29 '20 at 21:29
Is this not quite similar to existing solutions? – AMC Mar 30 '20 at 00:21

score 0 · Answer 9 · answered Feb 07 '23 at 03:45

0

sheet.column_dimensions[new_column_letter].width = new_column_length*1.23

The above line of code fits the adjust the width of the column to best fit

answered Feb 07 '23 at 03:45

You need to explain little more – Mounesh Feb 07 '23 at 03:45
Okay Sir. I will – Feb 07 '23 at 03:46

score 0 · Answer 10 · edited Mar 17 '23 at 09:36

0

Try this, it works for me:

from openpyxl.utils import get_column_letter

for columns in worksheet.columns:
    col = get_column_letter(columns[0].column)
    worksheet.column_dimensions[col].auto_size = True

edited Mar 17 '23 at 09:36

S.B

13,077
10
22
49

answered Mar 12 '23 at 09:10

samad

43
2
6

Jay Jara · Answer 11 · 2023-03-18T18:18:36.357

pywin32 is a better package to work with Excel on Python in my opinion because it mirrors Excel VBA. The Range.AutoFit method takes care of this issue. Example below:

import win32com.client as win32

xlApp = win32.Dispatch('Excel.Application')
wb = xlApp.Workbooks.Open(***file path to excel file goes here***)

ws = wb.Worksheets[***name of worksheet trying adjust column width***]
ws.Columns.AutoFit()

Note: Worksheet.Columns property represents a Range object. Autofit is a method that belongs to the Range object.

Python - Automatically adjust width of an excel file's columns

11 Answers11

Just insert the below line of code in your file. That's it (install the mentioned libraries if you don't have)

Linked

Related