Write values to a particular cell in a sheet in pandas in python

Question

I have an excel sheet, which already has some values in some cells.

For ex :-

        A      B      C      D
1      val1   val2          val3
2             valx   valy

I want pandas to write to specific cells without touching any other cells,sheet etc

This is the code i tried.

import pandas as pd
from openpyxl import load_workbook

df2 = pd.DataFrame({'Data': [13, 24, 35, 46]})
book = load_workbook('b.xlsx')
writer = pd.ExcelWriter('b.xlsx', engine='openpyxl')

df2.to_excel(writer, "Sheet1", startcol=7,startrow=6)

writer.save()

However this code deletes the older cell values.

I have reffered to :- How to write to an existing excel file without overwriting data (using pandas)? but this solution does not work.

MaxU - stand with Ukraine · Answer 1 · 2016-10-01T14:40:35.407

19

UPDATE2: appending data to existing Excel sheet, preserving other (old) sheets:

import pandas as pd
from openpyxl import load_workbook

fn = r'C:\Temp\.data\doc.xlsx'

df = pd.read_excel(fn, header=None)
df2 = pd.DataFrame({'Data': [13, 24, 35, 46]})

writer = pd.ExcelWriter(fn, engine='openpyxl')
book = load_workbook(fn)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, sheet_name='Sheet1', header=None, index=False)
df2.to_excel(writer, sheet_name='Sheet1', header=None, index=False,
             startcol=7,startrow=6)

writer.save()

UPDATE: your Excel file doesn't have a header, so you should process it accordingly:

In [57]: df = pd.read_excel(fn, header=None)

In [58]: df
Out[58]:
     0    1
0  abc  def
1  ghi  lmn

In [59]: df2
Out[59]:
   Data
0    13
1    24
2    35
3    46

In [60]: writer = pd.ExcelWriter(fn)

In [61]: df.to_excel(writer, header=None, index=False)

In [62]: df2.to_excel(writer, startcol=7,startrow=6, header=None, index=False)

In [63]: writer.save()

OLD answer:

You can use the following trick:

first read the existing contents of the excel file into a new DF:

In [17]: fn = r'C:\Temp\b.xlsx'

In [18]: df = pd.read_excel(fn)

In [19]: df
Out[19]:
       A      B     C      D
0   val1    NaN  val3   val4
1  val11  val22   NaN  val33

now we can write it back and append a new DF2:

In [20]: writer = pd.ExcelWriter(fn)

In [21]: df.to_excel(writer, index=False)

In [22]: df2.to_excel(writer, startcol=7,startrow=6, header=None)

In [23]: writer.save()

edited Oct 01 '16 at 14:40

answered Oct 01 '16 at 12:58

MaxU - stand with Ukraine

205,989
36
386
419

1

This solution comes close to answer, is there any way by which it will just write to specific cell, you see in your solution, it is re making the headers "A B C D" – penta Oct 01 '16 at 13:58
1

@penta, those headers are "comming" from the original Excel file. If you don't need them you can either ignore them when writing back to Excel file: `df.to_excel(writer, index=False, header=None)` or when reading it from excel – MaxU - stand with Ukraine Oct 01 '16 at 14:01
thanks for quick response, i did try df.to_excel(writer, index=False, header=None) it however deletes the first row – penta Oct 01 '16 at 14:07
@penta, can you upload somewhere (for example: http://dropmefile.com/) your sample Excel file and post here a link ? – MaxU - stand with Ukraine Oct 01 '16 at 14:09
the updated answer does work, however if you have many sheets in your excel, it deleted the other sheets, is there any way by which it just writes the data to specific sheet, cell and just not touch the existing data :-) – penta Oct 01 '16 at 14:29
@penta, for that you would have to combine two solutions: the one you've posted in your question and mine... ;) – MaxU - stand with Ukraine Oct 01 '16 at 14:33
I did try both of them combined, no luck :-) – penta Oct 01 '16 at 14:35
the above solution is slightly more helpful but it is anyhow reading the data and overwriting it(there are few elements besides texts which are getting deleted), I just want it to write to specific cell, sheet without touching the existing elements – penta Oct 01 '16 at 14:52
@penta, [How to make good reproducible pandas examples](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) - please edit your question accordingly... – MaxU - stand with Ukraine Oct 01 '16 at 14:55
FYI, i had asked it quite specifically in the question itself that the data should be written to specific sheet,cell :-) – penta Oct 01 '16 at 15:49
@penta, my answer does exactly that with the excel file you've uploaded... ;) – MaxU - stand with Ukraine Oct 01 '16 at 16:20
hey @MaxU this is a pretty popular answer, is this still the accepted method to write to specific excel worksheets? – Umar.H Dec 12 '19 at 15:12
1

@Datanovice, you might want to check [this answer](https://stackoverflow.com/a/38075046/5741205) :) – MaxU - stand with Ukraine Dec 12 '19 at 17:09

score 12 · Accepted Answer · edited Sep 20 '21 at 11:17

I was not able to do what was asked by me in the question by using pandas, but was able to solve it by using Openpyxl.

I will write few code snippets which would help in achieving what was asked.

import openpyxl

# to open the excel sheet and if it has macros
srcfile = openpyxl.load_workbook('docname.xlsx', read_only=False, keep_vba=True)

# get sheetname from the file
sheetname = srcfile.get_sheet_by_name('sheetname')
# write something in B2 cell of the supplied sheet
sheetname['B2'] = str('write something')
# write to row 1,col 1 explicitly, this type of writing is useful to
# write something in loops
sheetname.cell(row=1, column=1).value = 'something'

# save it as a new file, the original file is untouched and here I am saving
# it as xlsm(m here denotes macros).
srcfile.save('newfile.xlsm')

So Openpyxl writes to a purticular cell, without touching the other sheets,cells etc. It basically writes to a new file respecting the properties of the original file.

Will be accepting this answer after 2 days, just to see if anyone comes with better solution — penta, Oct 15 '16 at 14:52

score 1 · Answer 3 · answered Jan 28 '19 at 12:49

Using pandas to read the excel and append the file

def getpayment_excel(request):
    df = pd.read_excel(open(str(settings.MEDIA_ROOT)+"/"+"details.xlsx", 'rb'), sheetname='Sheet1')
    XLSX_MIME = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    response = HttpResponse(content_type=XLSX_MIME)
    response['Content-Disposition'] = 'attachment; filename="PythonExport.xlsx"'
    writer = pd.ExcelWriter(response, engine='xlsxwriter')
    df.loc[0,'Bank Name'] = "ICICIW"
    df.to_excel(writer, 'Sheet1', index=False)
    writer.save()
    return response

score 0 · Answer 4 · edited Oct 16 '22 at 20:24

So the solution buhtz provided didn't work for me for two reasons:

writer.save() is deprecated in version 1.5.0, you should use writer.close().
pandas makes a new 0 byte file when you run ExcelWriter without mode='a' which resulted in an error

zipfile badzipfile file is not a zip file

So, on the off chance a noob like me stumbles upon this problem I want to save them some time:

file = 'file.xlsx'

sheetNames = pd.ExcelFile(file).sheet_names

writer = pd.ExcelWriter(file, engine='openpyxl', mode='a', if_sheet_exists='overlay')
df2 = pd.DataFrame([13, 24, 35, 46])

for sheet in sheetNames:
    df = pd.read_excel(file, sheet_name=sheet, header=None)
    df.to_excel(writer, header=None, sheet_name=sheet, index=False)

df2.to_excel(writer, header=None, sheet_name=sheetNames[0], index=False, startcol=4, startrow=6)

writer.close()

Write values to a particular cell in a sheet in pandas in python

4 Answers4

Linked

Related