Perspective Export JSON data to Excel

Ignition
1

I have this code which works great:

data = self.getSibling(“table_name”).props.data
Excel_xlsx = system.dataset.toExcel(True,[data])
system.perspective.download(“table_export.xlsx”,Excel_xlsx)

but this seems to only work for the “auto” Return Format.

For context: I have a table in Perspective which is driven by a Named Query and I’d like to be able to use the JSON Return Format to be able to custom-color the table rows. I know how to do that if I can edit the JSON. Am I right in that the above code does not work for JSON data, or am I overlooking something else?

2

Correct you can’t send the json to excel (well you could just not directly)

In this case I usually drop a excel export on my view that exports the named queries directly. Then transform the data for my table the way it’s needed for the UI

1 Like
3

Ah, I think I see. One way to do what you describe would be to take the Export PB and add a custom property bound to the Named Query, and the export ‘onActionPerofrmed’ script just references the PB custom property dataset itself. Then in the table display, bind the table data to that custom property and add transform(s) in the table. One of those transforms seem to need to be from here:

Script Transform - Ignition User Manual 8.0 - Ignition Documentation (inductiveautomation.com)

4

Why not get the data in json format for the table props.data property (that way you can make each cell its own object with its own styles so you can custom color each individual row/cell), then just convert the json into a dataset just before exporting as an excel file? Assuming that your table.props.data property contains a list of json objects that all have the same keys, this should work:

def jsonToDataset(list):
	columns = dict(list[0]).keys()
	data = []
	for item in list:
		row = []
		for column in columns:
			cellValue = item.get(column, '')
			try:
				dict(cellValue)
				cellValue = cellValue['value']
			except:
				pass
			row.append(cellValue)
		data.append(row)
	return system.dataset.toDataSet(columns, data)

json = self.getSibling("table_name").props.data
data = jsonToDataset(json)
Excel_xlsx = system.dataset.toExcel(True,[data])
system.perspective.download("table_export.xlsx",Excel_xlsx)

Of course, with this code, if your rows had a style property that you were using to change how they looked in the table, you're going to have an extra column of empty strings in your excel export named 'style'.

2 Likes
5

WOW! Excelent code!
I had a hard time trying to solve this until I found your code.
Thank You!

6

This helped me as well. Now, is there a better solution to download the table OR the filtered data if a filter is applied? Like, just download what I'm seeing.

I guess I could just put some IF the data property is empty then download the root data... just seems like a lot of work to get at the data being displayed.

7

I don't think that what you're describing is all that much work; I'd probably do exactly that. I might replace the line from my code above where I define the json variable with this:

json = self.getSibling("table_name").props.filter.results.data
if not json:
	json = self.getSibling("table_name").props.data

Of course, you'll have to make sure that both the props.filter.enabled AND the props.filter.results.enabled properties are set to true or else the filtered results data list will always be empty (not a problem with the code above; you just will always return the full table's data).

2 Likes