如何用Python读取带格式XLSX、修改指定行并保留格式保存

要实现保留原有XLSX文件的表头格式（黄色背景）和列宽，同时仅修改第4行及以后的数据，需要结合pandas处理数据、openpyxl操作Excel格式。具体实现步骤如下：

1. 安装依赖库 #

确保已安装所需工具库：

pip install pandas openpyxl

2. 核心实现逻辑 #

• 用openpyxl加载原Excel文件，完整保留所有格式信息（表头背景色、列宽、字体样式等）。
• 用pandas处理XML提取的数据生成DataFrame，确保数据列与原Excel列对应。
• 将DataFrame数据写入原工作簿的第4行起始位置，不改动表头区域。
• 保存修改后的工作簿，确保原有格式不受破坏。

3. 修改后的完整代码 #

import os
import xml.etree.ElementTree as ET
import pandas as pd
from openpyxl import load_workbook

def get_data_from_xml(path):
    tree = ET.parse(path)
    root = tree.getroot()
    
    # 初始化变量，避免XML节点缺失导致未定义错误
    PN = None
    DS = None
    GH = None
    PP = None
    PI = None
    
    for Type in root.iter('Type'):
        PN = Type.get('name')
        DS = Type.get('Desc')
        
    for Component in root.iter('Component'):
        CName = Component.get('name')
        if CName == 'Pos010_GearHousing':
             for ComponentNo in Component.iter('ComponentNo'):
                GH = ComponentNo.get('name')                   

        if CName == 'Pos058_PowerPack':
             for ComponentNo in Component.iter('ComponentNo'):
                PP = ComponentNo.get('name')
                
        if CName == 'Pos082_PedalInterface':
             for ComponentNo in Component.iter('ComponentNo'):
                PI = ComponentNo.get('name')
                
    return PN, GH, PP, DS, PI

# 从XML批量提取数据生成DataFrame
pn_list = []
gh_list = []
pp_list = []
ds_list = []
pi_list = []

xml_path = 'C:/Users/STJ2TW/Desktop/Files XML/'
for filename in os.listdir(xml_path):
    if '.' not in filename:
        full_xml_path = os.path.join(xml_path, filename)
        PN, GH, PP, DS, PI = get_data_from_xml(full_xml_path)
        pn_list.append(PN)
        gh_list.append(GH)
        pp_list.append(PP)
        ds_list.append(DS)
        pi_list.append(PI)

df = pd.DataFrame(
    {'Description': ds_list,
     'Part number': pn_list,
     'Gear Housing': gh_list,
     'Power Pack': pp_list,
     'Pedal Interface': pi_list,
    }) 

# 取消索引设置，确保DataFrame包含所有列，与原Excel列对应
df = df.reset_index(drop=True)

# 处理原Excel文件，保留格式
original_xlsx = "C:/Users/STJ2TW/Desktop/你的原Excel文件路径.xlsx"
# 加载工作簿，默认保留所有格式
wb = load_workbook(original_xlsx)
ws = wb.active

# 从第4行开始写入数据（Excel行号从1开始）
start_row = 4
for row_idx, row_data in df.iterrows():
    # 需确保DataFrame列顺序与原Excel列顺序一致，若不一致调整column参数即可
    ws.cell(row=start_row + row_idx, column=1, value=row_data['Description'])
    ws.cell(row=start_row + row_idx, column=2, value=row_data['Part number'])
    ws.cell(row=start_row + row_idx, column=3, value=row_data['Gear Housing'])
    ws.cell(row=start_row + row_idx, column=4, value=row_data['Power Pack'])
    ws.cell(row=start_row + row_idx, column=5, value=row_data['Pedal Interface'])

# 保存修改后的文件（建议先存为新文件测试，确认无误后再覆盖原文件）
wb.save("C:/Users/STJ2TW/Desktop/修改后的文件.xlsx")

4. 关键注意事项 #

• 变量初始化：在XML解析函数中添加变量初始值，避免因部分XML缺失节点导致的未定义变量错误。
• 列对应校验：必须确保DataFrame的列顺序与原Excel的列顺序完全匹配，否则数据会写入错误列。
• 格式保留机制：openpyxl.load_workbook默认会完整保留原文件的格式（表头背景色、列宽等），因为我们直接操作原工作簿的单元格，未重新创建工作表。
• 安全保存：建议先将修改后的文件保存为新文件，验证数据和格式无误后，再覆盖原文件，防止数据丢失。

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