Adding rows to existing Excel file

Question

I am trying to add a DataFrame to rows below existing cells on an existing .xlsx file with this code:

book = load_workbook(r"C:\path\file_name.xlsx")
writer = pd.ExcelWriter(r"C:\path\file_name.xlsx", engine='openpyxl')
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}
contract_df.to_excel(writer, startrow = 10, header = False, 
sheet_name='UsrLeaseContract')
writer.save()

I manage to add the data, but I am getting the following error when re-opening the file:

Removed Part: /xl/styles.xml part with XML error. (Styles) HRESULT 0x8000ffff Line 1, column 0. Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

and the detailed XML

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> 
    <logFileName>error344800_01.xml </logFileName>
    <summary>
     Errors were detected in file 'C:path\file_name.xlsx'
    </summary>
    <removedParts><removedPart>Removed Part: /xl/styles.xml part with XML error.  (Styles) HRESULT 0x8000ffff Line 1, column 0.
    </removedPart>
</removedParts><repairedRecords><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord>
    </repairedRecords></recoveryLog>

Anders Steen Nilsen · Accepted Answer · 2019-08-13 13:07:43Z

1

Have you tried using openpyxl directly to append the data? Ran this code and it ran without problems. Also, did not get any warnings when opening the Exel file.

from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

# list of strings
vegetables = ['potatoes', 'carrots', 'cabbage']

# Calling DataFrame constructor on list
df = pd.DataFrame(vegetables)

wb = load_workbook('file_name.xlsx')
ws = wb['UsrLeaseContract']

for r in dataframe_to_rows(df, index=True, header=True):
    ws.append(r)

wb.save('file_name.xlsx')

answered Aug 13, 2019 at 13:07

Anders Steen Nilsen

1065 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rafael Castelo Branco Over a year ago

It worked very well and with fewer lines of code than my original code, so it is a good answer. The error is still present on my exported sheet but I realize the error is the aspect of the import file. openpyxl scraps away all formatting.

Collectives™ on Stack Overflow

Adding rows to existing Excel file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related