Let's break down why your initial code wasn't working, then walk through two reliable solutions to get your desired column widths.

What Went Wrong with Your Code #

The key issue here is that fp.set_column() isn't a valid method for a pandas DataFrame. That method belongs to worksheet objects from Excel writing libraries like XlsxWriter or OpenPyXL, not directly to your DataFrame. You were trying to call a library-specific method on the wrong object, which is why it didn't have any effect.

Solution 1: Adjust Columns During Export with XlsxWriter #

This is the most efficient approach—we'll set column widths while writing the DataFrame to Excel, using XlsxWriter as the engine.

import pandas as pd
from tkinter import filedialog

# Your existing data loading code
filename = filedialog.askopenfilename(initialdir="/", title="Select file to convert")
fichierp = pd.read_excel(filename, index_col=1)
fp = fichierp.iloc[:, [0, 1, 2, 5, 8, 10, 13]]

# Define your output file path
output_path = "formatted_output.xlsx"

# Use ExcelWriter with XlsxWriter engine
with pd.ExcelWriter(output_path, engine="xlsxwriter") as writer:
    # Export the DataFrame to Excel
    fp.to_excel(writer, sheet_name="Data")
    
    # Get the worksheet object to modify columns
    worksheet = writer.sheets["Data"]
    
    # Calculate and set width for each data column
    for col_idx, column in enumerate(fp.columns):
        # Account for both the column name length and longest cell value
        max_content_length = fp[column].astype(str).map(len).max()
        column_width = max(max_content_length, len(column)) + 2  # +2 for padding
        # Set width for the current column (col_idx +1 because index takes column 0)
        worksheet.set_column(col_idx + 1, col_idx + 1, column_width)
    
    # Don't forget to adjust the index column width too
    index_max_length = fp.index.astype(str).map(len).max()
    index_width = max(index_max_length, len(fp.index.name)) + 2
    worksheet.set_column(0, 0, index_width)

Key Notes:

• We add +2 to the calculated width to give a little padding so content doesn't get cut off.
• Since you're using index_col=1 when reading the Excel file, the exported sheet will have an index column (column 0), so we explicitly adjust its width separately.

Solution 2: Adjust Columns After Export with OpenPyXL #

If you prefer to modify an existing Excel file (or already have the DataFrame exported), use OpenPyXL to load the file and tweak column widths post-export:

import pandas as pd
from tkinter import filedialog
from openpyxl import load_workbook

# Your data loading code (same as before)
filename = filedialog.askopenfilename(initialdir="/", title="Select file to convert")
fichierp = pd.read_excel(filename, index_col=1)
fp = fichierp.iloc[:, [0, 1, 2, 5, 8, 10, 13]]

# Export the DataFrame first
output_path = "unformatted_output.xlsx"
fp.to_excel(output_path, sheet_name="Data")

# Load the workbook and select the active sheet
wb = load_workbook(output_path)
ws = wb["Data"]

# Iterate over each column to calculate optimal width
for column in ws.columns:
    max_length = 0
    # Get the column letter (e.g., 'A', 'B') to reference dimensions
    col_letter = column[0].column_letter
    
    # Check each cell in the column to find the longest value
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            # Skip cells that might throw errors (e.g., empty cells)
            pass
    
    # Set the column width with padding
    ws.column_dimensions[col_letter].width = max_length + 1

# Save the formatted file
wb.save("formatted_output.xlsx")

Both methods will give you the clean, auto-adjusted columns you're looking for. The XlsxWriter approach is faster if you're exporting from scratch, while OpenPyXL is great for modifying existing files.

内容的提问来源于stack exchange，提问作者Johan KOUAME