Rockbar Rockbar - 15 days ago 8
Python Question

Write pandas df into excel file with xlsxwriter?

I have scripted code for writing pandas df into excel file with openpyxl. See Fill in pd data frame into existing excel sheet (using openpyxl v2.3.2).

from openpyxl import load_workbook
import pandas as pd
import numpy as np

book=load_workbook("excel_proc.xlsx")
writer=pd.ExcelWriter("excel_proc.xlsx", engine="openpyxl")
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
data_df.to_excel(writer, sheet_name="example", startrow=100, startcol=5, index=False)
writer.save()


That procedure works fine. However, each returned excel file reports, when opening, that it is corrupted, since content is not readable. Excel can repair it and save it again. But this has to be done manually. Since I have to process many files, how can i solve/circumvent that?

Alternatively, how do I have to change the code to use "xlsxwriter" instead of "openpyxyl"?

When I just exchange "engine="openpyxl"" with "engine="xlsxwriter"" python tells me that "'Worksheet' object has no attribute 'write'" at the data_df.to_excel line.

Addition: Excel tells me "removed records named range of /xl/workbook.xml part" is the corruption and has to be repaired. I do not know, what it means

Answer

I think you'll have to use openpyxl, because xlsxwriter doesn't support yet modifying of existing Excel XLSX files.

From docs:

  • It cannot read or modify existing Excel XLSX files.