Python How to use ExcelWriter to write to an existing worksheet

Question

I am trying to use ExcelWriter to write / add some information to a workbook containing multiple sheets. The first time I use this function, I create a book with some data. In the second call, I would like to add some information to the book in different places on all the 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 columns to print my data in different places.

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

But the problem is that the output is just the second call to the function, as if the function was overwriting the entire book. I think I need to download a book that already exists in this case? Any help?

Answer 1

load_book openpyxl - . xlsxwriter openpyxl:

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

Answer 2

Excel:

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

, .

Answer 3

Pandas 0.24.0 mode, Excel , . mode='a' .

From the documentation :

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