Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 406
Hamed Avatar asked Jan 12 '16 13:01

Hamed


People also ask

How do I write to a different sheet in Python?

To write to multiple sheets it is necessary to create an ExcelWriter object with a target file name, and specify a sheet in the file to write to. Multiple sheets may be written to by specifying unique sheet_name . With all data written to the file it is necessary to save the changes.

How do I append a DataFrame to an existing Excel sheet in Python?

To append existing Excel sheet with new dataframe using Python Pandas, we can use ExcelWriter . to call load_workbook with the Excel file path. Then we caLL ExcelWrite to create the writer . And set writer.

How to create and write on Excel file using xlsxwriter in Python?

Create and write on excel file using xlsxwriter module in Python 1 Writing to Each Cell. We can write to each cell of an excel sheet by writing to the name of the cell. ... 2 Example 3 Output 4 Writing directly to Rows. In this approach we can initialize the row and column number from where we want to start writing. ... 5 Example 6 Output

How do I write data from Python to excel in Excel?

Write Excel with Python Pandas. You can write any data (lists, strings, numbers etc) to Excel, by first converting it into a Pandas DataFrame and then writing the DataFrame to Excel. To export a Pandas DataFrame as an Excel file (extension: .xlsx, .xls), use the to_excel () method.

How to write to an existing Excel file without overwriting data using Python pandas?

How to write to an existing Excel file without overwriting data using Python Pandas? To write to an existing Excel file without overwriting data using Python Pandas, we can use ExcelWriter.

Can it write to an existing Excel file?

It cannot write into existing excel file. We can write to each cell of an excel sheet by writing to the name of the cell. In the below example we create a workbook and then ass a worksheet to it.


3 Answers

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()
like image 111
Stefan Avatar answered Nov 03 '22 09:11

Stefan


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')
like image 40
T.C. Proctor Avatar answered Nov 03 '22 08:11

T.C. Proctor


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.

like image 38
Naufal Avatar answered Nov 03 '22 10:11

Naufal