Write pandas df into excel file with xlsxwriter?

Question

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

Do you definitely have xlsxwriter installed (and is it the most up to date version)? — Clusks
– Clusks, Commented Nov 16, 2016 at 13:32
Can you post a reproducible code which corrupts excel files? When i tried your code - it worked fine for me. — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Nov 16, 2016 at 13:44
I have it in jupyter and I have to evaluate some more things. I would have to mask a lot of things, since it is confidential. Let me see ... — Rockbar
– Rockbar, Commented Nov 17, 2016 at 9:41
I opended the xlsx file with a zip tool and realised that a lot of folders disappeared compared to the original. Also some formats get corrupted in the processed file. Further, images which where part of the excel file got lost. It seems so, that the saving process "destroys" the excel file. — Rockbar
– Rockbar, Commented Nov 24, 2016 at 11:36

MaxU - stand with Ukraine · Accepted Answer · 2016-11-16 13:43:25Z

2

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.

answered Nov 16, 2016 at 13:43

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Collectives™ on Stack Overflow

Write pandas df into excel file with xlsxwriter?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related