How do I add information to an excel sheet without deleting the rest in Python?

Question

Problem

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

Context:

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.

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.

Pseudocode

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)

My code so far

Some is out commented to focus on the problem

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()

The result

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.

refer this quesion https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas — Nuwan, Aug 30 '18 at 10:56
I have tried looking at the answer there. And it has inspired a lot of my code. But I cannot get it to work with more than one sheet. Old sheets are deleted when I use the approach from https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas — NotMyRealName, Aug 30 '18 at 12:24

NotMyRealName · Accepted Answer · 2018-08-31T20:00:54.390

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

How do I add information to an excel sheet without deleting the rest in Python?

1 Answers1