如何在不删除其余内容的情况下将信息添加到Excel工作表中? [英] How do I add information to an excel sheet without deleting the rest in Python?

问题描述

问题

我需要制作一个python脚本，可以将信息导出到excel工作表，而无需删除工作表中的旧信息或工作表.参见伪代码

I need to make a python script that can export information to excel sheet without deleting sheets old information or sheets. See Pseudocode

上下文:

此功能将与卷筒纸刮板一起使用.并且希望应该能够将数据扩展到现有工作表，创建新工作表和创建新文件.

This function is to be used in conjunction with a web scraper. And should hopefully, be able to extend data to an existing sheet, create a new sheet, and create a new file.

在其他问题的帮助下，我非常接近:如何保存新表在现有的excel文件中，使用Pandas?但是我希望可以扩展现有的工作表.

With the help of other questions, I have come really close: How to save a new sheet in an existing excel file, using Pandas? But I want the possibility to extend on existing sheets.

伪代码

def OutputDataToExcel(Filename,Sheetname,df): #df is a pandas dataframe
    If Filename and Sheetname exists:
        extend the Sheetname with df (no header)
    elif Filename exists:
        create new sheet with Sheetname
        add df (with header)
    else:
    create new excelfile with Filename
    create new sheet with Sheetname
    add df (with header)

到目前为止我的代码

有些人被评论以专注于问题

import openpyxl
import pandas as pd

def outputdata(Filename,Sheetname,df):
    #try:
    #will error if file or sheet cannot be found
    #And will extend information to the sheet
    xl1 = pd.read_excel(Filename,Sheetname)
    xl2 = pd.ExcelFile(Filename)
    wb=openpyxl.load_workbook(Filename)
    length = len(xl2.parse(Sheetname))
    writer = pd.ExcelWriter(Filename,sheet_name = Sheetname , engine = "openpyxl")
    df.to_excel(writer,Sheetname, startrow=length+1, index=False, header = None)
    writer.book = wb

    writer.save()
    #writer.close()
    #except:
        #try:
            #will error if file cannot be found
            #and will create a new sheet with information
            #wb=openpyxl.load_workbook(Filename)
            #wb.openpyxl.create_sheet(Sheetname)
            #df.to_excel(writer,Sheetname, index=False)
            #writer.save()
            #writer.close()
        #except:
            #writer = pd.ExcelWriter(Filename,sheet_name = Sheetname  , engine = "openpyxl")
            #df.to_excel(writer,Sheetname, index= False)
            #writer.save()
            #writer.close()

结果

将创建一个名为Sheetname1的新工作表，并将df添加到该工作表中，然后将其添加到Sheetname中.如果我再次运行它，则会在此处添加一个名为Sheetname2和df的新工作表...

A new sheet is created with the name Sheetname1 and df is added to this sheet, insted of it being added to Sheetname. If I run it again a new sheet named Sheetname2 and df is then added here...

我希望有人能帮助我，这是我的第一个问题，请在出现问题的方法上给予帮助或批评.

I hope someone can help me, it's my first question so, please respond both with help or critic on how to present the problem.

推荐答案

我终于明白了！如果有人有兴趣，请使用以下代码:

I finally got it!! If anyone has any interest, here is the code:

import xlsxwriter
import pandas as pd

def outputdata(Filename,Sheetname,df):
    try:
        xlsx_file = pd.ExcelFile(Filename)
    except:
        xlsxwriter.Workbook(Filename)
        xlsx_file = pd.ExcelFile(Filename)

    writer = pd.ExcelWriter(Filename, engine='openpyxl')
    IsSheetThereAlready = False
    for sheet in xlsx_file.sheet_names:
        if sheet == Sheetname:
            df2 = xlsx_file.parse(sheet)
            df2.to_excel(writer,sheet_name= sheet, index=False)
            df.to_excel(writer,sheet_name= sheet, startrow=len(df2)+1, index=False, header=None)
            IsSheetThereAlready = True

        else:
            df2 = xlsx_file.parse(sheet)
            df2.to_excel(writer,sheet_name= sheet, index=False)


    if IsSheetThereAlready is False:
        df.to_excel(writer,sheet_name = Sheetname, index=False)

    writer.save()

    return

这篇关于如何在不删除其余内容的情况下将信息添加到Excel工作表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！