Given the following as an onActionPerformed action for a Perspective PB:
def runAction(self, event):
“”"
This event is fired when the ‘action’ of the component occurs.
Arguments:
self: A reference to the component that is invoking this function.
event: An empty event object.
"""
data = self.getSibling("Table").props.data
Excel_xlsx = system.dataset.toExcel(True,[data])
system.perspective.download("table_export.xlsx",Excel_xlsx)
Is there a known issue with self.getSibling(etc.).props.data? Specifically, the data in question has a python script doing a transform that adds a column to data that comes from a query binding, in JSON format. I have a bunch of table/button combos where this works that do not include an added script transform, but I have one case where the Excel export doesn’t work, and a difference is that this one case is the only table that includes a transform script. Is this a known bug/thing?
If I view the data in the table or copy the data into Notepad to view the post-transform JSON, it all looks fine.
The system.dataset.toExcel() function expects a dataset for the “dataset” Parameter. Are you sure the Table component’s data prop is currently a dataset? You may be able to change the format the query that populates the table returns in.
It would look somewhat like this simple example that assumes there is a dataset of 1 column called “id”:
data = self.getSibling("Table").props.data
#make list of headers for dataset
headers = ['id']
#generate empty list for dataset
dataset = []
#loop through table object and build a new row from the data
for row in data:
newRow = [row["id"]]
dataset.append(newRow)
#generate dataset
dataset = system.dataset.toDataSet(headers, dataset)
Excel_xlsx = system.dataset.toExcel(True,[dataset])
system.perspective.download("table_export.xlsx",Excel_xlsx)
That makes sense, I overlooked the requirement for system.dataset.toExcel(). Thanks. Following this down, given:
def getList(x):
return x.keys()
headers = getList(value[0])
rows = []
for x in value:
rows.append(list(x.values()))
newDataset = system.dataset.toDataSet(headers, rows)
return newDataset
This is adding the data transform as a specific “transform” step instead of putting it in the script for exporting to excel. This seems to work for some tables but not all - with a KeyError:21 at the line with system.dataset.toDataSet(). I’m not sure what the problem is; the number of headers and number of rows seem to match up, but it’s easy to miss as this error is popping up in a rather large set of data that’s not easy to eyeball.
Consider moving the actual query binding to a custom property, as a dataset, then perform your transform on a property binding to the table’s props.data. Then your script can access the original dataset for export.
Then use two custom properties. The first with the query binding, the second with the column added (but still a dataset). The the table’s data binding looks at the second custom property to transform to satisfy all the json table configuration requirements.