How to add Pandas dataframe to an existing xlsx file using to_excel

Question

I have write some content to a xlsx file by using xlsxwriter

workbook = xlsxwriter.Workbook(file_name)
worksheet = workbook.add_worksheet()
worksheet.write(row, col, value)
worksheet.close()

I'd like to add a dataframe after the existing rows to this file by to_excel

 df.to_excel(file_name,
             startrow=len(existing_content),
             engine='xlsxwriter')

However, this seems not work.The dataframe not inserted to the file. Anyone knows why?

Finally works with this answer https://stackoverflow.com/a/69258865/10844937 — haojie, Feb 01 '23 at 04:08

yunwoong · Answer 1 · 2023-02-01T04:38:10.423

Unfortunately, as the content above is not specifically written, let's take a look at to_excel and XlsxWriter as examples.

using xlsxwriter

import xlsxwriter

# Create a new Excel file and add a worksheet
workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet()

# Add some data to the worksheet
worksheet.write('A1', 'Language')
worksheet.write('B1', 'Score')
worksheet.write('A2', 'Python')
worksheet.write('B2', 100)
worksheet.write('A3', 'Java')
worksheet.write('B3', 98)
worksheet.write('A4', 'Ruby')
worksheet.write('B4', 88)

# Save the file
workbook.close()

Using the above code, we have saved the table similar to the one below to an Excel file.

Language	Score
Python	100
Java	98
Ruby	88

Next, if we want to add rows using a dataframe.to_excel :

using to_excel

import pandas as pd

# Load an existing Excel file
existing_file = pd.read_excel('example.xlsx')

# Create a new DataFrame to append
df = pd.DataFrame({
    'Language': ['C++', 'Javascript', 'C#'],
    'Score': [78, 97, 67]
})

# Append the new DataFrame to the existing file
result = pd.concat([existing_file, df])

# Write the combined DataFrame to the existing file
result.to_excel('example.xlsx', index=False)

The reason for using pandas concat:

To append, it is necessary to use pandas.DataFrame.ExcelWriter(), but XlsxWriter does not support append mode in ExcelWriter
Although the task can be accomplished using pandas.DataFrame.append(), the append method is slated to be deleted in the future, so we use concat instead.

I need to add df after the existing rows, you need the `startrow` parameter. — haojie, Feb 01 '23 at 02:45
I misunderstood your question. I have corrected my answer after re-checking. — yunwoong, Feb 01 '23 at 04:40

score 0 · Answer 2 · answered Feb 08 '23 at 15:01

The OP is using xlsxwriter in the engine parameter. Per XlsxWriter documentation "XlsxWriter is designed only as a file writer. It cannot read or modify an existing Excel file." (link to XlsxWriter Docs). Below I've provided a fully reproducible example of how you can go about modifying an existing .xlsx workbook using the openpyxl module (link to Openpyxl Docs).

For demonstration purposes, I'll first create create a workbook called test.xlsx using pandas:

import pandas as pd

df = pd.DataFrame({'Col_A': [1,2,3,4],
                   'Col_B': [5,6,7,8],
                   'Col_C': [0,0,0,0],
                   'Col_D': [13,14,15,16]})

df.to_excel('test.xlsx', index=False)

This is the Expected output at this point:

Using openpyxl you can use another dataset to load the existing workbook ('test.xlsx') and modify the third column with different data from the new dataframe while preserving the other existing data. In this example, for simplicity, I update it with a one column dataframe but you could extend it to update or add more data.

from openpyxl import load_workbook
import pandas as pd
df_new = pd.DataFrame({'Col_C': [9, 10, 11, 12]})
wb = load_workbook('test.xlsx')

ws = wb['Sheet1']

for index, row in df_new.iterrows():
    cell = 'C%d'  % (index + 2)
ws[cell] = row[0]

wb.save('test.xlsx')

With the Expected output at the end:

How to add Pandas dataframe to an existing xlsx file using to_excel

2 Answers2