Adding rows to existing Excel file

0

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>
python
pandas
pandas.excelwriter
asked on Stack Overflow Aug 12, 2019 by Rafael Castelo Branco • edited Aug 12, 2019 by Dave

1 Answer

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 on Stack Overflow Aug 13, 2019 by Anders Steen Nilsen

User contributions licensed under CC BY-SA 3.0