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
– Charlie Clark, Commented Sep 16, 2016 at 18:00
Related: Auto-adjust column widths with xlwt. 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
– John Y, Commented Sep 16, 2016 at 22:14

Naofumi · Accepted Answer · 2021-06-10 17:01:46Z

71

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 =)

edited Jun 10, 2021 at 17:01

Naofumi

3404 silver badges10 bronze badges

answered Sep 16, 2016 at 11:43

oldsea

1,02111 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

oldsea Over a year ago

link this question basically covers your topic as well.

Roberto Over a year ago

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))

Noel Evans Over a year ago

In what circumstance is the try / except is needed? I don't seem to reach the except block with empty values.

Eystein Thanisch Over a year ago

At risk of being accused of code golfing, the if clause at lines 6-7 could be replaced with max_length = max(len(str(cell.value)), max_length).

Nam G VU · Accepted Answer · 2022-03-09 18:27:09Z

25

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 9, 2022 at 18:27

Nam G VU

35.8k79 gold badges248 silver badges398 bronze badges

answered Sep 28, 2019 at 21:55

Manuel G

1,2891 gold badge22 silver badges36 bronze badges

4 Comments

Arvind Sridharan Over a year ago

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 Over a year ago

@DušanAtanacković good to hear. I have put this as a github gist for easier readability - gist.github.com/arvsr1988/4468e7f9bb9c5f3aa514eea6a14804b6

Greg G. Over a year ago

This works but I had to replace as_text() with str()

Nam G VU Over a year ago

Not working for me

Corvax · Accepted Answer · 2021-06-30 13:52:14Z

8

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, 2021 at 13:52

Corvax

8109 silver badges13 bronze badges

6 Comments

ssc Over a year ago

This doesn't seem to do anything at all; column is the same as before; see also first comment to this question (bestFit seems to be an alias for auto_size).

Nam G VU Over a year ago

Not working for me

Cristian F. Over a year ago

for me it hides the column(s) instead auto-size them.

Corvax Over a year ago

@CristianF. I faced the same for some versions. It's weird.

Corvax Over a year ago

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.

|

HK boy · Accepted Answer · 2020-12-24 15:20:54Z

6

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"

edited Dec 24, 2020 at 15:20

HK boy

1,40411 gold badges21 silver badges27 bronze badges

answered Nov 20, 2020 at 8:59

Phùng Hoàng

611 silver badge1 bronze badge

Comments

Community · Accepted Answer · 2017-05-23 12:10:08Z

4

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

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Apr 24, 2017 at 23:23

Virako

67010 silver badges18 bronze badges

Comments

Mounesh · Accepted Answer · 2022-08-11 10:06:18Z

2

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

answered Aug 11, 2022 at 10:06

Mounesh

8428 silver badges21 bronze badges

Comments

Meghana Naik · Accepted Answer · 2022-10-10 10:04:56Z

1

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

answered Oct 10, 2022 at 10:04

Meghana Naik

112 bronze badges

Comments

freddiemacintosh · Accepted Answer · 2020-03-29 20:08:58Z

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, 2020 at 20:08

freddiemacintosh

21

2 Comments

Arco Bast Over a year ago

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.

AMC Over a year ago

Is this not quite similar to existing solutions?

user20662213 · Accepted Answer · 2023-02-07 03:45:06Z

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 7, 2023 at 3:45

user20662213

2 Comments

Mounesh Over a year ago

You need to explain little more

user20662213 Over a year ago

Okay Sir. I will

S.B · Accepted Answer · 2023-03-17 09:36:59Z

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, 2023 at 9:36

S.B

17k12 gold badges38 silver badges73 bronze badges

answered Mar 12, 2023 at 9:10

samad

572 silver badges7 bronze badges

Comments

SamWest · Accepted Answer · 2024-10-13 23:48:38Z

If anyone also needs to apply autosizing to columns/cells with excel number formatting already applied, this seems to work well.

   def autosize_column(self, sheet: Worksheet, col: tuple[Cell], width_multiplier: float = 1.0) -> None:
    """Autosize a column in an openpyxl worksheet to fit its contents
    :param sheet: the openpyxl worksheet
    :param col: the column to autosize
    :param width_multiplier: a multiplier to adjust the width of the column.  Tweak manually to account for different fonts, font sizes etc.
    """
    max_length = 0
    column = get_column_letter(col[0].column)
    # sheet.column_dimensions[column].auto_size = True
    for cell in col:
        val = cell.value or ""
        if cell.number_format and val:
            # need to format the number as excel will to get display width
            decimal_places = cell.number_format.count("0")
            if decimal_places > 0:
                val = f"{val:.{decimal_places}f}"
        cell_chars = len(str(val)) if val else 0
        max_length = max(max_length, cell_chars)
    adjusted_width = max_length * width_multiplier
    sheet.column_dimensions[column].width = adjusted_width

ChrisPlat · Accepted Answer · 2025-01-09 12:50:56Z

Most solutions simply count the number of characters per string in a cell and multiply by a scaling factor. This approach fails if you have either:

multiple lines per cell
different font sizes within the same column

The following solution takes account for this by breaking up multi-line cells (seperated by "\n") and considering the cell's font size using cell.font.sz:

SCALING_FACTOR = 0.113

for column_cells in ws.columns: #loop through columns in ws

    column_max_lenght = 0 

    for cell in column_cells # loop through cells in column

        cell_values = str(cell.value).split('\n')   # list of "\n" seperated cell value strings := oneline strings
        cell_oneline_lens = [len(oneline_string) for oneline_string in cell_values] # list of lengths of each oneline string
        cell_oneline_lens_font_adjusted = [oneline_lenght * (cell.font.sz * SCALING_FACTOR) for oneline_lenght in cell_oneline_lens] # adjust lenght of oneline string for font size of cell
 
        if (max(cell_oneline_lens_font_adjusted)) > column_max_lenght:
            column_max_lenght = max(cell_oneline_lens_font_adjusted)

    current_column_letter = (get_column_letter(column_cells[0].column))         

    if column_max_lenght > 0:
        ws.column_dimensions[current_column_letter].width = column_max_lenght

Collectives™ on Stack Overflow

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

12 Answers 12

4 Comments

4 Comments

6 Comments

Comments

Comments

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

Comments

Comments

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

4 Comments

4 Comments

6 Comments

Comments

Comments

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

Comments

Comments

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related