Python 如何将信息从一个excel复制到模板excel

如果您只想更新文本并保持模板中的格式、 colored颜色 等不变，则需要使用Openpyxl.更新了下面的代码.请注意

• 我没有使用您的df2代码，因为模板文件已经有了新的头文件.仅将每个工作表中的数据更新到文件中
• 您可以使用READ_EXCEL读取每个工作表，但需要使用Openpyxl.Load_Workbook进行写入，最后在读取所有工作表后保存文件
• 在for循环之前使用LOAD_WORKBOOK打开上图中所示的模板文件，并在for循环完成后保存到新文件template2

spreadsheet_file = pd.ExcelFile('example.xlsx')
worksheets = spreadsheet_file.sheet_names
#cat_dic = {"Part Number":"CÓDIGO", "QTY":"QT", "Description":"DESCRIÇÃO", "Material":"MATERIAL", "Company":"MARCA","Category":"OPERAÇÃO"}
#d = {}

import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
wb=openpyxl.load_workbook('Template.xlsx')  ##Your Template file
ws=wb['Sheet1']
rownumber=2 ##Skip 2 rows and start writing from row 3 - first two are headers in template file

for sheet_name in worksheets:
    df = pd.read_excel(spreadsheet_file, sheet_name)
    #Getting only the columns asked: "Part Number","QTY","Description","Material","Company","Category"
    df = df[["Part Number","QTY","Description","Material","Company","Category"]]
    #Organizing info:
    #1º By Category
    #2º By Description
    df = df.sort_values(['Category', 'Description'], ascending = [False, False])

    rows = dataframe_to_rows(df, index=False, header=False) ## Read all rows from df, but don't read index or header
    for r_idx, row in enumerate(rows, 1):
        for c_idx, value in enumerate(row, 1):
             ws.cell(row=r_idx+rownumber, column=c_idx, value=value) Write to cell, but after rownumber + row index
            
    rownumber += len(df) ##Move the rownumber to end, so next worksheet data comes after this sheet's data 

wb.save('template2.xlsx')