Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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? 
                Do you want to append D to C in the same sheet? Or do you you want to add a new sheet to the existing workbook?  If you want to append in the same sheet, you really should do the append in pandas.
– T.C. Proctor
                Apr 25, 2019 at 18:22
                Possible duplicate of How to write to an existing excel file without overwriting data (using pandas)?
– T.C. Proctor
                Apr 25, 2019 at 18:41
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()
                Thanks for your comment. Following your suggestion, I have to create the workbook for the first time and then whenever needed use the load_book. I did that but I get the following error : TypeError: copy() got an unexpected keyword argument 'font'
– Hamed
                Jan 12, 2016 at 15:30
                Thanks Stefan, I have been struggling with this for a while. I tried several times and I get an error as I mentioned in my previous comments. I am using openpyxl 2.3.1 and pandas 0.16.2   . I guess I need to find another solution. Thanks for your comment anyway
– Hamed
                Jan 12, 2016 at 17:53
                @Stefan I just posted a question and recently stumbled upon this post. can you explain what writer.book = book is doing and why this allows you to write to existing workbooks?
– MattR
                Jul 20, 2017 at 16:32
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')
                It's a little unclear if the OP wanted to append sheets to an existing workbook, but this does that. If Sheet3 already exists in the existing workbook, it will be overwritten with the contents of df.
– T.C. Proctor
                Apr 25, 2019 at 18:25
                what is df? I saw this from the pandas website, but it also didn't say where does it come from
– silence_lamb
                Aug 30, 2019 at 19:35
                On Pandas 1.03 (Py 3.8) this gave me ValueError: Append mode is not supported with xlsxwriter. Changing the engine to openpyxl resolved at least  the error  with pd.ExcelWriter(file,engine='openpyxl', mode = 'a') as writer:
– Björn
                Apr 1, 2020 at 8:57
                @T.C.Proctor: In my case it didnt overwrite it just added a 1 at the end. So a new sheet was created called Sheet31
– Björn
                Apr 1, 2020 at 9:00
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.