Can we do auto width for all columns instead of manually setting for each column. I have like 100000 rows and iterating just to find out the max length is time consuming.
I have not seen a method to implement this in a way that doesn't require going row by row, unfortunately. Calling ".auto_size" on the worksheet attribute, like this: "ws.column_dimensions['A'].auto_size", should achieve the same result, but requires going through row by row. Alternatively, you could just call "ws.column_dimensions['A'].width = " and set that to a large number that you know will display all the data. It will likely leave white space in most of your columns, however.
@@data_automation no, auto_size won't work, and it's just an alias to bestFit which also doesn't work. I've tried to set them to True, and set customWidth to False, and it didn't worked too. Then i've set the width to 0 + customWidth=false, bestFit=True, auto_size=True... not working either. There's plenty of discussions about this. Yes it should work, but it doesn't and it's been like that for years. Now the only thing that can be done is to check for the len of strings in a column but: - It doesn't work with functions - And it's not accurate at all with some font types. Some letters are wider than other like the upper case letters There's a way to get closer by using Pillow. Like _font = ImageFont.truetype(font="arial.ttf", size=10) _myTextWidth = _font.getlength("HelloWorld") But even there it's not what Excel expect to receive. I think that it's width is not in Pixel at all. It's cm or inches... which mean that you got to work with DPI. So it's a pain in the ass. My rule of thumb is that i take the value of getlength, and devide that by 5.3 and i'm really close to what Excel do -> On my computer.
@@PunisherQc Right - I have only read into methods other than the one recorded, I haven't tested the "auto_size" or "width" methodology, though I understood them to operate in the way way as the video describes. That you are still required to go row by row to recognize the longest string and capture/prescribe that value to the whole column. I have recognized that it doesn't always fit the text 100% (sometimes being a bit too long or short, which I have attributed to capital letters in the past - good catch with the font types, I hadn't thought of that), but it captures it with close enough accuracy for my needs. I agree wholeheartedly, it truly is frustrating, that these attributes aren't working. There really should be a simpler way the achieve this.
from openpyxl import load_workbook from openpyxl.utils import get_column_letter def auto_adjust_width(ws): for col in ws.columns: max_length = 0 for cell in col: try: if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = max_length + 2 # Adjusted width with padding col_letter = get_column_letter(col[0].column) ws.column_dimensions[col_letter].width = adjusted_width # Load the existing Excel file excel_file = "Viandas.xlsx" wb = load_workbook(excel_file) # Adjust the width of each column in each sheet for sheet_name in wb.sheetnames: ws = wb[sheet_name] auto_adjust_width(ws) # Save the workbook wb.save(excel_file) print("All columns have been adjusted in the Excel file.") This code does brother
@bersi3306 True! Thank you, great call out. The len function doesn't work on integer values. You can cast the value to become a string (using "str()"), which will let you see the length of a long number.
thanks, it helped me very much :)
Very welcome! Glad it helped.
Can we do auto width for all columns instead of manually setting for each column. I have like 100000 rows and iterating just to find out the max length is time consuming.
I have not seen a method to implement this in a way that doesn't require going row by row, unfortunately.
Calling ".auto_size" on the worksheet attribute, like this: "ws.column_dimensions['A'].auto_size", should achieve the same result, but requires going through row by row.
Alternatively, you could just call "ws.column_dimensions['A'].width = " and set that to a large number that you know will display all the data. It will likely leave white space in most of your columns, however.
@@data_automation no, auto_size won't work, and it's just an alias to bestFit which also doesn't work. I've tried to set them to True, and set customWidth to False, and it didn't worked too. Then i've set the width to 0 + customWidth=false, bestFit=True, auto_size=True... not working either. There's plenty of discussions about this. Yes it should work, but it doesn't and it's been like that for years.
Now the only thing that can be done is to check for the len of strings in a column but:
- It doesn't work with functions
- And it's not accurate at all with some font types. Some letters are wider than other like the upper case letters
There's a way to get closer by using Pillow. Like
_font = ImageFont.truetype(font="arial.ttf", size=10)
_myTextWidth = _font.getlength("HelloWorld")
But even there it's not what Excel expect to receive. I think that it's width is not in Pixel at all. It's cm or inches... which mean that you got to work with DPI. So it's a pain in the ass. My rule of thumb is that i take the value of getlength, and devide that by 5.3 and i'm really close to what Excel do -> On my computer.
@@PunisherQc Right - I have only read into methods other than the one recorded, I haven't tested the "auto_size" or "width" methodology, though I understood them to operate in the way way as the video describes. That you are still required to go row by row to recognize the longest string and capture/prescribe that value to the whole column.
I have recognized that it doesn't always fit the text 100% (sometimes being a bit too long or short, which I have attributed to capital letters in the past - good catch with the font types, I hadn't thought of that), but it captures it with close enough accuracy for my needs. I agree wholeheartedly, it truly is frustrating, that these attributes aren't working. There really should be a simpler way the achieve this.
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
def auto_adjust_width(ws):
for col in ws.columns:
max_length = 0
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except:
pass
adjusted_width = max_length + 2 # Adjusted width with padding
col_letter = get_column_letter(col[0].column)
ws.column_dimensions[col_letter].width = adjusted_width
# Load the existing Excel file
excel_file = "Viandas.xlsx"
wb = load_workbook(excel_file)
# Adjust the width of each column in each sheet
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
auto_adjust_width(ws)
# Save the workbook
wb.save(excel_file)
print("All columns have been adjusted in the Excel file.")
This code does brother
Great video. There is a bug though:
len(ws[f'{letter}{row_number}'].value)
@bersi3306 True! Thank you, great call out. The len function doesn't work on integer values.
You can cast the value to become a string (using "str()"), which will let you see the length of a long number.
@@data_automation how is the final code of this line. I do not understand the casting. How you cast this expression and compare with integer number?
Excellent question! The code would looks like the following:
len(str(ws[f'{letter}{row_number}'].value))
@@data_automation Thank you. Now it works very nicely.
Excellent! Very glad it works for you now. Let me know if you have any further questions.