CODEWITHSUNDEEP
pythonpandasxlsxwriter
Hamed
Hamed

Reputation: 797

Python How to use ExcelWriter to write into an existing worksheet

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?

Upvotes: 16

Views: 106989

Answers (3)

Stefan
Stefan

Reputation: 42885

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

Upvotes: 20

T.C. Proctor
T.C. Proctor

Reputation: 6476

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

Upvotes: 12

Naufal
Naufal

Reputation: 1303

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.

Upvotes: 1

Related Questions