I recently created some excel reports by using python library: openpyxl
I have a ReportTemplate with pie chart. Then, I write some data into the template file so that the data can be shown in pie chart.
I can write excel in xlsx format succesfully. But I got some error dialogs prompting “excel found unreadable content” when I opened the excel file if it contains pie chart.
Although it worked well after I dismissed the error dialogs, it’s still annoying.
So I did some tricks to get rid of it.
Below are what I did:
#open excel file
wb = openpyxl.load_workbook(“ReportTemplate_New2.xlsx”)
Sheet = wb[“Report”]
#write the cell
Sheet[batchnamecol+str(batchcurrow)].value = batchname
#save it
wb.save(outputname)
#the trick is: I unzip the template and output report files, then, copy some xml #files from the output report into template. Then, zip the updated template #extracted folder as the final output.
zip_ref = zipfile.ZipFile(filename, ‘r’)
zip_ref.extractall(unzippathsource)
zip_ref.close()
target_ref = zipfile.ZipFile(“ReportTemplate_New2.xlsx”, ‘r’)
target_ref.extractall(unzippathtarget)
target_ref.close()
#I only have one sheet, so just copy three xml files
shutil.copyfile(unzippathsource+”/xl/worksheets/sheet1.xml”, unzippathtarget+”/xl/worksheets/sheet1.xml”)
shutil.copyfile(unzippathsource+”/xl/workbook.xml”, unzippathtarget+”/xl/workbook.xml”)
shutil.copyfile(unzippathsource+”/xl/sharedStrings.xml”, unzippathtarget+”/xl/sharedStrings.xml”)
shutil.make_archive(finaloutput, ‘zip’, “./” + unzippathtarget)
os.rename(finaloutput+”.zip”, finaloutput)
There is another way by writting these three files directory using report data. But I’m just lazy and don’t want to analyze the format of excel xml.