CODEWITHSUNDEEP
pythonpython-3.xopenpyxl
Sanjoy
Sanjoy

Reputation: 336

Write to an existing excel file using Openpyxl starting in existing sheet starting at a specific column and row

I have been searching this question to write in an existing excel sheet starting from specific row and column however methods like dataframe_to_rows is not writing from a specific position in a cell. I am now using a custom loop to write this however was wondering if there is a better approach. The loops works like this

import pandas as pd
import numpy as np
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
df = pd.DataFrame(np.random.randn(20, 4), columns=list('ABCD'))
file = "C:\\somepath\\some_existing_file.xlsx"
wb = load_workbook(filename=file, read_only=False)
ws = wb['some_existing_sheet']

##Fill up the row and column needed
stcol = 5
strow = 5

## Writing the column header
for c in range(0,len(df.columns)):
    ws[get_column_letter(c+stcol)+str(strow)].value = df.columns[c]
## Writing the data
for r in range(0,len(df)):
    for c in range(0,len(df.columns)):
       ws[get_column_letter(c+stcol)+str(strow+r+1)].value = df.iloc[r][c]
wb.save(file)

Please let me know if there is a better way to write to specefic position in a cell. By any chance if this turns out to be duplicate question, happy to merge in the original thread. I do have another approach however with xlsx writer but this removes all other data from existing sheet

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application') # opens Excel
writer = pd.ExcelWriter(file', engine='xlsxwriter')
df.to_excel(writer, sheet_name='abc', startrow=5, startcol=5,index=False)
writer.save()

Upvotes: 3

Views: 2447

Answers (1)

Janne Karila
Janne Karila

Reputation: 25197

Instead of

ws[get_column_letter(c+stcol)+str(strow)]

you can use

ws.cell(column=c+stcol, row=strow)

Upvotes: 2

Related Questions