如何将Pandas DataFrame保存至Excel并格式化？代码未生效如何处理？

Got it, let's fix this formatting issue once and for all. The key problem here is that Pandas' basic df.to_excel() call doesn't handle custom cell formatting on its own—you need to use an Excel writer engine (like XlsxWriter) to directly access the worksheet and apply your style rules. Here's a complete, working example that will give you the blue background for column B plus any other formatting you want:

Step 1: Import Required Libraries #

import pandas as pd
import xlsxwriter

Step 2: Prepare Your DataFrame #

Use your existing DataFrame, or create a sample one for testing:

# Replace this with your actual DataFrame
data = {
    "A": [1, 2, 3, 4],
    "B": ["Apple", "Banana", "Cherry", "Date"],
    "C": [10.5, 20.7, 30.2, 40.9]
}
df = pd.DataFrame(data)

Step 3: Initialize ExcelWriter with XlsxWriter Engine #

This lets you tap into advanced formatting features:

# Create a writer object pointing to your output file
writer = pd.ExcelWriter("formatted_excel.xlsx", engine="xlsxwriter")

# Write the DataFrame to a worksheet (turn off index if you don't need it)
df.to_excel(writer, sheet_name="Sheet1", index=False)

# Get references to the workbook and worksheet objects
workbook = writer.book
worksheet = writer.sheets["Sheet1"]

Step 4: Define Your Custom Format #

Here's where you set the blue background and any other styles you mentioned in format_bc:

# Custom format: blue background, white text, bold, border, centered alignment
format_bc = workbook.add_format({
    "bg_color": "#4A90E2",  # Adjust the hex code to your preferred blue
    "font_color": "#FFFFFF",
    "bold": True,
    "border": 1,  # Thin cell border
    "align": "center",
    "valign": "vcenter"
})

Step 5: Apply the Format to Column B #

You can apply it to the entire column (including the header) or just the data rows:

# Option 1: Apply format to the entire column B (header + data)
worksheet.set_column("B:B", None, format_bc)

# Option 2: Apply only to data rows (exclude header)
# worksheet.conditional_format(f"B2:B{len(df)+1}", {"type": "no_blanks", "format": format_bc})

Step 6: Save the File #

# Close the writer to finalize the Excel file
writer.close()

Why Your Original Code Didn't Work #

Chances are you tried to apply formatting without accessing the underlying worksheet object. Pandas doesn't automatically pass custom styles through the basic to_excel() method—you need to use the writer to modify the worksheet after writing the DataFrame.

If you prefer using OpenPyXL instead of XlsxWriter, the process is similar: use engine="openpyxl", load the worksheet, create a style with openpyxl.styles, then iterate over the cells in column B to apply the style.

内容的提问来源于stack exchange，提问作者Mo Azim