python - 如何将信息从一个 excel 复制到模板 excel？

Question

我正在使用 Excel，我必须将一些列导出到另一个列，但第二个列是一个具有一些颜色、公司 Logo 和其他内容的模板。有什么方法可以保留 template.xlsx 的外观和功能吗？

我的代码:

import pandas as pd

#variables for source file, worksheets, and empty dictionary for dataframes
spreadsheet_file = pd.ExcelFile('example.xlsx')
worksheets = spreadsheet_file.sheet_names

appended_data = {}
cat_dic = {"Part Number":"CÓDIGO", "QTY":"QT", "Description":"DESCRIÇÃO", "Material":"MATERIAL", "Company":"MARCA","Category":"OPERAÇÃO"}
d = {}

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])
  appended_data = df.to_dict()
  #Change Key names
  d = dict((cat_dic[key], value) for (key, value) in appended_data.items())
  #Exporting Data
  df2 = pd.DataFrame(d)
  df2.to_excel('template2.xlsx',sheet_name='Projeto',index=False)

示例:

模板:

我的输出:

在此先感谢您的帮助。

Answer 1

如果您只想更新文本并保持模板中的格式、颜色等不变，则需要使用 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')