I am creating a spreadsheet with openpyxl and adding some data.

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import load_workbook

from collections import OrderedDict
workbook = Workbook()
sheet = workbook.active


def fill_static_values():
    sheet["A1"] = "Run No."
    sheet["A2"] = "MLIDMLPA"
    sheet["A48"] = "Patients here"
    
    sheet["B1"] = "Patient"

fill_static_values()

output = "./Name_of_run.xlsx"

workbook.save(filename=output)

Then my application do some data management and I want to add some of this data into the existing file.

book = load_workbook(output)
writer = pd.ExcelWriter(output, engine='openpyxl') 
writer.book = book

## ExcelWriter for some reason uses writer.sheets to access the sheet.
## If you leave it empty it will not know that sheet Main is already there
## and will create a new sheet.

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_no_control.to_excel(writer, "sheet", startrow=2, startcol=3,
                        header=False,
                        index=False)

writer.save()

Solution found on this StackOverflow link

However, this is creating and adding the data in the correct position but in a new sheet called sheet2. What I am doing wrong?

CodePudding user response：

The to_excel has incorrect sheet name. The S should be in CAPS. Change the line from data_no_control.to_excel(writer, "sheet", startrow=2, startcol=3, to data_no_control.to_excel(writer, "Sheet", startrow=2, startcol=3,

As there is already a sheet in the excel, it is writing the data to Sheet2

EDIT Noticed that you are using writer.sheets. If you want to use want the program pick up the first sheet from excel automatically, you can use this as well...

data_no_control.to_excel(writer, sheet_name=list(writer.sheets.keys())[0], startrow=2, startcol=3,

This will pick up the first sheet (in your case the only sheet) as the worksheet to update