Python - 自动调整Excel文件列的宽度

Question

新手 - 我有一个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)

是否有一种方法可以调整每列的宽度到最优值，而不需要为不同列明确指定宽度(也就是说，不使用这种 "if-elif-elif-......-elif-else" 结构)？谢谢！

- Aditya

---

使用列名和宽度的字典。由于文件格式要求每列有值，因此没有什么神奇的方法可以做到这一点。 - Charlie Clark

相关：使用xlwt自动调整列宽。无论您使用哪个软件包，列宽的问题基本上都是相同的。链接的问题有一个基于Arial 10宽度的答案（旧版.xls文件中的默认字体）。类似的技术可以用于Calibri 11（.xlsx文件的默认字体）。 - John Y

Answer 1

for col in worksheet.columns:
     max_length = 0
     column = col[0].column_letter # Get the column name
     for cell in col:
         try: # Necessary to avoid error on empty cells
             if len(str(cell.value)) > max_length:
                 max_length = len(str(cell.value))
         except:
             pass
     adjusted_width = (max_length + 2) * 1.2
     worksheet.column_dimensions[column].width = adjusted_width

这个代码可能可以更加简洁，但它能够胜任。您需要根据所使用的字体进行调整，以便查看时效果更好。如果您使用等宽字体，那么您可以精确匹配，但这并不是一一对应的，因此您仍然需要进行一些调整。

如果您想要精确而又花哨的效果，而且没有使用等宽字体，那么您可以按照宽度排序字母并为每个宽度分配一个浮点值，然后将它们相加。这将需要第三个循环解析单元格值中的每个字符，并为每个列总和结果，可能需要一个按宽度排序字符的字典，也许有点过头了，但如果您这样做，就很酷。

编辑：实际上似乎有一种更好的方法来测量文本的视觉大小：链接我个人更喜欢使用 matplotlib 技术。

希望我能有所帮助，这是我在 StackOverflow 上的第一个回答 =)

- oldsea

---

这个问题基本上也涵盖了你的主题。链接 - oldsea

我认为这里有一个错误，阻止了对数字单元格值的正确操作。在第8行，应该是max_length = len(str(cell.value))而不是max_length = len(cell.value)。 - Roberto

在什么情况下需要使用try / except？我似乎无法通过空值到达except块。 - Noel Evans

Answer 2

截至openpyxl 3.0.0的更新版本（使用.columns将导致TypeError: expected <class 'str'>）：

for column_cells in ws.columns:
    length = max(len(str(cell.value)) for cell in column_cells)
    ws.column_dimensions[column_cells[0].column_letter].width = length

- Manuel G

---

根据@virako的建议，为未合并的单元格添加过滤器：for column_cells in ws.columns: unmerged_cells = list(filter(lambda cell_to_check: cell_to_check.coordinate not in ws.merged_cells, column_cells)) length = max(len(str(cell.value)) for cell in unmerged_cells) ws.column_dimensions[unmerged_cells[0].column_letter].width = length * 1.2 - Arvind Sridharan

很高兴听到这个消息。我已经将这个内容放在了 Github 的 Gist 上，以便更容易地阅读 - https://gist.github.com/arvsr1988/4468e7f9bb9c5f3aa514eea6a14804b6 - Arvind Sridharan

这个有效，但我必须用str()替换as_text()。 - Greg G.

对我不起作用 - Nam G VU

Answer 3

使用最新的 openpyxl 库，您可以这样做:

from openpyxl.utils import get_column_letter
for idx, col in enumerate(worksheet.columns, 1):
    worksheet.column_dimensions[get_column_letter(idx)].auto_size = True

- Corvax

---

这似乎没有任何作用；列与之前相同；请参见此问题的第一个评论（bestFit似乎是auto_size的别名）。 - ssc

无法工作 - Nam G VU

对我来说，它隐藏了列而不是自动调整列宽。 - Cristian F.

@CristianF。我在某些版本中也遇到了同样的问题。这很奇怪。 - Corvax

我相信这个指令对于LibreOffice完美运作，但是对于Excel则不然。当后者打开文件时，它无法理解并隐藏了列。 - Corvax

对我来说，当我也设置cell.alignment.warp_text=True时，看起来更好。 - ginger

Answer 4

基于上面的评论并添加这篇文章openpyxl - 调整列宽度大小。我成功了，但正确答案应该是:

from openpyxl.utils import get_column_letter

for col in ws.columns:
    max_length = 0
    column = get_column_letter(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
    ws.column_dimensions[column].width = adjusted_width

我正在使用openpyxl版本 "^3.0.5"

- Phùng Hoàng

Answer 5

我有一个关于合并单元格和自动调整大小不正确的问题，如果你也遇到了同样的问题，可以在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

- Virako

Answer 6

只需将以下代码行插入您的文件中。如没有，请安装所需的库。

# Imorting the necessary modules
try:
        from openpyxl.cell import get_column_letter
except ImportError:
        from openpyxl.utils import get_column_letter
        from openpyxl.utils import column_index_from_string
from openpyxl import load_workbook
import openpyxl
from openpyxl import Workbook



for column_cells in sheet.columns:
    new_column_length = max(len(str(cell.value)) for cell in column_cells)
    new_column_letter = (get_column_letter(column_cells[0].column))
    if new_column_length > 0:
        sheet.column_dimensions[new_column_letter].width = new_column_length*1.23

- Mounesh

Answer 7

for column_cells in sheet.columns:
    new_column_length = max(len(str(cell.value)) for cell in column_cells)
    new_column_letter = (get_column_letter(column_cells[0].column))
    if new_column_length > 0:
        sheet.column_dimensions[new_column_letter].width = new_column_length*1.23

- Meghana Naik

Answer 8

你可以尝试一下这个，对我很有效：

from openpyxl.utils import get_column_letter

for columns in worksheet.columns:
    col = get_column_letter(columns[0].column)
    worksheet.column_dimensions[col].auto_size = True

- samad

Answer 9

在我看来，pywin32是一个更好的Python Excel包，因为它模拟了Excel VBA。Range.AutoFit方法可以解决这个问题。以下是示例：

import win32com.client as win32

xlApp = win32.Dispatch('Excel.Application')
wb = xlApp.Workbooks.Open(***file path to excel file goes here***)

ws = wb.Worksheets[***name of worksheet trying adjust column width***]
ws.Columns.AutoFit()

注意：Worksheet.Columns属性代表一个Range对象。Autofit是属于Range对象的一个方法。

- Jay Jara

Answer 10

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

- freddiemacintosh

---

请勿仅发布代码作为答案，还需包含解释您的代码是如何解决问题以及其工作原理。带有解释的答案通常更具质量，并更有可能获得赞同。 - Arco Bast

这不是与现有解决方案非常相似吗？ - AMC