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)
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')
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 fogured 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
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)
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
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.
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
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
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
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
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
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)
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
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.
Source: Stackoverflow.com