15

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?

Hamed
  • 757
  • 3
  • 10
  • 17
  • 1
    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 '19 at 18:22
  • 1
    Possible duplicate of [How to write to an existing excel file without overwriting data (using pandas)?](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas) – T.C. Proctor Apr 25 '19 at 18:41

3 Answers3

19

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()
T.C. Proctor
  • 6,096
  • 6
  • 27
  • 37
Stefan
  • 41,759
  • 13
  • 76
  • 81
  • 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 '16 at 15:30
  • See http://stackoverflow.com/questions/30102232/pandas-can-not-write-to-excel-file – Stefan Jan 12 '16 at 15:32
  • Just double checked and works fine for me with `pandas 0.17.1` and `openpyxl 2.3.1`. – Stefan Jan 12 '16 at 15:45
  • 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 '16 at 17:53
  • 2
    @Stefan I just [posted a question](https://stackoverflow.com/questions/45220247/pandas-excel-writer-using-openpyxl-with-existing-workbook) 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 '17 at 16:32
  • @MattR - `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. – Stefan Jul 24 '17 at 19:22
  • 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. – T.C. Proctor Apr 25 '19 at 18:23
11

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')
T.C. Proctor
  • 6,096
  • 6
  • 27
  • 37
  • 1
    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 '19 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 '19 at 19:35
  • @silence_lamb `df` is a pandas `DataFrame`. – T.C. Proctor Sep 05 '19 at 18:55
  • 1
    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 01 '20 at 08:57
  • 1
    @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 01 '20 at 09:00
  • @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. – T.C. Proctor Apr 01 '20 at 12:41
  • @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? – Greencolor Dec 18 '21 at 20:43
1

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.

Naufal
  • 1,203
  • 14
  • 12