python-openpyxl-调整列宽siz

我有以下脚本将CSV文件转换为XLSX文件，但我的列大小非常狭窄。 每次我必须用鼠标拖动它们以读取数据。 有人知道如何在openpyxl中设置列宽吗？

这是我正在使用的代码。

#!/usr/bin/python2.6

import csv

from openpyxl import Workbook

from openpyxl.cell import get_column_letter

f = open('users_info_cvs.txt', "rU")

csv.register_dialect('colons', delimiter=':')

reader = csv.reader(f, dialect='colons')

wb = Workbook()

dest_filename = r"account_info.xlsx"

ws = wb.worksheets[0]

ws.title = "Users Account Information"

for row_index, row in enumerate(reader):

for column_index, cell in enumerate(row):

column_letter = get_column_letter((column_index + 1))

ws.cell('%s%s'%(column_letter, (row_index + 1))).value = cell

wb.save(filename = dest_filename)

Satish asked 2020-02-04T17:57:32Z

9个解决方案

64 votes

您可以估计（或使用等宽字体）来实现。 假设数据是一个嵌套数组，例如[['a1'，'a2']，['b1'，'b2']]

我们可以获取每一列中的最大字符数。 然后设置宽度。 宽度正好是等宽字体的宽度（如果至少未更改其他样式）。 即使您使用可变宽度的字体，这也是一个不错的估计。 这不适用于公式。

column_widths = []

for row in data:

for i, cell in enumerate(row):

if len(column_widths) > i:

if len(cell) > column_widths[i]:

column_widths[i] = len(cell)

else:

column_widths += [len(cell)]

for i, column_width in enumerate(column_widths):

worksheet.column_dimensions[get_column_letter(i+1)].width = column_width

有点骇人听闻，但您的报告将更具可读性。

Bufke answered 2020-02-04T17:57:51Z

33 votes

我对Bufke答案的不同意见。 避免对数组进行一些分支，并忽略空单元格/列。

现在修复了非字符串单元格值。

ws = your current worksheet

dims = {}

for row in ws.rows:

for cell in row:

if cell.value:

dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value))))

for col, value in dims.items():

ws.column_dimensions[col].width = value

velis answered 2020-02-04T17:58:15Z

29 votes

设置至少在openpyxl版本2.4.0中有效的所有列的宽度的更多pythonic方法：

for column_cells in worksheet.columns:

length = max(len(as_text(cell.value)) for cell in column_cells)

worksheet.column_dimensions[column_cells[0].column].width = length

as_text函数应该是将值转换为适当长度的字符串的东西，例如Python 3：

def as_text(value):

if value is None:

return ""

return str(value)

User3759685 answered 2020-02-04T17:58:40Z

6 votes

我的merged_cells有问题，并且autosize无法正常工作，如果您有同样的问题，则可以使用以下代码解决：

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 answered 2020-02-04T17:59:00Z

3 votes

上面接受的答案略有改进，我认为是更Python化的（寻求宽恕比寻求许可要好）

column_widths = []

for row in workSheet.iter_rows():

for i, cell in enumerate(row):

try:

column_widths[i] = max(column_widths[i], len(cell.value))

except IndexError:

column_widths.append(len(cell.value))

for i, column_width in enumerate(column_widths):

workSheet.column_dimensions[get_column_letter(i + 1)].width = column_width

shayst answered 2020-02-04T17:59:20Z

2 votes

这是我的版本，引用@Virako的代码段

def adjust_column_width_from_col(ws, min_row, min_col, max_col):

column_widths = []

for i, col in \

enumerate(

ws.iter_cols(min_col=min_col, max_col=max_col, min_row=min_row)

):

for cell in col:

value = cell.value

if value is not None:

if isinstance(value, str) is False:

value = str(value)

try:

column_widths[i] = max(column_widths[i], len(value))

except IndexError:

column_widths.append(len(value))

for i, width in enumerate(column_widths):

col_name = get_column_letter(min_col + i)

value = column_widths[i] + 2

ws.column_dimensions[col_name].width = value

以及使用方法如下

adjust_column_width_from_col(ws, 1,1, ws.max_column)

alones answered 2020-02-04T17:59:44Z

1 votes

以上所有答案均产生一个问题，即col [0] .column返回数字，而worksheet.column_dimensions [column]仅接受诸如'A'，'B'，'C'之类的字符代替列。 我已经修改了@Virako的代码，现在可以正常工作了。

import re

import openpyxl

..

for col in _ws.columns:

max_lenght = 0

print(col[0])

col_name = re.findall('\w\d', str(col[0]))

col_name = col_name[0]

col_name = re.findall('\w', str(col_name))[0]

print(col_name)

for cell in col:

try:

if len(str(cell.value)) > max_lenght:

max_lenght = len(cell.value)

except:

pass

adjusted_width = (max_lenght+2)

_ws.column_dimensions[col_name].width = adjusted_width

Ssubrat Rrudra answered 2020-02-04T18:00:05Z

1 votes

我们可以将数字转换为其ASCII值，并将其提供给column_dimension参数

import openpyxl as xl

work_book = xl.load_workbook('file_location')

sheet = work_book['Sheet1']

column_number = 2

column = str(chr(64 + column_number))

sheet.column_dimensions[column].width = 20

work_book.save('file_location')

Prashant Tiwari answered 2020-02-04T18:00:25Z

1 votes

当openpyxl更新时，我不得不更改以上答案的@ User3759685。 我遇到了错误。 @phihag也在评论中报告了这一点

for column_cells in ws.columns:

new_column_length = max(len(as_text(cell.value)) for cell in column_cells)

new_column_letter = (openpyxl.utils.get_column_letter(column_cells[0].column))

if new_column_length > 0:

ws.column_dimensions[new_column_letter].width = new_column_length + 1

Monte Jones answered 2020-02-04T18:00:45Z