# Perspective Export JSON data to Excel

**URL:** <https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914>\
**Category:** Ignition\
**Created:** [July 16, 2021, 5:02pm UTC](https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914 "2021-07-16T17:02:02Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andrew.Meronek](https://avatars.discourse-cdn.com/v4/letter/a/a9a28c/32.png) [@Andrew.Meronek](https://forum.inductiveautomation.com/u/Andrew.Meronek)\
**Post date:** [July 16, 2021, 5:02pm UTC](https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914/1 "2021-07-16T17:02:02Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ryanjmclaughlin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryanjmclaughlin/32/4318_2.png) [@ryanjmclaughlin](https://forum.inductiveautomation.com/u/ryanjmclaughlin)\
**Post date:** [July 17, 2021, 10:33am UTC](https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914/2 "2021-07-17T10:33:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Andrew.Meronek](https://avatars.discourse-cdn.com/v4/letter/a/a9a28c/32.png) [@Andrew.Meronek](https://forum.inductiveautomation.com/u/Andrew.Meronek)\
**Post date:** [July 20, 2021, 4:25pm UTC](https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914/3 "2021-07-20T16:25:09Z")

</div>

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)](https://docs.inductiveautomation.com/display/DOC80/Script+Transform)

---

<div class="post-metadata">

**Author:** ![swolnisty](https://avatars.discourse-cdn.com/v4/letter/s/7bcc69/32.png) [@swolnisty](https://forum.inductiveautomation.com/u/swolnisty)\
**Post date:** [July 20, 2021, 7:39pm UTC](https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914/4 "2021-07-20T19:39:36Z")

</div>

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:

```auto
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'.

---

<div class="post-metadata">

**Author:** ![jmorris](https://avatars.discourse-cdn.com/v4/letter/j/ba8739/32.png) [@jmorris](https://forum.inductiveautomation.com/u/jmorris)\
**Post date:** [February 18, 2022, 1:10am UTC](https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914/5 "2022-02-18T01:10:36Z")

</div>

> [@swolnisty](#):
>
> ```auto
> 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)
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![jcaudle](https://avatars.discourse-cdn.com/v4/letter/j/35a633/32.png) [@jcaudle](https://forum.inductiveautomation.com/u/jcaudle)\
**Post date:** [January 10, 2023, 7:20pm UTC](https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914/6 "2023-01-10T19:20:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![swolnisty](https://avatars.discourse-cdn.com/v4/letter/s/7bcc69/32.png) [@swolnisty](https://forum.inductiveautomation.com/u/swolnisty)\
**Post date:** [January 10, 2023, 7:45pm UTC](https://forum.inductiveautomation.com/t/perspective-export-json-data-to-excel/48914/7 "2023-01-10T19:45:51Z")

</div>

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:

```auto
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).