CODEWITHSUNDEEP
pythonexcelpandasopenpyxl
Arundeep Chohan
Arundeep Chohan

Reputation: 9969

Writing to Excel file in Python

import pandas as pd
import openpyxl

filename="Tests.xlsx"

def createWorkBook():
    wrkbk = openpyxl.Workbook()
    ws = wrkbk.active
    Sheets=["Rostered Patient","Non-Rostered Patient","Email","Error Handling"]
    for sheet in Sheets:
        if not sheet in wrkbk.sheetnames:
            print("Created sheet: "+ sheet)
            wrkbk.create_sheet(sheet)
    wrkbk.save(filename)

def main():
    Columns=[[""],["ID","Testing Procedure:","Pass/Fail","Issue#","Date"],[""],[""],[""]]

    createWorkBook()
xl_writer = pd.ExcelWriter(filename, engine='openpyxl')
    df = pd.DataFrame([["1","2","3","4","5",'6','7','8']],columns = Columns[1])
    df.to_excel(xl_writer, 'Rostered Patient', index=False, startcol=1, startrow=1)

if __name__ == "__main__":
    main()

I'm just trying to append a dataframe to an Excel file to the Rostered Patient tab. I want to append the headers and 8 id's everytime I run the code. My issue is using df_to_excel to run it with the startrow and startcol.

enter image description here

Upvotes: 0

Views: 2173

Answers (1)

Laurent
Laurent

Reputation: 13458

You could try to refactor your code like this:

from pathlib import Path

import openpyxl
import pandas as pd

filename="Tests.xlsx"

...

def main():
    Columns = [
        [""],
        ["ID", "Testing Procedure:", "Pass/Fail", "Issue#", "Date"],
        [""],
        [""],
        [""],
    ]
    # Check if the WorkBook exists and get its length
    if not Path(filename):
        createWorkBook()
        previous_df_length = 0
    else:
        previous_df = pd.read_excel(io=filename, sheet_name="Rostered Patient")
        previous_df_length = previous_df.shape[0] + 2

    # As recommended in pandas documentation, use ExcelWriter in a context manager
    with pd.ExcelWriter(filename, engine="openpyxl", mode="a") as xl_writer:
        df = pd.DataFrame(
            [["1", "2", "3", "4", "5", "6", "7", "8"]], columns=Columns[1]
        )
        df.to_excel(
            xl_writer,
            "Rostered Patient",
            index=False,
            startrow=previous_df_length,
            startcol=1,
        )

Upvotes: 1

Related Questions