23

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.

Improve this question

4 Answers 4

Reset to default
20

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()

enter image description here

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()

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

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, 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
thanks for quick response, i did try df.to_excel(writer, index=False, header=None) it however deletes the first row
@penta, can you upload somewhere (for example: dropmefile.com) your sample Excel file and post here a link ?
@Datanovice, you might want to check this answer :)
|
14

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.

Improve this answer

1 Comment

Will be accepting this answer after 2 days, just to see if anyone comes with better solution
1

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
Improve this answer

Comments

0

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

  1. writer.save() is deprecated in version 1.5.0, you should use writer.close().

  2. 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()
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.