Python 有没有办法使用 pandas.ExcelWriter 自动调整 Excel 列宽？

Is there a way to auto-adjust Excel column widths with pandas.ExcelWriter?

提问by badideas

I am being asked to generate some Excel reports. I am currently using pandas quite heavily for my data, so naturally I would like to use the pandas.ExcelWriter method to generate these reports. However the fixed column widths are a problem.

我被要求生成一些 Excel 报告。我目前正在大量使用 Pandas 来处理我的数据，所以我自然想使用 pandas.ExcelWriter 方法来生成这些报告。然而，固定的列宽是一个问题。

The code I have so far is simple enough. Say I have a dataframe called 'df':

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

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

I was looking over the pandas code, and I don't really see any options to set column widths. Is there a trick out there in the universe to make it such that the columns auto-adjust to the data? Or is there something I can do after the fact to the xlsx file to adjust the column widths?

我正在查看 Pandas 代码，但我真的没有看到任何设置列宽的选项。宇宙中有没有什么技巧可以使列自动调整到数据？或者我可以在 xlsx 文件之后做些什么来调整列宽？

(I am using the OpenPyXL library, and generating .xlsx files - if that makes any difference.)

（我正在使用 OpenPyXL 库，并生成 .xlsx 文件 - 如果这有什么不同的话。）

Thank you.

谢谢你。

回答by ojdo

There is probably no automatic way to do it right now, but as you use openpyxl, the following line (adapted from another answer by user Bufkeon how to do in manually) allows you to specify a sane value (in character widths):

现在可能没有自动执行此操作的方法，但是当您使用 openpyxl 时，以下行（改编自用户Bufke关于如何手动执行的另一个答案）允许您指定一个合理的值（以字符宽度为单位）：

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

回答by AsafSH

There is a nice package that I started to use recently called StyleFrame.

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

it gets DataFrame and lets you to style it very easily...

它获取 DataFrame 并让您可以非常轻松地对其进行样式设置...

by default the columns width is auto-adjusting.

默认情况下，列宽是自动调整的。

for example:

例如：

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()

you can also change the columns width:

您还可以更改列宽：

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

UPDATE

更新

In version 1.4 best_fitargument was added to StyleFrame.to_excel. See the documentation.

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

回答by AsafSH

I'm posting this because I just ran into the same issue and found that the official documentation for Xlsxwriter and pandas still have this functionality listed as unsupported. I hacked together a solution that solved the issue i was having. I basically just iterate through each column and use worksheet.set_column to set the column width == the max length of the contents of that column.

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

One important note, however. This solution does not fit the column headers, simply the column values. That should be an easy change though if you need to fit the headers instead. Hope this helps someone :)

然而，一个重要的注意事项。此解决方案不适合列标题，仅适合列值。如果您需要改用标题，那应该是一个简单的更改。希望这对某人有所帮助:)

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()

回答by alichaudry

Inspired by user6178746's answer, I have the following:

受到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()

回答by Ashish Jith

Easiest solution is to specify width of column in set_column method.

最简单的解决方案是在 set_column 方法中指定列的宽度。

    for worksheet in writer.sheets.values():
        worksheet.set_column(0,last_column_value, required_width_constant)

回答by kgibm

Combining the other answers and comments and also supporting multi-indices:

结合其他答案和评论，也支持多指标：

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()

回答by Ssubrat Rrudra

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

回答by Ashu007

By using pandas and xlsxwriter you can do your task, below code will perfectly work in Python 3.x. For more details on working with XlsxWriter with pandas this link might be useful https://xlsxwriter.readthedocs.io/working_with_pandas.html

通过使用 pandas 和 xlsxwriter 你可以完成你的任务，下面的代码将在 Python 3.x 中完美运行。有关使用带有熊猫的 XlsxWriter 的更多详细信息，此链接可能有用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()

回答by Hyman1536

I found that it was more useful to adjust the column with based on the column header rather than column content.

我发现根据列标题而不是列内容调整列更有用。

Using df.columns.values.tolist()I generate a list of the column headers and use the lengths of these headers to determine the width of the columns.

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

See full code below:

请参阅下面的完整代码：

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

回答by rafat.ch

At work, I am always writing the dataframes to excel files. So instead of writing the same code over and over, I have created a modulus. Now I just import it and use it to write and formate the excel files. There is one downside though, it takes a long time if the dataframe is extra large. So here is the code:

在工作中，我总是将数据帧写入 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()
    writerReport.close()
    return output_dir + output_name