首页 文章

将pandas df写入Excel并将其保存为副本

提问于
浏览
0

我有一个pandas数据框,我想打开一个包含公式的现有excel工作簿,将数据框复制到一组特定的列中(比如从A列到H列)并将其保存为具有不同名称的新文件 .

我们的想法是更新现有模板,使用指定的一组列中的数据框填充它,然后使用不同的名称保存Excel文件的副本 .

任何的想法?

我有的是:

import pandas
  from openpyxl import load_workbook

 book = load_workbook('Template.xlsx')
 writer = pandas.ExcelWriter('Template.xlsx', engine='openpyxl') 
 writer.book = book
 writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

 df.to_excel(writer)

 writer.save()

2 回答

  • 0

    假设您很乐意复制到A列中,以下内容应该有效 . 我没有看到从不同列开始写入工作表的方法(不覆盖任何内容) .

    下面结合@ MaxU的建议,在写入之前复制模板表(刚刚在我自己的模板工作簿中丢失了几个小时的工作到pd.to_excel)

    import pandas as pd
    from openpyxl.utils.dataframe import dataframe_to_rows
    from shutil import copyfile
    
    template_file = 'Template.xlsx' # Has a header in row 1 already
    output_file = 'Result.xlsx' # What we are saving the template as
    
    # Copy Template.xlsx as Result.xlsx
    copyfile(template_file, output_file)
    
    # Read in the data to be pasted into the termplate
    df = pd.read_csv('my_data.csv') 
    
    # Load the workbook and access the sheet we'll paste into
    wb = load_workbook(output_file)
    ws = wb.get_sheet_by_name('Existing Result Sheet') 
    
    # Selecting a cell in the header row before writing makes append()
    #  start writing to the following line i.e. row 2
    ws['A1']
    # Write each row of the DataFrame
    # In this case, I don't want to write the index (useless) or the header (already in the template)
    for r in dataframe_to_rows(df, index=False, header=False):
        ws.append(r)
    
    wb.save(output_file)
    
  • 1

    试试这个:

    df.to_excel(writer, startrow=10, startcol=1, index=False, engine='openpyxl')
    

    请注意 startrowstartcol 参数

相关问题