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

[osolorzano]

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”.

[lwood]

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

[jd1]

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)

[PGriffith]

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

[jd1]

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?

[PGriffith]

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

[jd1]

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.

[PGriffith]

@jd1

It was actually perspective workstation that was ignoring it. I’m not sure if that is something within IA’s power to change.

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

[jay.meinke]

This works too.

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

[garnet.tozer]

Nicely done Jay!

[jramirez3]

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?

[mblystone]

You are my hero. Well done.

[Ryan_Bein]

Thank you for Sharing. This was a great solution for what I needed.

[Pierre_Nerriere]

def runAction(self, event):
	table = self.view.custom.data
	excelstr = system.dataset.toExcel(True,[table])
 
	fileName = "VolumeHmeleck %s.xlsx" % system.date.now()
	system.perspective.download(fileName, excelstr)

[Alex_Wakefield]

So I have a question. I have written code that works for this, and in "Preview" mode in the designer works great. Although when I open the application over the web, when I click my button, nothing happens. What would be the cause of this?

[cmallonee]

We would need to see your script in order to really help you. The Designer is not a true comparison to a running Session as it has different scopes, permissions, and capabilities available to it as an application, compared to a browser page.

[samer_samy]

@craigb

	dataset = self.parent.parent.getChild("Table Container").getChild("Your_Table").props.data
	data = system.dataset.toCSV(dataset, 1, 0, 0)
	filename = 'Orders.csv'
	system.perspective.download(filename, data)

it works ,u r genius

[samer_samy]

your code working also ,thanks . may be my attached pic will help some visitors here

[pawel.kondol1]

@jay.meinke

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

Great Job. This is what I was looking for. Not sure why this is not documented as export files in Ignition.