3

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

Community
  • 1
  • 1
Rockbar
  • 1,081
  • 1
  • 20
  • 31
  • Try importing **xlsxwriter** package – Shiva Nov 16 '16 at 13:28
  • Do you definitely have xlsxwriter installed (and is it the most up to date version)? – Clusks Nov 16 '16 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 Nov 16 '16 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 Nov 17 '16 at 09: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 Nov 24 '16 at 11:36

1 Answers1

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.
MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419