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

Thanks for your response. I have a customer asking me. They were trying to determine whether we custom write a csv export or wait for the feature to exist natively.

We’re going to custom write at least for the pilot system. They are hoping for this feature. Thanks for the rough ETA and adding the feature request.

3 Likes

Hi,
What is the situation on this?
Many of our customers are asking for this functionality.

No progress has been made insofar as modifying the Link to trigger downloads, but as others have mentioned this is possible when using the Web Dev module in conjunction with Perspective.

1 Like
	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)
3 Likes
	table = self.parent.parent.getChild("Table Container").getChild("Table").props.data
	excelstr = system.dataset.dataSetToExcel(True,[table])
	excelbytes = system.dataset.dataSetToHTML(1, table, "")
	OrderNo = self.view.params.OrderID
	fileName = "Purchase Request Number %s.xls" % OrderNo
	system.perspective.download(fileName, excelbytes)
3 Likes

Hello @ceszamora,

I just had this same issue come up and here is what i came up with. this is a script i put on a button.

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

You will need change the path to your table’s data prop and also the filename. I tested it on my system but i don’t have excel installed, i just have an excel file reader and it opened with no issues.

I found that i needed to convert json to a dataset in order to make my instance work.

Let me know if you find a better way.

Just a little bit change , and I export with default table @Phil_B

data = self.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.xls",XLSFormat)
3 Likes

This worked great!

Hi,

Has there been any development regarding exporting data from perspective in the current versions (8.0.15)

Thanks,
John

Hi all,

I got onto this forum thread looking for a way to download a table from perspective just like you all. I was able to figure it out on Ignition 8.1.0 (Perspective Module 2.1.0) by doing the following:

  1. Message Handler Script on Table component (1 indent on all lines) (Message Type was ‘download-test-data’) (Listen Scopes, selected ‘View’) :
    fileName = ‘testData.xls’
    excelbytes = system.dataset.dataSetToHTML(1, self.props.data, “”)
    system.perspective.download(fileName, excelbytes)

    OR in case you wanted a csv instead of xls file

    #csv = system.dataset.toCSV(self.props.data)
    #fileName = ‘testData.csv’
    #system.perspective.download(fileName, csv)

  2. On Click Event on Button component (1 indent on all lines):
    messageType = ‘download-test-data’
    system.perspective.sendMessage(messageType, scope = ‘view’)

Thanks for the discussion guys!

My table has hidden columns and custom headings… I found the following works well:

columns = self.getSibling("Table").props.columns
header = [col.header.title for col in columns if col.visible]

data = self.getSibling("Table").props.data
data = [[data.getValueAt(i,j) for j in range(data.columnCount) if columns[j].visible] for i in range(data.rowCount)]

data = system.dataset.toDataSet(header, data)
data = system.dataset.toExcel(True, [data])

system.perspective.download('test.xls',data)
1 Like

I am using this script as it was posted here, but I get the following error I try and open it in excel. " The file format and extension of ‘test.xls’ don’t match. The file could be corrupted or unsafe. Unless you trust its source, don’t open it. DO you want to open it anyway?" If I click yes the file will open. I should also add the save dialog box that opens when the script runs doesn’t list a file type and so I am adding the “.xls” to the end of the file name. Any ideas on what might be causing this issue?

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