如何在 openpyxl 中将列宽设置为最佳拟合

Question

我已经用一些数据填充了一个工作表，我正在尝试使列宽以假设它们最适合，如下所示。基本上是双击列宽调整分隔符时发生的那种自动调整。

这是我的最小示例，就我对openpyxl文档的理解而言，它应该有效：

import openpyxl
from typing import NoReturn
def columns_best_fit(ws: openpyxl.worksheet.worksheet.Worksheet) -> NoReturn:
"""
Make all columns best fit
"""
column_letters = tuple(openpyxl.utils.get_column_letter(col_number + 1) for col_number in range(ws.max_column))
for column_letter in column_letters:
dim = openpyxl.worksheet.dimensions.ColumnDimension(ws, index=column_letter, bestFit=True, customWidth=True)
ws.column_dimensions[column_letter] = dim

wb = openpyxl.Workbook()
ws = wb.active
ws.append(("Long Column Header 1", "Even Longer Column Header 2"))
ws.append(("some data", "more data"))
columns_best_fit(ws)
wb.save("column_width_test.xlsx")

但是，当我打开生成的文件时，列只是稍微宽一点，但肯定不是最合适的。

Answer 1

经过几个小时的研究，我终于找到了。

备注：在下面的代码中，工作表是工作表名称。通常在文档中，我们可以将其视为ws。请不要忘记更改工作表名称。

# 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

更新：此代码不适用于所有人，但不要犹豫，尝试一下 😆

Answer 2

我已经编辑了你的函数。它现在应该可以工作了。

def columns_best_fit(ws: openpyxl.worksheet.worksheet.Worksheet) -> NoReturn:
"""
Make all columns best fit
"""
column_letters = tuple(openpyxl.utils.get_column_letter(col_number + 1) for col_number in range(ws.max_column))
for column_letter in column_letters:
ws.column_dimensions[column_letter].bestFit = True

Answer 3

参考@Mounesh答案，设置列宽后似乎没有保存工作簿。这将起作用。

# 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

workbook = load_workbook("excel_name.xlsx")
for sheet_name in workbook.sheetnames:
for column_cells in workbook[sheet_name].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:
workbook[sheet_name].column_dimensions[new_column_letter].width = new_column_length*1.23
workbook.save("excel_name.xlsx")

Answer 4

我用了一些这样的：

from openpyxl.worksheet.dimensions import ColumnDimension
wb = Workbook()
ws = wb.active
ColumnDimension(ws, bestFit=True)