Python Excel设置列宽行高自适应：内容不再被截断

用openpyxl设置列宽、行高，按内容长度估算自适应列宽，避免文字显示成####或被截断，附完整代码。

场景痛点

导出的表在自己电脑上看好好的，发给同事打开发现金额列全是 ####，长文本列被截断成一串 #。原因是列宽没设。手动拖列又慢又不齐。用 openpyxl 遍历每一列，按该列最长内容算出合适宽度，一次性调好；行高也能统一设成舒服的尺寸。

用到的库

pip install openpyxl

完整代码

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter


def auto_fit(file_path: str) -> None:
wb = load_workbook(file_path)
ws = wb.active

# 1) 先按列估算内容宽度
col_widths = {}
for row in ws.iter_rows(values_only=False):
for cell in row:
if cell.value is None:
continue
text = str(cell.value)
# 中文字符按 2 个宽度算，英文按 1 个
length = sum(2 if ord(ch) > 127 else 1 for ch in text)
col_idx = cell.column
if length > col_widths.get(col_idx, 0):
col_widths[col_idx] = length

# 2) 应用到列宽，加 2 个字符的留白，最小 8、最大 40
for col_idx, width in col_widths.items():
letter = get_column_letter(col_idx)
ws.column_dimensions[letter].width = min(max(width + 2, 8), 40)

# 3) 行高：表头行设 22，数据行设 18
ws.row_dimensions[1].height = 22
for r in range(2, ws.max_row + 1):
ws.row_dimensions[r].height = 18

# 4) 也可以单独写死某一列宽度，比如备注列宽一点
if "备注" in [c.value for c in ws[1]]:
header = [c.value for c in ws[1]]
remark_col = header.index("备注") + 1
ws.column_dimensions[get_column_letter(remark_col)].width = 30

wb.save(file_path)
print("列宽行高已调整")


if __name__ == "__main__":
auto_fit(r"D:\reports\销售明细.xlsx")