Perspective Export JSON data to Excel

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?

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

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)

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