openpyxl overwrites all data instead of update the excel sheet

Question

I'm trying to read a string from a text file and write it into an excel sheet without overwriting. I found somewhere that to update excel sheets, openpyxl in used. But my script just overwrites the entire sheet. I want other data to be the same.

python script:

from openpyxl import Workbook     
file_name="D:\\a.txt"    
content={}    
with open(file_name) as f:
    for line in f:
        (key,value)=line.split(":")
        content[key]=value

wb=Workbook()
ws=wb.active
    r = 2
for item in content:
    ws.cell(row=r, column=3).value = item
    ws.cell(row=r, column=4).value = content[item]
    r += 1
    
wb.save("D:\\Reports.xlsx")

Excel sheet before script:

Excel sheet after script :

How do I write the data to excel with overwriting other things ? Help.

Benjamin Benjamin · Accepted Answer · 2016-09-20T12:44:15

Overwriting is due to both saving the file with wb.save() and your hard coded starting row number r = 2.
1) If you don't care of overwriting the rows each time you execute your script you could use something like this:

from openpyxl import Workbook
from openpyxl import load_workbook

path = 'P:\Desktop\\'
file_name = "input.txt"    
content= {}    
with open(path + file_name) as f:
    for line in f:
        (key,value)=line.split(":")
        content[key]=value

wb = load_workbook(path + 'Reports.xlsx')
ws = wb.active

r = 2
for item in content:
    ws.cell(row=r, column=3).value = item
    ws.cell(row=r, column=4).value = content[item]
    r += 1

wb.save(path + "Reports.xlsx")

2) If you care about overwriting rows and the column numbers (3 & 4) you could try something like this:

from openpyxl import Workbook
from openpyxl import load_workbook

path = 'P:\Desktop\\'
file_name = "input.txt"    
content= []    
with open(path + file_name) as f:
    for line in f:
        key, value = line.split(":")
        content.append(['','', key, value]) # adding empty cells in col 1 + 2

wb = load_workbook(path + 'Reports.xlsx')
ws = wb.active

for row in content:
    ws.append(row)

wb.save(path + "Reports.xlsx")

openpyxl overwrites all data instead of update the excel sheet

1 Answers