Python - 自动调整 Excel 文件列的宽度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39529662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python - Automatically adjust width of an excel file's columns
提问by Aditya
Newbie - I have a Python script that adjusts the width of different columns of an excel file, according to the values specified:
新手 - 我有一个 Python 脚本，可以根据指定的值调整 excel 文件不同列的宽度：
import openpyxl
from string import ascii_uppercase
newFile = "D:\Excel Files\abc.xlsx"
wb = openpyxl.load_workbook(filename = newFile)
worksheet = wb.active
for column in ascii_uppercase:
if (column=='A'):
worksheet.column_dimensions[column].width = 30
elif (column=='B'):
worksheet.column_dimensions[column].width = 40
elif (column=='G'):
worksheet.column_dimensions[column].width = 45
else:
worksheet.column_dimensions[column].width = 15
wb.save(newFile)
Is there any way through which we can adjust the width of every column to its most optimum value, without explicitly specifying it for different columns (means, without using this "if-elif-elif-......-elif-else" structure)? Thanks!
有什么方法可以将每列的宽度调整到其最佳值，而无需为不同的列明确指定它（意味着，不使用此“ if-elif-elif-......-elif-else“ 结构体）？谢谢！
回答by oldsea
for col in worksheet.columns:
max_length = 0
column = col[0].column # Get the column name
# Since Openpyxl 2.6, the column name is ".column_letter" as .column became the column number (1-based)
for cell in col:
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 could probably be made neater but it does the job. You will want to play around with the adjusted_width value according to what is good for the font you are using when viewing it. If you use a monotype you can get it exact but its not a one-to-one correlation so you will still need to adjust it a bit.
这可能会更整洁，但它可以完成工作。您将需要根据查看时使用的字体的优点来调整调整宽度值。如果您使用 monotype，您可以获得精确但它不是一对一的相关性，因此您仍然需要对其进行一些调整。
If you want to get fancy and exact without monotype you could sort letters by width and assign each width a float value which you then add up. This would require a third loop parsing each character in the cell value and summing up the result for each column and probably a dictionary sorting characters by width, perhaps overkill but cool if you do it.
如果你想在没有单字的情况下变得花哨和精确，你可以按宽度对字母进行排序，并为每个宽度分配一个浮点值，然后将其相加。这将需要第三次循环解析单元格值中的每个字符并总结每列的结果，可能还需要一个按宽度对字符进行排序的字典，如果你这样做可能会矫枉过正但很酷。
Edit: Actually there seems to be a better way of measuring visual size of text: linkpersonally I would prefer the matplotlib technique.
编辑：实际上似乎有一种更好的方法来测量文本的视觉大小：个人链接我更喜欢 matplotlib 技术。
Hope I could be of help, my very first stackoverflow answer =)
希望我能有所帮助，我的第一个 stackoverflow 答案 =)
回答by Manuel G
Updated version as of openpyxl 3.0.0 (using .columns fails with TypeError: expected <class 'str'>:
从 openpyxl 3.0.0 开始更新版本（使用 .columns 失败并显示TypeError: expected <class 'str'>：
for column_cells in ws.columns:
length = max(len(as_text(cell.value)) for cell in column_cells)
ws.column_dimensions[column_cells[0].column_letter].width = length
回答by Virako
I have a problem with merged_cells and autosize not work correctly, if you have the same problem, you can solve adding the next lines inside code of oldsea
我遇到了merged_cells 和autosize 无法正常工作的问题，如果你有同样的问题，你可以解决在oldsea 的代码中添加下一行
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
回答by freddiemacintosh
def auto_format_cell_width(ws):
for letter in range(1,ws.max_column):
maximum_value = 0
for cell in ws[get_column_letter(letter)]:
val_to_check = len(str(cell.value))
if val_to_check > maximum_value:
maximum_value = val_to_check
ws.column_dimensions[get_column_letter(letter)].width = maximum_value + 1
回答by n.dury
If possible you should determine the length of the longest entry in the column and use that to set the width.
如果可能，您应该确定列中最长条目的长度并使用它来设置宽度。
I'm assuming you can make use of for entry in ascii_uppercase.
我假设您可以在 ascii_uppercase 中使用 for 条目。
I'm on mobile atm so can't give a concrete code example but what I said before should help you get closer to what you want to achieve.
我使用的是移动 atm，因此无法给出具体的代码示例，但我之前所说的应该可以帮助您更接近您想要实现的目标。
