Python How to use ExcelWriter to write into an existing worksheet

Question

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?

Teleview

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. – Grub

Possible duplicate of How to write to an existing excel file without overwriting data (using pandas)? – Grub

Answer 1

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

Chesterfield

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' – Teleview

See #30102732 – Chesterfield

Just double checked and works fine for me with pandas 0.17.1 and openpyxl 2.3.1. – Chesterfield

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 – Teleview

@Chesterfield 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? – Kirkkirkcaldy

@Kirkkirkcaldy - writer.book=book sets the result of load_book as the writer's book attribute so you can manipulate it, after also generating the sheets attribute in the next step. No book or sheet attributes, no adding sheets or saving to excel. – Chesterfield

This looks like it's adding a new sheet to an existing workbook. But it looks to me like the OP wants to append data to an existing sheet. I'm guessing this will overwrite 'tab_name' if tab_name is a sheet name in the existing workbook. – Grub

Answer 2

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

Grub

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. – Grub

what is df? I saw this from the pandas website, but it also didn't say where does it come from – Hartz

@Hartz df is a pandas DataFrame. – Grub

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: – Farr

@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 – Farr

@J.Doe I probably didn't check the actual behavior - it looks like putting an existing sheet_name will just write a new sheet with a different name. – Grub

@T.C.Proctor I have problem with the code. I get the io.UnsupportedOperation: truncate error. Even there is an error it still takes the data and pastes in it but it creates another sheet3(1). is there any solution for this? – Limulus

Answer 3

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.

Kakapo

I don't think this actually does what the question is asking about. – Grub