How to write to the middle of an excel document with pandas

Question

I have an excel document (with formatting and everything) the first several lines are title and header stuff.

On line 11, I start with actual data, with column headers and everything.

I'm computing data to put into this table.

I can read the data with header=[11], and it will read in with the correct column names, and index and everything. I can fill in my data, adding columns as needed, etc.

The problem comes when I try to save the file. If I just save with to_excel, it saves, but without any of the title stuff, or formatting of the original.

How do I plug my dataframe into a spot in an existing excel document?

ETA:

One thing that I forgot to mention that may be important is that the original excel file also has conditional formatting all through the data portion. I want to maintain that.

ETA2: adding details for Josh's comment.

In [153]: xl.to_excel(writer,  "Program Area Summary.xls", startrow=11)
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-153-6541427c4a61> in <module>()
----> 1 xl.to_excel(writer,  "Program Area Summary.xls", startrow=11)

/Users/brianp/work/cyan/venv/lib/python2.7/site-packages/pandas/core/frame.pyc in to_excel(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep, verbose)
   1422         formatted_cells = formatter.get_formatted_cells()
   1423         excel_writer.write_cells(formatted_cells, sheet_name,
-> 1424                                  startrow=startrow, startcol=startcol)
   1425         if need_save:
   1426             excel_writer.save()

/Users/brianp/work/cyan/venv/lib/python2.7/site-packages/pandas/io/excel.pyc in write_cells(self, cells, sheet_name, startrow, startcol)
   1243             wks = self.book.create_sheet()
   1244             wks.title = sheet_name
-> 1245             self.sheets[sheet_name] = wks
   1246 
   1247         for cell in cells:

TypeError: list indices must be integers, not str

I did this in ipython, so there isn't really much other code. xl is a dataframe.

score 1 · Answer 1 · edited May 23 '17 at 12:34

1

There are two options I know of, the first I found from searching StackOverflow and you can find that here. You can use that example and the startrow parameter from the to_excel method to start your DataFrame at row 11 or some other desired place. Like

df.to_excel(writer, startrow=11, startcol=2).

The second option is the xlwings library, which is what I generally use when I work with Excel in Python. Here is xlwings link to documentation for using pandas DataFrames with it. Below is a code snippet to do what you're looking for.

import xlwings as xw
bk = xw.Book('BookX.xlsx')
sht = bk.sheets[0]
sht.range('A11').value = df # df is your pandas Dataframe
bk.save()

edited May 23 '17 at 12:34

Community

1
1

answered May 16 '17 at 15:43

Josh

2,767
1
27
31

Will it maintain conditional formatting in the preexisting sheet? (forgot to mention that initially...) – Brian Postow May 16 '17 at 16:21
1

I tested both (xlwings + openpyxl) and looks like both keep the pre-existing conditional formatting. – Josh May 16 '17 at 16:37
When I do the openpyxl, I get:TypeError: list indices must be integers, not str – Brian Postow May 16 '17 at 16:57
Hmm, post your code snippet / stack trace and I can look. – Josh May 16 '17 at 17:07

How to write to the middle of an excel document with pandas

1 Answers1