Czy istnieje sposób na automatyczne dostosowanie szerokości kolumn programu Excel za pomocą pandas.ExcelWriter?

Jestem proszony o wygenerowanie raportów w formacie Excel. Obecnie dość intensywnie używam pand do moich danych, więc naturalnie chciałbym użyć metody pandas.ExcelWriter do generowania tych raportów. Jednak problemem są stałe szerokości kolumn.

Dotychczasowy kod jest dość prosty. Powiedzmy, że mam ramkę danych o nazwie „df”:

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

Przeglądałem kod pandy i tak naprawdę nie widzę żadnych opcji ustawiania szerokości kolumn. Czy istnieje sztuczka we wszechświecie, która powoduje, że kolumny automatycznie dostosowują się do danych? A może jest coś, co mogę zrobić po fakcie w pliku xlsx, aby dostosować szerokości kolumn?

(Używam biblioteki OpenPyXL i generuję pliki .xlsx - jeśli to ma znaczenie).

Dziękuję Ci.

Zainspirowany odpowiedzią użytkownika6178746 , mam:

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

Publikuję to, ponieważ właśnie natknąłem się na ten sam problem i stwierdziłem, że oficjalna dokumentacja dla Xlsxwriter i pand nadal zawiera tę funkcję jako nieobsługiwaną. Zhakowałem rozwiązanie, które rozwiązało problem, który miałem. Po prostu przechodzę przez każdą kolumnę i używam pliku workheet.set_column do ustawienia szerokości kolumny == maksymalna długość zawartości tej kolumny.

Jedna ważna uwaga. To rozwiązanie nie pasuje do nagłówków kolumn, a jedynie do wartości kolumn. To powinna być łatwa zmiana, jeśli zamiast tego chcesz dopasować nagłówki. Mam nadzieję, że to komuś pomoże :)

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

Prawdopodobnie nie ma automatyczny sposób to zrobić teraz, ale jak używać openpyxl następującą linię (dostosowany innej odpowiedzi przez użytkownika Bufke na jak to zrobić ręcznie ) pozwala określić wartość sane (w szerokościach znaków):

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

Jest fajny pakiet, którego ostatnio zacząłem używać, nazwany StyleFrame.

pobiera DataFrame i pozwala bardzo łatwo nadać jej styl ...

domyślnie szerokość kolumn jest dostosowywana automatycznie.

na przykład:

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

możesz także zmienić szerokość kolumn:

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

AKTUALIZACJA 1

W wersji 1.4 best_fitdodano argument do StyleFrame.to_excel. Zobacz dokumentację .

AKTUALIZACJA 2

Oto przykład kodu, który działa dla StyleFrame 3.xx

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

Używając pand i xlsxwriter możesz wykonać swoje zadanie, poniższy kod doskonale zadziała w Pythonie 3.x. Aby uzyskać więcej informacji na temat pracy z XlsxWriter z pandami, ten link może być przydatny 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()

Dynamicznie dostosuj wszystkie długości kolumn

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)

Ręcznie dostosuj kolumnę, używając nazwy kolumny

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

Ręcznie dostosuj kolumnę za pomocą indeksu kolumny

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

W przypadku niepowodzenia któregokolwiek z powyższych

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

pamiętaj, aby zainstalować xlsxwriter:

pip install xlsxwriter

Okazało się, że bardziej przydatne jest dostosowanie kolumny na podstawie nagłówka kolumny niż zawartości kolumny.

Używając df.columns.values.tolist()I generuję listę nagłówków kolumn i używam długości tych nagłówków do określenia szerokości kolumn.

Zobacz pełny kod poniżej:

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

W pracy zawsze piszę ramki danych do plików Excela. Więc zamiast ciągle pisać ten sam kod, stworzyłem moduł. Teraz po prostu importuję go i używam do pisania i formatowania plików programu Excel. Jest jednak jedna wada, zajmuje to dużo czasu, jeśli ramka danych jest bardzo duża. Oto kod:

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

Łączenie innych odpowiedzi i komentarzy, a także obsługa wielu wskaźników:

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

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

Najłatwiejszym rozwiązaniem jest podanie szerokości kolumny w metodzie set_column.

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

def auto_width_columns(df, sheetname):
    workbook = writer.book  
    worksheet= writer.sheets[sheetname] 

    for i, col in enumerate(df.columns):
        column_len = max(df[col].astype(str).str.len().max(), len(col) + 2)
        worksheet.set_column(i, i, column_len)

Tak, jest coś, co możesz zrobić po fakcie w pliku xlsx, aby dostosować szerokość kolumn. Użyj xlwings do AutoFit kolumn. To całkiem proste rozwiązanie, zobacz sześć ostatnich wierszy przykładowego kodu. Zaletą tej procedury jest to, że nie musisz martwić się o rozmiar czcionki, typ czcionki ani nic innego. Wymagania: instalacja programu Excel.

import pandas as pd
import xlwings as xw

report_file = "test.xlsx"

df1 = pd.DataFrame([
    ('this is a long term1', 1, 1, 3),
    ('this is a long term2', 1, 2, 5),
    ('this is a long term3', 1, 1, 6),
    ('this is a long term2', 1, 1, 9),
    ], columns=['term', 'aaaa', 'bbbbbbb', "cccccccccccccccccccccccccccccccccccccccccccccc"])

writer = pd.ExcelWriter(report_file, engine="xlsxwriter")
df1.to_excel(writer, sheet_name="Sheet1", index=False)

workbook = writer.book
worksheet1 = writer.sheets["Sheet1"]
num_format = workbook.add_format({"num_format": '#,##0.00'})

worksheet1.set_column("B:D", cell_format=num_format)
writer.save()

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

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

wb.save(report_file)
app.quit()