Python(openpyxl):将数据从一个Excel文件放到另一个(模板文件)&在保留模板的同时使用其他名称保存它 [英] Python (openpyxl) : Put data from one excel file to another (template file) & save it with another name while retaining the template

问题描述

我有一个名为template.xlsx的 模板 excel文件，其中包含许多工作表.我想将数据从单独的.csv文件复制到template.xlsx的第一张纸(命名为 data )中，然后将新文件另存为 result.xlsx ，同时保留原始模板文件.

I have a template excel file named as template.xlsx which has a number of sheets. I would like to copy data from a seperate .csv file into the first sheet of template.xlsx (named as data) and save the new file as result.xlsx while retaining the original template file.

我要从template.xlsx

这是我到目前为止开发的代码

This is the code I have developed so far

import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
import openpyxl
from shutil import copyfile

template_file = 'template.xlsx' # Has a header in row 1 already which needs to be skipped while pasting data but it should be there in the output file
output_file = 'result.xlsx' 

copyfile(template_file, output_file)
df = pd.read_csv('input_file.csv') #The file which is to be pasted in the template

wb = openpyxl.load_workbook(output_file)
ws = wb.get_sheet_by_name('data') #Getting the sheet named as 'data'

for r in dataframe_to_rows(df, index=False, header=False):
   ws.append(r)

 wb.save(output_file)

我无法获得所需的输出

左侧是模板文件(带有额外的行)，右侧是输入文件(要复制到模板的数据)

The template file (with an extra row) on the left and the input file (data to be copied to the template) on the right, look like this

推荐答案

实际上并不需要使用shutil模块，因为您可以只使用openpyxl.load_workbook加载模板，然后以其他名称保存.

There's not really any need to use the shutil module, as you could just use openpyxl.load_workbook to load your template and then save by a different name.

另外，您在for循环内的ws.append(r)将从模板template.xlsx追加到现有数据中，听起来您只想保留标题.

Additionally, ws.append(r) inside you're for loop will append to the existing data taken form template.xlsx, and it sounds like you only want to keep the header.

我在下面提供了一个完全可复制的示例，该示例出于演示目的而创建了"template.xlsx".然后加载'template.xlsx'，向其中添加新数据并另存为result.xlsx.

I've provided a fully reproducible example below that creates 'template.xlsx' for demonstration purposes. Then it loads 'template.xlsx' adds new data to it and saves as result.xlsx.

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import PieChart, Reference, Series
import pandas as pd

template_file = 'template.xlsx'
output_file = 'result.xlsx'

#This part creates a workbook called template.xlsx with a sheet called 'data' and sheet called 'second_sheet'
writer = pd.ExcelWriter('template.xlsx', engine='openpyxl') 
wb  = writer.book
df = pd.DataFrame({'Pie': ["Cream", "Cherry", "Banoffee", "Apple"],
                  'Sold': [2, 2, 1, 4]})

df.to_excel(writer, index=False, sheet_name='data', startrow=1)
ws = writer.sheets['data']
ws['A1'] = 1
ws['B1'] = 2

ch = PieChart()
labels = Reference(ws, min_col=1, min_row=3, max_row=6)
data = Reference(ws, min_col=2, min_row=3, max_row=6)
ch.series = (Series(data),)
ch.title = "Pies sold"
ws.add_chart(ch, "D2")

ws = wb.create_sheet("Second_sheet")
ws['A1'] = 'This Sheet will not be overwitten'

wb.save(template_file)

#Now we load workbook called template.xlsx modify the 'data' sheet and save under a new name
#template.xlsx has not been modified

df_new = pd.DataFrame({'different_name': ["Blueberry", "Pumpkin", "Mushroom", "Turnip"],
                  'different_numbers': [4, 6, 2, 1]})

wb = load_workbook(template_file)

ws = wb.get_sheet_by_name('data') #Getting the sheet named as 'data'

rows = dataframe_to_rows(df_new, index=False, header=False)

for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
         ws.cell(row=r_idx+2, column=c_idx, value=value)

wb.save(output_file)

预期输出:

这篇关于Python(openpyxl):将数据从一个Excel文件放到另一个(模板文件)&在保留模板的同时使用其他名称保存它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！