Existe-t-il un moyen d'ajuster automatiquement la largeur des colonnes Excel avec pandas.ExcelWriter ?

Question

On me demande de générer des rapports Excel. J'utilise actuellement pandas de manière assez intensive pour mes données, et j'aimerais donc naturellement utiliser la méthode pandas.ExcelWriter pour générer ces rapports. Cependant, la largeur fixe des colonnes pose un problème.

Le code que j'ai jusqu'à présent est assez simple. Disons que j'ai un cadre de données appelé 'df' :

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

J'ai examiné le code de pandas, et je ne vois pas vraiment d'options pour définir la largeur des colonnes. Existe-t-il un moyen de faire en sorte que les colonnes s'adaptent automatiquement aux données ? Ou y a-t-il quelque chose que je puisse faire après coup dans le fichier xlsx pour ajuster la largeur des colonnes ?

(J'utilise la bibliothèque OpenPyXL, et je génère des fichiers .xlsx - si cela fait une différence).

Merci.

Answer 1

En utilisant pandas et xlsxwriter vous pouvez effectuer votre tâche, le code ci-dessous fonctionnera parfaitement en Python 3.x. Pour plus de détails sur le travail avec XlsxWriter avec pandas ce lien peut être utile 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()

Answer 2

J'ai trouvé qu'il était plus utile d'ajuster la colonne avec en fonction de l'en-tête de la colonne plutôt que du contenu de la colonne.

Utilisation de df.columns.values.tolist() Je génère une liste d'en-têtes de colonnes et j'utilise les longueurs de ces en-têtes pour déterminer la largeur des colonnes.

Voir le code complet ci-dessous :

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

Answer 3

Au travail, j'écris toujours les cadres de données dans des fichiers Excel. Donc, au lieu d'écrire le même code encore et encore, j'ai créé un module. Maintenant, il me suffit de l'importer et de l'utiliser pour écrire et formater les fichiers Excel. Il y a cependant un inconvénient, cela prend beaucoup de temps si le dataframe est très grand. Voici donc le code :

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

Answer 4

Combiner les autres réponses et commentaires et soutenir également les 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()

Answer 5

Vous pouvez résoudre le problème en appelant la fonction suivante, où df est le cadre de données dont vous voulez obtenir les tailles et sheetname est la feuille dans excel où vous voulez que les modifications aient lieu

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)