[Feature-13610]Export to Excel in Perspective Ignition 8

Not 100% sure this is the reason, but if you are on a version 8.0.7 or higher, the toExcel() function will export the data as an “xlsx” instead of the older “xls” file type. It may go away if you set the file type to “xlsx”.

100% what osolorzano said. We had this issue and as soon as we added .xlsx it went away.

Any chance you would be willing to share your script? I changed to .xlsx and now excel just tells me the file format is not valid. I feel like I must be missing something basic here.

This is my code

data = self.parent.parent.getChild("Table").props.data
	headers = data[0].keys()
	system.perspective.print(headers)
	
	datasetData = []
	for row in range(len(data)):	
		rowData = []
		for col in range(len(headers)):
			cell = data[row][headers[col]]
			if hasattr(cell, 'value'):
				rowData.append(cell.value)
			else:
				rowData.append(cell)
		#system.perspective.print(rowData)
		datasetData.append(rowData)
	
	#system.perspective.print(datasetData)	
	finalDataset = system.dataset.toDataSet(headers,datasetData)
	XLSFormat = system.dataset.dataSetToExcel(True,[finalDataset])
	system.perspective.download("test.xlsx",XLSFormat)

dataSetToExcel is the ‘legacy’, deprecated implementation. Use system.dataset.toExcel - it should be a simple swap.

that seems to have fixed my problem. I still have to manually add the “.xlsx” to the end of the file name in the save dialog box. Is there anyway to make that be automatic?

No; it’s up to your browser. The most you can provide to a browser is a suggested filename; whether the browser will use that (or parse it for an extension, etc) is out of our hands:
https://developer.mozilla.org/en-US/docs/Web/API/HTMLAnchorElement/download

That makes sense. Just for the sake of putting this out there, Google Chrome uses the suggested file name, as does the latest version of Firefox. It was actually perspective workstation that was ignoring it. I’m not sure if that is something within IA’s power to change.

2 Likes

Huh, good to know - I let the relevant experts know about it. There might be something we can do to fix that.

1 Like

This works too.

data = self.getSibling(Table).props.data
data = system.dataset.toExcel(True, [data])
system.perspective.download(‘filename.xlsx’,data)

2 Likes

Nicely done Jay!

Is everyone able to view the downloaded Excel file?
I can get it it download but its always corrupted and Excel won’t let me view it.
Anyone have any suggestions on what to try?