Automating tasks in Excel can significantly improve efficiency and reduce manual errors. One common task is adjusting column widths to fit the content. Python, with its powerful libraries like openpyxl, can automate this process easily.

In this tutorial, we’ll explore how to adjust Excel column widths automatically using Python. We’ll demonstrate how to create a new Excel workbook, populate it with data, and then adjust the column widths based on the content. This automation not only saves time but also ensures that your Excel sheets are neatly formatted and easy to read.

By the end of this tutorial, you’ll have a clear understanding of how to use Python to automate column width adjustments in Excel, allowing you to streamline your workflow and focus on more important tasks. Let’s dive in!

Create a file and give it a name with .py extension and paste the code below or you can also write your own excel and the paste the code below

Check here how

Code:

import openpyxl

# Create a new Excel workbook
wb = openpyxl.Workbook()
ws = wb.active

# Insert some data into the Excel sheet
data = [
    ['Name of people', 'Age', 'City'],
    ['Alice', 30, 'New York'],
    ['Bob', 25, 'Los Angeles'],
    ['Charlie', 35, 'Chicago']
]

for row in data:
    ws.append(row)

# Adjust column widths based on the content
for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        try:
            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

# Save the Excel workbook
wb.save('sample.xlsx')

# Open the Excel workbook
import os
os.system('start sample.xlsx')

Lets understand how the code is working for adjusting the coloumn

for row in data:
    ws.append(row)

# Adjust column widths based on the content
for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        try:
            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

1. for col in ws.columns:: This iterates over each column in the Excel sheet (ws is assumed to be the worksheet object).
2. max_length = 0: Initializes a variable to store the maximum length of content in the column.
3. column = col[0].column_letter: Retrieves the column letter (e.g., ‘A’, ‘B’, ‘C’) for the current column.
4. for cell in col:: Iterates over each cell in the current column.
5. try: and except:: Used to handle any exceptions that might occur when accessing the cell value. This is a defensive programming approach to ensure the code doesn’t break if it encounters an unexpected cell value.
6. if len(str(cell.value)) > max_length:: Checks if the length of the cell value (converted to a string) is greater than the current max_length.
7. max_length = len(cell.value): Updates max_length to the length of the current cell value if it is greater.
8. adjusted_width = (max_length + 2) * 1.2: Calculates the adjusted width for the column. It adds 2 to max_length to provide some padding and then multiplies by 1.2 to adjust for font and display differences.
9. ws.column_dimensions[column].width = adjusted_width: Sets the column width for the current column (column_dimensions[column]) to the calculated adjusted_width.

Output:

Explanation of Whole Code:

1. import openpyxl: Imports the openpyxl module, which is used for reading and writing Excel files.
2. wb = openpyxl.Workbook(): Creates a new Excel workbook and assigns it to the variable wb.
3. ws = wb.active: Gets the active sheet (the first sheet) of the workbook and assigns it to the variable ws.
4. data = [...]: Defines a list of lists containing the data to be inserted into the Excel sheet.
5. for row in data: ws.append(row): Iterates over each row in the data list and appends it to the Excel sheet.
6. Adjust column widths based on the content:
   • for col in ws.columns:: Iterates over each column in the Excel sheet.
   • max_length = 0: Initializes a variable to store the maximum length of content in the column.
   • column = col[0].column_letter: Gets the column letter for the current column.
   • for cell in col:: Iterates over each cell in the current column.
   • try: and except:: Used to handle any exceptions that might occur when accessing the cell value.
   • if len(str(cell.value)) > max_length:: Checks if the length of the cell value (converted to a string) is greater than the current max_length.
   • max_length = len(cell.value): Updates max_length to the length of the current cell value if it is greater.
   • adjusted_width = (max_length + 2) * 1.2: Calculates the adjusted width for the column.
   • ws.column_dimensions[column].width = adjusted_width: Sets the column width for the current column to the calculated adjusted_width.
7. wb.save('sample.xlsx'): Saves the Excel workbook to a file named ‘sample.xlsx’.
8. import os os.system('start sample.xlsx'): Imports the os module and uses it to open the ‘sample.xlsx’ file. The os.system('start sample.xlsx') command opens the file using the default application associated with Excel files on the system.

More on Excel Automation

In conclusion, automating the adjustment of Excel column widths using Python offers a significant advantage in efficiently managing and formatting large datasets. By dynamically adjusting column widths based on the content, you ensure that the data is displayed correctly and clearly, improving readability and usability. This automation eliminates the need for manual adjustments, saving time and effort. Additionally, Python’s flexibility and open-source libraries like openpyxl make it a powerful tool for automating various tasks in Excel, streamlining workflow processes, and enhancing productivity.