有没有一种方法可以使用pandas.ExcelWriter自动调整Excel列宽？

Question

我被要求生成一些Excel报告。目前我在处理数据时非常依赖于pandas，因此自然希望使用pandas.ExcelWriter方法来生成这些报告。但是，固定的列宽度是个问题。

到目前为止，我的代码相当简单。假设我有一个名为df的数据框：

writer = pd.ExcelWriter(excel_file_path, engine='openpyxl')
df.to_excel(writer, sheet_name="Summary")

我查看了Pandas文档，但并没有找到设置列宽的选项。是否能自动调整列宽以适应数据？或者我可以在生成的xlsx文件上进行调整吗？ （我使用OpenPyXL库生成.xlsx文件，不知道这是否有影响。）

- badideas

---

目前看起来似乎不可能，可以在 Github 上开一个问题以进行增强（也许还有一个 PR？）。看起来并不难做。 - Jeff

谢谢Jeff，我已经提交了这个问题。我不确定是否有时间真正深入pandas代码库来解决它，但你永远不知道 :) - badideas

是的...看到你的问题了...如果需要帮助，请在问题下发表评论！（基本上需要将可选参数传递给“to_excel”，可能是包含列标题样式元素的“col_style=dict”（而不是默认的“header_style”，似乎现在已经硬编码） - Jeff

链接到pandas问题 - dmvianna

Answer 1

受user6178746的回答启发，我有以下内容：

# Given a dict of dataframes, for example:
# dfs = {'gadgets': df_gadgets, 'widgets': df_widgets}

writer = pd.ExcelWriter(filename, engine='xlsxwriter')
for sheetname, df in dfs.items():  # loop through `dict` of dataframes
    df.to_excel(writer, sheet_name=sheetname)  # send df to writer
    worksheet = writer.sheets[sheetname]  # pull worksheet object
    for idx, col in enumerate(df):  # loop through all columns
        series = df[col]
        max_len = max((
            series.astype(str).map(len).max(),  # len of largest item
            len(str(series.name))  # len of column name/header
            )) + 1  # adding a little extra space
        worksheet.set_column(idx, idx, max_len)  # set column width
writer.save()

- alichaudry

---

在我的情况下，我需要在“df.to_excel（...）”调用中使用“index=False”，否则列会偏移1个。 - Jesper - jtk.eth

如果您无法使用index=False（因为行上有多个索引），那么可以使用df.index.nlevels获取索引级别深度，然后使用此深度添加到您的set_column调用中：“worksheet.set_column(idx+nlevels, idx+nlevels, max_len)”。否则，长度将计算在帧的第一列中，然后应用于Excel中的第一列，这很可能是索引。 - ac24

@Dascienz，就像迭代dict实际上是迭代dict中的键一样（您不必手动输入dict.keys()），迭代pd.DataFrame会迭代列。您不必手动迭代df.columns。 - alichaudry

Pandas支持一种很好的符号表示法来计算字符串长度和其他内容：series.astype(str).map(len).max()可以重写为：series.astype(str).str.len().max()。 - Chaoste

如果你遇到了AttributeError: 'Worksheet' object has no attribute 'set_column'的错误，可能是因为你没有安装XlsxWriter并且pandas回退到openpyxl。执行pip install XlsxWriter应该可以解决这个问题 :) - Philippe Hebert

Answer 2

动态调整所有列的长度

writer = pd.ExcelWriter('/path/to/output/file.xlsx') 
df.to_excel(writer, sheet_name='sheetName', index=False, na_rep='NaN')

for column in df:
    column_length = max(df[column].astype(str).map(len).max(), len(column))
    col_idx = df.columns.get_loc(column)
    writer.sheets['sheetName'].set_column(col_idx, col_idx, column_length)

writer.save()

---

使用列名手动调整列

col_idx = df.columns.get_loc('columnName')
writer.sheets['sheetName'].set_column(col_idx, col_idx, 15)

---

使用列索引手动调整列

writer.sheets['sheetName'].set_column(col_idx, col_idx, 15)

---

如果上述任何一项失败了

AttributeError: 'Worksheet' object has no attribute 'set_column'

请确保安装了xlsxwriter：

pip install xlsxwriter

并将其用作引擎：

writer = pd.ExcelWriter('/path/to/output/file.xlsx', engine='xlsxwriter') 

---

如果您需要更全面的解释，可以在TDS上阅读文章Pandas ExcelWriter如何自动调整Excel列宽。

- Giorgos Myrianthous

---

这里的 df 是什么？您能展示一下包括 df 初始化的代码吗？ - parsecer

@parsecer 你可以参考我在帖子底部分享的文章。 - Giorgos Myrianthous

工作得非常完美，包括自动宽度、按列名指定的显式宽度以及通过安装xlswriter解决的异常。谢谢 :) - MattG

当列是多级索引时，这种列宽的检测方法将无法使用。在Excel中，多级索引会呈现为单元格合并。 - LogZ

我注意到原帖中使用了 engine='openpyxl'，但你建议安装 xlsxwriter。是 openpyxl 无法完成此任务，还是 xlsxwriter 更容易使用？ - Joe

Answer 3

我最近开始使用一个不错的包，叫做StyleFrame。

它可以接收DataFrame，并让你非常容易地对其进行样式设置...

默认情况下，列宽度会自动调整。

例如：

from StyleFrame import StyleFrame
import pandas as pd

df = pd.DataFrame({'aaaaaaaaaaa': [1, 2, 3], 
                   'bbbbbbbbb': [1, 1, 1],
                   'ccccccccccc': [2, 3, 4]})
excel_writer = StyleFrame.ExcelWriter('example.xlsx')
sf = StyleFrame(df)
sf.to_excel(excel_writer=excel_writer, row_to_add_filters=0,
            columns_and_rows_to_freeze='B2')
excel_writer.save()

您还可以更改列的宽度：

sf.set_column_width(columns=['aaaaaaaaaaa', 'bbbbbbbbb'],
                    width=35.3)

更新 1

在版本1.4中，best_fit参数已添加到StyleFrame.to_excel函数。请参阅文档。

更新 2

以下是适用于 StyleFrame 3.x.x 的示例代码。

from styleframe import StyleFrame
import pandas as pd

columns = ['aaaaaaaaaaa', 'bbbbbbbbb', 'ccccccccccc', ]
df = pd.DataFrame(data={
        'aaaaaaaaaaa': [1, 2, 3, ],
        'bbbbbbbbb': [1, 1, 1, ],
        'ccccccccccc': [2, 3, 4, ],
    }, columns=columns,
)
excel_writer = StyleFrame.ExcelWriter('example.xlsx')
sf = StyleFrame(df)
sf.to_excel(
    excel_writer=excel_writer, 
    best_fit=columns,
    columns_and_rows_to_freeze='B2', 
    row_to_add_filters=0,
)
excel_writer.save()

- AsafSH

---

StyleFrame包可能很容易使用，但我不明白“默认情况下列宽度是自动调整”的意思。当我运行您提供的代码示例时，所有列的宽度都相同，并且所有三个标题都被换行。您的示例数据选择得也很差，因为它们本来就几乎相同宽度。要真正说明自动调整，您应该选择一些非常宽的数据和一些窄的数据。当我自己尝试时，列宽仍然与以前完全相同。根本没有任何调整。 - John Y

也许在StyleFrame的历史上，列宽度默认是自动调整的，但至少今天，您必须在“best_fit”参数中指定要调整的列。此外，当我尝试这样做时，我得到了非常糟糕的结果。 (https://github.com/pandas-dev/pandas/issues/4049#issuecomment-426829120) - John Y

宽度似乎偏移了1列。我尝试启用和禁用“index”参数，但没有成功。 - user10417531

对于那些寻找答案的人：如何为标题添加更多样式，例如：sf.apply_headers_style(Styler(bold=False))。我花了很长时间才弄清楚这一点。在导入语句中，from StyleFrame import StyleFrame, Styler。除了加粗之外，以下是所有选项：https://styleframe.readthedocs.io/en/2.0.5/api_documentation.html#styler-class - Nikhil VJ

从版本3开始，导入语句应该改为 from styleframe import StyleFrame，以符合PEP8的命名规范。 - DeepSpace

Answer 4

我发帖是因为我遇到了同样的问题，并发现Xlsxwriter和pandas的官方文档仍将此功能列为不支持。我想出了一个解决方法，解决了我遇到的问题。我基本上只是遍历每一列，并使用worksheet.set_column将列宽设置为该列内容的最大长度。

然而，需要注意的一点是，这个解决方案不适用于列标题，只适用于列值。如果您需要调整标题的大小，那应该很容易改变。希望这能帮助到某些人 :)

import pandas as pd
import sqlalchemy as sa
import urllib


read_server = 'serverName'
read_database = 'databaseName'

read_params = urllib.quote_plus("DRIVER={SQL Server};SERVER="+read_server+";DATABASE="+read_database+";TRUSTED_CONNECTION=Yes")
read_engine = sa.create_engine("mssql+pyodbc:///?odbc_connect=%s" % read_params)

#Output some SQL Server data into a dataframe
my_sql_query = """ SELECT * FROM dbo.my_table """
my_dataframe = pd.read_sql_query(my_sql_query,con=read_engine)

#Set destination directory to save excel.
xlsFilepath = r'H:\my_project' + "\\" + 'my_file_name.xlsx'
writer = pd.ExcelWriter(xlsFilepath, engine='xlsxwriter')

#Write excel to file using pandas to_excel
my_dataframe.to_excel(writer, startrow = 1, sheet_name='Sheet1', index=False)

#Indicate workbook and worksheet for formatting
workbook = writer.book
worksheet = writer.sheets['Sheet1']

#Iterate through each column and set the width == the max length in that column. A padding length of 2 is also added.
for i, col in enumerate(my_dataframe.columns):
    # find length of column i
    column_len = my_dataframe[col].astype(str).str.len().max()
    # Setting the length if the column header is larger
    # than the max column value length
    column_len = max(column_len, len(col)) + 2
    # set the column length
    worksheet.set_column(i, i, column_len)
writer.save()

- user6178746

---

好的解决方案。我喜欢你使用了Pandas而不是其他包。 - user6194984

我认为你需要在max函数里加上()：max(column_len(), len(col)) + 2。 - Serdia

Answer 5

现在可能没有自动化的方法可以做到这一点，但是使用openpyxl时，以下行（从另一个回答者Bufke在如何手动完成中适应而来）使您能够指定一个合理的值（按字符宽度）：

writer.sheets['Summary'].column_dimensions['A'].width = 15

- ojdo

---

自2013年以来，pandas使用的默认ExcelWriter引擎已更改为Xlsxwriter，该引擎不包含“column_dimensions”属性。如果您想继续使用openpyxl，请在创建写入器时使用“pd.ExcelWriter（excel_filename，engine ='openpyxl'）”进行指定。 - ojdo

@Sunil：检查使用“Xlsxwriter”作为引擎的其他答案，以了解如何使用今天的默认引擎指定列宽。 - ojdo

Answer 6

通过使用pandas和xlsxwriter，您可以完成您的任务，下面的代码将在Python 3.x中完美运行。有关使用XlsxWriter与pandas工作的更多细节，请参阅此链接 https://xlsxwriter.readthedocs.io/working_with_pandas.html

import pandas as pd
writer = pd.ExcelWriter(excel_file_path, engine='xlsxwriter')
df.to_excel(writer, sheet_name="Summary")
workbook = writer.book
worksheet = writer.sheets["Summary"]
#set the column width as per your requirement
worksheet.set_column('A:A', 25)
writer.save()

- Ashu007

Answer 7

我发现基于列标题来调整列宽比基于列内容更有用。

使用df.columns.values.tolist()生成列标题列表，并使用这些标题的长度来确定列宽。

参见下面的完整代码：

import pandas as pd
import xlsxwriter

writer = pd.ExcelWriter(filename, engine='xlsxwriter')
df.to_excel(writer, index=False, sheet_name=sheetname)

workbook = writer.book # Access the workbook
worksheet= writer.sheets[sheetname] # Access the Worksheet

header_list = df.columns.values.tolist() # Generate list of headers
for i in range(0, len(header_list)):
    worksheet.set_column(i, i, len(header_list[i])) # Set column widths based on len(header)

writer.save() # Save the excel file

- jack1536

Answer 8

综合其他回答和评论并支持多索引：

def autosize_excel_columns(worksheet, df):
  autosize_excel_columns_df(worksheet, df.index.to_frame())
  autosize_excel_columns_df(worksheet, df, offset=df.index.nlevels)

def autosize_excel_columns_df(worksheet, df, offset=0):
  for idx, col in enumerate(df):
    series = df[col]
    max_len = max((
      series.astype(str).map(len).max(),
      len(str(series.name))
    )) + 1
    worksheet.set_column(idx+offset, idx+offset, max_len)

sheetname=...
df.to_excel(writer, sheet_name=sheetname, freeze_panes=(df.columns.nlevels, df.index.nlevels))
worksheet = writer.sheets[sheetname]
autosize_excel_columns(worksheet, df)
writer.save()

- kgibm

---

这个对我来说直接就可以用了，非常感谢！ - bkaiser

Answer 9

在工作中，我经常将数据框写入Excel文件。因此，我创建了一个模块，以便不必一遍又一遍地编写相同的代码。现在，我只需导入它并使用它来编写和格式化Excel文件。然而，有一个缺点，如果数据框特别大，则需要很长时间。

以下是代码：

def result_to_excel(output_name, dataframes_list, sheet_names_list, output_dir):
    out_path = os.path.join(output_dir, output_name)
    writerReport = pd.ExcelWriter(out_path, engine='xlsxwriter',
                    datetime_format='yyyymmdd', date_format='yyyymmdd')
    workbook = writerReport.book
    # loop through the list of dataframes to save every dataframe into a new sheet in the excel file
    for i, dataframe in enumerate(dataframes_list):
        sheet_name = sheet_names_list[i]  # choose the sheet name from sheet_names_list
        dataframe.to_excel(writerReport, sheet_name=sheet_name, index=False, startrow=0)
        # Add a header format.
        format = workbook.add_format({
            'bold': True,
            'border': 1,
            'fg_color': '#0000FF',
            'font_color': 'white'})
        # Write the column headers with the defined format.
        worksheet = writerReport.sheets[sheet_name]
        for col_num, col_name in enumerate(dataframe.columns.values):
            worksheet.write(0, col_num, col_name, format)
        worksheet.autofilter(0, 0, 0, len(dataframe.columns) - 1)
        worksheet.freeze_panes(1, 0)
        # loop through the columns in the dataframe to get the width of the column
        for j, col in enumerate(dataframe.columns):
            max_width = max([len(str(s)) for s in dataframe[col].values] + [len(col) + 2])
            # define a max width to not get to wide column
            if max_width > 50:
                max_width = 50
            worksheet.set_column(j, j, max_width)
    writerReport.save()
    return output_dir + output_name

- rafat.ch

---

当我复制这段代码时，出现以下错误： AttributeError: 'str' object has no attribute 'to_excel'。我认为这与“dataframe_list”的创建方式有关。我的列表包含6个数据框名称。 - user3019973

是的，“dataframe_list” 应该包含数据帧而不是数据帧名称。 - rafat.ch

Answer 10

是的，有一些事情你可以在 xlsx 文件之后做来自动调整列宽。 使用 xlwings 来 autofit 列。这是一个非常简单的解决方案，参见示例代码的最后 6 行。这种方法的优点是你不必担心字体大小、字体类型或其他任何东西。 要求：需要安装 Excel。

import pandas as pd
import xlwings as xw

path = r"test.xlsx"

# Export your dataframe in question.
df = pd._testing.makeDataFrame()
df.to_excel(path)

# Autofit all columns with xlwings.
with xw.App(visible=False) as app:
    wb = xw.Book(path)

    for ws in wb.sheets:
        ws.autofit(axis="columns")

    wb.save(path)
    wb.close()

- mouwsy

---

仅适用于Windows和MacOS，不支持Linux。 - Guido