How to Auto-Adjust Column width by Python openpyxl
Adjustment of the column’s width as per the text or headings of the column is particularly important when you copy data from one sheet to another as using Python openpyxl, Pandas etc.
Auto-Adjusting column width of an Excel sheet using openpyxl
Adjustment of the column's width as per the text or headings of the column is particularly important when you copy data from one sheet to another while using Python openpyxl, Pandas, etc.
As you might have seen in the copying data tutorial, the destination sheet’s column width does not match the text it contains.
So, the user has to double-click the handle or drag & drop to adjust the widths in order to read the headings/text in cells thoroughly.
In this tutorial, we will provide a solution to auto-adjust the column widths for:
- A newly created sheet after copying data from another Workbook sheet
- An existing sheet
Auto-adjust columns of a newly created sheet
First, have a look at an example below where we will copy data from one Workbook sheet to another. For comparison, the source sheet and destination sheet are displayed after executing this code:
import openpyxl as copy_ex;
# Loading source File - Where you want to copy from and specify sheet name
wb_src = copy_ex.load_workbook("test_Excel.xlsx")
sheet_src = wb_src["Product Information"]
# Loading Destination Workbook and specify sheet
wb_dest = copy_ex.load_workbook("test_copy.xlsx")
sheet_dest = wb_dest.active
for i in range (1, sheet_src.max_row + 1):
for j in range (1, sheet_src.max_column + 1):
# Assign source cell value
cell_src = sheet_src.cell(row = i, column = j)
# paste to destination cell
sheet_dest.cell(row = i, column = j).value = cell_src.value
# Save the file after copying data
wb_dest.save("test_copy.xlsx")
Result:
You can see in the above graphic, the destination sheet’s columns are narrower than the text width.
The example of auto-adjusting the column widths
We added a few lines of code to expand the column widths as per the text in headings – so that it’s all visible.
Program:
import openpyxl as copy_colauto;
# Loading source File - Where you want to copy from and specify sheet name
wb_src_cols = copy_colauto.load_workbook("test_Excel.xlsx")
sheet_src = wb_src_cols["Product Information"]
# Loading Destination Workbook and specify sheet
wb_dest_cols = copy_colauto.load_workbook("test_copy.xlsx")
sheet_dest = wb_dest_cols.active
for i in range (1, sheet_src.max_row + 1):
for j in range (1, sheet_src.max_column + 1):
# Assign source cell value
cell_src = sheet_src.cell(row = i, column = j)
# paste to destination cell
sheet_dest.cell(row = i, column = j).value = cell_src.value
#Logic for making column width
for col in sheet_dest.columns:
SetLen = 0
column = col[0].column_letter # Get the column name
for cell in col:
if len(str(cell.value)) > SetLen:
SetLen = len(str(cell.value))
set_col_width = SetLen + 5
#Setting the column width
sheet_dest.column_dimensions[column].width = set_col_width
# Save the file after copying data
wb_dest_cols.save("col_adjust.xlsx")
Output:
Adjusting column-width of an existing sheet
Similarly, if you want to adjust the column width of an existing sheet without performing any other thing – you can do this as well.
The program below uses a “test_sheet.xlsx” where you can see the column width is less than the text it contains:
By running the program, we will make its column width per the text:
from openpyxl import load_workbook
#Load an existing workbook
wb_col = load_workbook("test_sheet.xlsx")
#Using active sheet
sheet = wb_col.active
#Logic for making column width
for col in sheet.columns:
SetLen = 0
column = col[0].column_letter # Get the column name
for cell in col:
if len(str(cell.value)) > SetLen:
SetLen = len(str(cell.value))
set_col_width = SetLen + 5
#Setting the column width
sheet.column_dimensions[column].width = set_col_width
# Save the workbook
wb_col.save("test_sheet.xlsx")
Result:
You may use a value other than 5 for set_col_width to increase/decrease the width.
