如何用Python更新带格式的多工作表Excel文件并保留原有格式？

Absolutely! The key here is to use libraries that interact directly with the Excel file's structure and formatting, rather than treating it as a raw data source like pandas does (which is why you lose formatting when writing back). Here are two reliable approaches:

Openpyxl is a great choice if you don't have Excel installed locally and only need to handle .xlsx/.xlsm files. It lets you modify specific cells without touching existing formatting or other worksheets.

步骤： #

1. Install the library first:

   pip install openpyxl
   

2. Open the existing workbook with data_only=False (this preserves formulas and formatting instead of just reading computed values):
3. Target your specific worksheet and column, then update cell values one by one (or in bulk where possible).
4. Save the workbook back to the file.

代码示例： #

Suppose you want to update column B in "Sheet1" (starting from row 2, skipping the header) with new values stored in a list new_values:

from openpyxl import load_workbook

# Load the workbook, keep formatting and formulas
wb = load_workbook("your_file.xlsx", data_only=False)
ws = wb["Sheet1"]  # Target the specific worksheet

# Assume new_values has the same length as the number of rows you need to update
for row_num, value in enumerate(new_values, start=2):  # Start at row 2 (header is row 1)
    ws[f"B{row_num}"].value = value

# Save the changes - this preserves all other sheets and formatting
wb.save("your_file_updated.xlsx")

Note: Openpyxl doesn't support the older .xls format. If you're working with .xls, convert it to .xlsx first or use the xlwings approach below.

If your Excel file has complex formatting, macros, or you need full compatibility with Excel's features, xlwings is the way to go. It interacts directly with the Excel application, so it preserves everything about your original file.

步骤： #

1. Install the library:

   pip install xlwings
   

2. Connect to your existing workbook (this will launch Excel in the background if it's not open).
3. Select the target worksheet and column, then assign your new values.
4. Save and close the workbook.

代码示例： #

Updating column B in "Sheet1" from row 2 to row 10 with new values:

import xlwings as xw

# Connect to the workbook - use mode='r' if you just want to read, but we need 'w' for writing
with xw.Book("your_file.xlsx") as wb:
    ws = wb.sheets["Sheet1"]
    # Assign new values to the range (e.g., B2:B10)
    ws.range("B2:B10").value = [[val] for val in new_values]  # xlwings expects 2D lists for columns

    # Save the changes (the 'with' block will close the workbook automatically)
    wb.save("your_file_updated.xlsx")

Note: You need Excel installed on your machine for xlwings to work. This is perfect for files with conditional formatting, charts, macros, or any other advanced Excel features.

通用注意事项： #

• Always make a backup of your original Excel file before making changes, just in case something goes wrong.
• If you're using pandas alongside these libraries (e.g., to compute the new values), you can generate new_values from your DataFrame and then use the above methods to write them back without touching the rest of the file.

内容的提问来源于stack exchange，提问作者Mustard Tiger