Python How to use ExcelWriter to write into an existing worksheet

I am trying to use ExcelWriter to write/add some information into a workbook that contains multiple sheets. First time when I use the function, I am creating the workbook with some data. In the second call, I would like to add some information into the workbook in different locations into all sheets.

def Out_Excel(file_name,C,col): writer = pd.ExcelWriter(file_name,engine='xlsxwriter') for tab in tabs: # tabs here is provided from a different function that I did not write here to keep it simple and clean df = DataFrame(C) # the data is different for different sheets but I keep it simple in this case df.to_excel(writer,sheet_name = tab, startcol = 0 + col, startrow = 0) writer.save() 

In the main code I call this function twice with different col to print out my data in different locations.

Out_Excel('test.xlsx',C,0) Out_Excel('test.xlsx',D,10) 

But the problem is that doing so the output is just the second call of the function as if the function overwrites the entire workbook. I guess I need to load the workbook that already exists in this case? Any help?

3 Answers

Use load_book from openpyxl - see xlsxwriter and openpyxl docs:

import pandas as pd from openpyxl import load_workbook book = load_workbook('test.xlsx') writer = pd.ExcelWriter('test.xlsx', engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) df.to_excel(writer, sheet_name='tab_name', other_params) writer.save() 

Pandas version 0.24.0 added the mode keyword, which allows you to append to excel workbooks without jumping through the hoops that we used to have to do. Just use mode='a' to append sheets to an existing workbook.

From the documentation:

with ExcelWriter('path_to_file.xlsx', mode='a') as writer: df.to_excel(writer, sheet_name='Sheet3') 

You could also try using the following method to create your Excel spreadsheet:

import pandas as pd def generate_excel(csv_file, excel_loc, sheet_): writer = pd.ExcelWriter(excel_loc) data = pd.read_csv(csv_file, header=0, index_col=False) data.to_excel(writer, sheet_name=sheet_, index=False) writer.save() return(writer.close()) 

Give this a try and let me know what you think.