Pasting a dataframe into excel using openpyxl

Question

I have a table which is the output of a sql query and I want this table to be pasted into a specific cell of an excel (say B10).Using openpyxl I used to do ws_hi["c31"].value = output_total.iloc[0] (ws_hi is the excel sheet and output_total is the variable which holds the data I wanna copy) which works fine for a single value but not for a table. Need help in exporting if output_total is a 4*5 table

FYI output_total is dataframe obtained by output_total = pd.read_sql_query(text(query), engine1) thanks!

No, I want the table as is for example a 2*2 output would be pasted like value(1,1) in cell b10 , value(1,2) in cell c10 value(2,1) in b11 and so on — crawling_panda, Jul 07 '22 at 12:44
Thank you for you response, Charlie. I am an absolute beginner, Could you please elaborate on something specific? maybe with an example? — crawling_panda, Jul 07 '22 at 13:48
have a look at this answer: https://stackoverflow.com/a/36664027/14333932 — joAschauer, Jul 13 '22 at 18:07

score 0 · Answer 1 · answered Jul 07 '22 at 12:01

0

Since your data is stored in a pandas DataFrame, you can use pd.DataFrame.to_excel and specify the upper left cell where you want to dump your data with startrow and startcol.

In your case this would be something like the following for the B10 cell:

output_total.to_excel('excel/file/path.xlsx', startrow=11, startcol=1, header=False, index=False)

answered Jul 07 '22 at 12:01

joAschauer

106
7

Maybe I had to mention, the excel I had to write into had other data. to_excel just deleted all the prior data I had saved there – crawling_panda Jul 07 '22 at 12:11

Pasting a dataframe into excel using openpyxl

1 Answers1