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?
23 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.
1
