openpyxl - adjust column width size

Question

I have following script which is converting a CSV file to an XLSX file, but my column size is very narrow. Each time I have to drag them with mouse to read data. Does anybody know how to set column width in openpyxl?

Here is the code I am using.

#!/usr/bin/python2.6
import csv
from openpyxl import Workbook
from openpyxl.cell import get_column_letter

f = open('users_info_cvs.txt', "rU")

csv.register_dialect('colons', delimiter=':')

reader = csv.reader(f, dialect='colons')

wb = Workbook()
dest_filename = r"account_info.xlsx"

ws = wb.worksheets[0]
ws.title = "Users Account Information"

for row_index, row in enumerate(reader):
    for column_index, cell in enumerate(row):
        column_letter = get_column_letter((column_index + 1))
        ws.cell('%s%s'%(column_letter, (row_index + 1))).value = cell

wb.save(filename = dest_filename)

Answer 1

You could estimate (or use a mono width font) to achieve this. Let's assume data is a nested array like

[['a1','a2'],['b1','b2']]

We can get the max characters in each column. Then set the width to that. Width is exactly the width of a monospace font (if not changing other styles at least). Even if you use a variable width font it is a decent estimation. This will not work with formulas.

from openpyxl.utils import get_column_letter

column_widths = []
for row in data:
    for i, cell in enumerate(row):
        if len(column_widths) > i:
            if len(cell) > column_widths[i]:
                column_widths[i] = len(cell)
        else:
            column_widths += [len(cell)]
    
for i, column_width in enumerate(column_widths,1):  # ,1 to start at 1
    worksheet.column_dimensions[get_column_letter(i)].width = column_width

A bit of a hack but your reports will be more readable.

Answer 2

My variation of Bufke's answer. Avoids a bit of branching with the array and ignores empty cells / columns.

Now fixed for non-string cell values.

ws = your current worksheet
dims = {}
for row in ws.rows:
    for cell in row:
        if cell.value:
            dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value))))    
for col, value in dims.items():
    ws.column_dimensions[col].width = value

As of openpyxl version 3.0.3 you need to use

 dims[cell.column_letter] = max((dims.get(cell.column_letter, 0), len(str(cell.value))))

as the openpyxl library will raise a TypeError if you pass column_dimensions a number instead of a column letter, everything else can stay the same.

Answer 3

Even more pythonic way to set the width of all columns that works at least in openpyxl version 2.4.0:

for column_cells in worksheet.columns:
    length = max(len(as_text(cell.value)) for cell in column_cells)
    worksheet.column_dimensions[column_cells[0].column].width = length

The as_text function should be something that converts the value to a proper length string, like for Python 3:

def as_text(value):
    if value is None:
        return ""
    return str(value)

Answer 4

With openpyxl 3.0.3 the best way to modify the columns is with the DimensionHolder object, which is a dictionary that maps each column to a ColumnDimension object. ColumnDimension can get parameters as bestFit, auto_size (which is an alias of bestFit) and width. Personally, auto_size doesn't work as expected and I had to use width and figured out that the best width for the column is len(cell_value) * 1.23.

To get the value of each cell it's necessary to iterate over each one, but I personally didn't use it because in my project I just had to write worksheets, so I got the longest string in each column directly on my data.

The example below just shows how to modify the column dimensions:

import openpyxl
from openpyxl.worksheet.dimensions import ColumnDimension, DimensionHolder
from openpyxl.utils import get_column_letter

wb = openpyxl.load_workbook("Example.xslx")
ws = wb["Sheet1"]

dim_holder = DimensionHolder(worksheet=ws)

for col in range(ws.min_column, ws.max_column + 1):
    dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=20)

ws.column_dimensions = dim_holder

Answer 5

I have a problem with merged_cells and autosize not work correctly, if you have the same problem, you can solve with the next code:

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

Answer 6

A slight improvement of the above accepted answer, that I think is more pythonic (asking for forgiveness is better than asking for permission)

column_widths = []
for row in workSheet.iter_rows():
    for i, cell in enumerate(row):
        try:
            column_widths[i] = max(column_widths[i], len(str(cell.value)))
        except IndexError:
            column_widths.append(len(str(cell.value)))

for i, column_width in enumerate(column_widths):
    workSheet.column_dimensions[get_column_letter(i + 1)].width = column_width

Answer 7

We can convert numbers to their ASCII values and give it to column_dimension parameter

import openpyxl as xl

work_book = xl.load_workbook('file_location')
sheet = work_book['Sheet1']
column_number = 2
column = str(chr(64 + column_number))
sheet.column_dimensions[column].width = 20
work_book.save('file_location')

Answer 8

Here is a more general, simplified solution for users new to the topic (Not specified for the question).

If you want to change the width or the height of cells in openpyxl (Version 3.0.9), you can do it simply by assigning the attributes of the cells with row_dimensions or column_dimensions.

import openpyxl
wb = openpyxl.Workbook()
sheet = wb["Sheet"]


sheet["A1"] = "Tall row"
sheet["B2"] = "Wide column"

# Change height of row A1
sheet.row_dimensions[1].height = 100
# Change width of column B
sheet.column_dimensions["B"].width = 50

wb.save("StackOverflow.xlsx")

Answer 9

This is a dirty fix. But openpyxl actually supports auto_fit. But there is no method to access the property.

import openpyxl
from openpyxl.utils import get_column_letter

wb = openpyxl.load_workbook("Example.xslx")
ws = wb["Sheet1"]
for i in range(1, ws.max_column+1):
    ws.column_dimensions[get_column_letter(i)].bestFit = True
    ws.column_dimensions[get_column_letter(i)].auto_size = True

Answer 10

This is my version referring @Virako 's code snippet

def adjust_column_width_from_col(ws, min_row, min_col, max_col):

        column_widths = []

        for i, col in \
                enumerate(
                    ws.iter_cols(min_col=min_col, max_col=max_col, min_row=min_row)
                ):

            for cell in col:
                value = cell.value
                if value is not None:

                    if isinstance(value, str) is False:
                        value = str(value)

                    try:
                        column_widths[i] = max(column_widths[i], len(value))
                    except IndexError:
                        column_widths.append(len(value))

        for i, width in enumerate(column_widths):

            col_name = get_column_letter(min_col + i)
            value = column_widths[i] + 2
            ws.column_dimensions[col_name].width = value

And how to use is as follows,

adjust_column_width_from_col(ws, 1,1, ws.max_column)

Answer 11

All the above answers are generating an issue which is that col[0].column is returning number while worksheet.column_dimensions[column] accepts only character such as 'A', 'B', 'C' in place of column. I've modified @Virako's code and it is working fine now.

import re
import openpyxl
..
for col in _ws.columns:
    max_lenght = 0
    print(col[0])
    col_name = re.findall('\w\d', str(col[0]))
    col_name = col_name[0]
    col_name = re.findall('\w', str(col_name))[0]
    print(col_name)
    for cell in col:
        try:
            if len(str(cell.value)) > max_lenght:
                max_lenght = len(cell.value)
        except:
            pass
    adjusted_width = (max_lenght+2)
    _ws.column_dimensions[col_name].width = adjusted_width

Answer 12

Another approach without storing any state could be like this:

from itertools import chain
# Using `ws` as the Worksheet
for cell in chain.from_iterable(ws.iter_cols()):
    if cell.value:
        ws.column_dimensions[cell.column_letter].width = max(
            ws.column_dimensions[cell.column_letter].width,
            len(f"{cell.value}"),
        )

Answer 13

I had to change @User3759685 above answer to this when the openpxyl updated. I was getting an error. Well @phihag reported this in the comments as well

for column_cells in ws.columns:
    new_column_length = max(len(as_text(cell.value)) for cell in column_cells)
    new_column_letter = (openpyxl.utils.get_column_letter(column_cells[0].column))
    if new_column_length > 0:
        ws.column_dimensions[new_column_letter].width = new_column_length + 1

Answer 14

Compiling and applying multiple suggestions above, and extending merged cells detection to the horizontally merged cells only, I could offer this code:

def adjust_width(ws):
    """
    Adjust width of the columns
    @param ws: worksheet
    @return: None
    """

    def is_merged_horizontally(cell):
        """
        Checks if cell is merged horizontally with an another cell
        @param cell: cell to check
        @return: True if cell is merged horizontally with an another cell, else False
        """
        cell_coor = cell.coordinate
        if cell_coor not in ws.merged_cells:
            return False
        for rng in ws.merged_cells.ranges:
            if cell_coor in rng and len(list(rng.cols)) > 1:
                return True
        return False

    for col_number, col in enumerate(ws.columns, start=1):
        col_letter = get_column_letter(col_number)

        max_length = max(
            len(str(cell.value or "")) for cell in col if not is_merged_horizontally(cell)
        )
        adjusted_width = (max_length + 2) * 0.95
        ws.column_dimensions[col_letter].width = adjusted_width

Answer 15

After update from openpyxl2.5.2a to latest 2.6.4 (final version for python 2.x support), I got same issue in configuring the width of a column.

Basically I always calculate the width for a column (dims is a dict maintaining each column width):

dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value))))

Afterwards I am modifying the scale to something shortly bigger than original size, but now you have to give the "Letter" value of a column and not anymore a int value (col below is the value and is translated to the right letter):

worksheet.column_dimensions[get_column_letter(col)].width = value +1 

This will fix the visible error and assigning the right width to your column ;) Hope this help.

Answer 16

I made a function that is very fast with large Excel files because it uses pandas.read_excel

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def auto_adjust_column_width(file_path, sheet_name=0):
    column_widths = []

    df = pd.read_excel(file_path, sheet_name=sheet_name, header=None)
    for col in df.columns:
        max_length = int(df[col].astype(str).str.len().max() * 1.2)
        column_widths.append(max_length)

    wb = load_workbook(file_path)
    if isinstance(sheet_name, int):
        sheet_name = wb.sheetnames[sheet_name]

    worksheet = wb[sheet_name]
    for i, column_width in enumerate(column_widths):
        column = get_column_letter(i+1)
        worksheet.column_dimensions[column].width = column_width
    wb.save(file_path)

Answer 17

When this came up for me, I just did everything I wanted to do with openpyxl, saved the workbook, and opened it again with pywin32. Pywin32 has autofit built in without having to make a bunch of rules/conditions.

Edit: I should note that pywin32 only works with Windows.

from win32com.client import Dispatch

excel = Dispatch('Excel.Application')
wb = excel.Workbooks.Open("excelFile.xlsx")

excel.Worksheets(1).Activate()
excel.ActiveSheet.Columns.AutoFit()

wb.Save()
wb.Close()
excel.Quit()

I did add a rule, however, because I had one text column that had some long values I didn't need to show. I limited any column to 75 characters.

excel = Dispatch('Excel.Application')
wb = excel.Workbooks.Open("excelFile.xlsx")

excel.Worksheets(1).Activate()
excel.ActiveSheet.Columns.AutoFit()

for col in excel.ActiveSheet.Columns:
    if col.ColumnWidth > 75:
        col.ColumnWidth = 75

wb.Save()
wb.Close()
excel.Quit()
 

Answer 18

Here is an answer for Python 3.8 and OpenPyXL 3.0.0.

I tried to avoid using the get_column_letter function but failed.

This solution uses the newly introduced assignment expressions aka "walrus operator":

import openpyxl
from openpyxl.utils import get_column_letter

workbook = openpyxl.load_workbook("myxlfile.xlsx")

worksheet = workbook["Sheet1"]

MIN_WIDTH = 10
for i, column_cells in enumerate(worksheet.columns, start=1):
    width = (
        length
        if (length := max(len(str(cell_value) if (cell_value := cell.value) is not None else "")
                          for cell in column_cells)) >= MIN_WIDTH
        else MIN_WIDTH
    )
    worksheet.column_dimensions[get_column_letter(i)].width = width

Answer 19

Since in openpyxl 2.6.1, it requires the column letter, not the column number, when setting the width.

 for column in sheet.columns:
    length = max(len(str(cell.value)) for cell in column)
    length = length if length <= 16 else 16
    sheet.column_dimensions[column[0].column_letter].width = length

Answer 20

Just insert the below line of code in your file

# 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