When I download a file using the system.perspective.download after creating my Excel file from the system.dataset.toExcel when I try to open it, Excel says the file is corrupt and can’t be downloaded. The data in the file is from a simple runQuery function.
Has anyone experienced the same behavior in perspective?
Any suggestions on what to try to get this to work?
Can you post your code that you are using to generate everything? I’ve got it working in an application with the same steps as yourself.
I have a button on the screen to perform this script.
results = system.db.runQuery("SELECT * FROM DataMain LIMIT 500", "MySQL_SCADA_LOG_C_rr")
spreadsheet = system.dataset.toExcel(True, [results])
system.perspective.download("test.xls",spreadsheet)
OK name the file as .xlsx instead of .xls
This is the code that I have that is working
#Export the ticket list to excel.
tickets = self.view.custom.tickets
if len(tickets) > 0:
headers = tickets[0].keys()
dataSet = [list(data.values()) for data in tickets]
ticketsExcel = system.dataset.toExcel(True,system.dataset.toDataSet(headers,dataSet))
fileName = 'Tickets_{}.xlsx'.format(
system.date.format(system.date.now(),'yyyMMd_kms')
)
system.perspective.download(fileName,ticketsExcel,'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
The only other thing I did was to add the contentType of “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”. I’m not thinking that that makes a difference though.
I just tried with the contentType and no change. I also tried adding results = system.dataset.toDataSet(system.db.runQuery("SELECT * FROM DataMain LIMIT 500", "MySQL_SCADA_LOG_C_rr")) just in case if the PyDataSet was giving it problems.
I even just tried this to see if creating a dataset with system.dataset.toDataSet(headers, values)
headers = ["Position", "Value"]
values = []
for i in range(25):
values.append([i, "Value" + str(i)])
results = system.dataset.toDataSet(headers, values)
spreadsheet = system.dataset.toExcel(True, [results])
system.perspective.download("test.xlsx",spreadsheet, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
File is still corrupted.
What browser are you using? Shouldn’t matter but I’m going to test it on my end real quick.
Also… what type of data is in your SQL Results? Any blob data or weird data types?
I’m using Chrome. The data contains datetimes, ints, floats and varchars. Nothing out of the ordinary.
Hmm… What version of Ignition are you using? I’ve tested in both 8.1.5 and 8.1.6
I just did a quick mockup in perspective
results = system.db.runQuery('select * from tblpo','TicketTrack')
spreadsheet = system.dataset.toExcel(True,[results])
system.perspective.download("test.xlsx",spreadsheet)
To match yours and it is working in Chrome.
And I also did your test
headers = ["Position", "Value"]
values = []
for i in range(25):
values.append([i, "Value" + str(i)])
results = system.dataset.toDataSet(headers, values)
spreadsheet = system.dataset.toExcel(True, [results])
system.perspective.download("test.xlsx",spreadsheet, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
with no issues. So there might be something with your version?
The ignition version is 8.1.1
I just had my co-worker download the file from the perspective view and it was not corrupted for him.
Well that’s a good sign.
Do you have antivirus running on your end? Maybe try disabling any add-ons and see if one of them is causing an issue.
Turns out i had to go to component services on my computer and go to properties on “My Computer” and edit the default properties and change the authentication level for the “Default Distributed COM Communication Properties” to “Connected” instead of “None”.
OK that I would have never thought of. Glad you got it working through.