Perspective Table excel report

Ignition
1

I wonder is it possible to get an excel report from Perspective table info? someone just click on button and export all info to excel file?

image
image766×730 34.2 KB

2

Slap a button on the view, grab the dataset from the table and run it through system.dataset.toExcel: system.dataset.toExcel - Ignition User Manual 8.1 - Ignition Documentation and system.perspective.download system.perspective.download - Ignition User Manual 8.1 - Ignition Documentation

Stripped example from a working application:

tableDS= self.parent.getChild("Table").props.data
excelBytes= system.dataset.toExcel(True,tableDS)
fileName = 'TableExport{}.xlsx'.format(system.date.format(system.date.now(),'yyyMMd_kms'))
system.perspective.download(fileName,excelBytes,'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
6 Likes
3

Thank you. It works.

1 Like
4

I get an error on the line that calls the toExcel() function:

Error running action 'component.onActionPerformed' on DataSetPlayGround@C/root/Button: Traceback (most recent call last): File "<function:runAction>", line 13, in runAction TypeError: can't convert [9, u'Test'] to com.inductiveautomation.ignition.common.Dataset

The data type from the DB is an nvarchar. Is there a simple way to strip the unicode character u off the string?

Thanks

5

Don't. It isn't really "there". It is an artifact of repr() operations when printing complex objects.

What it means is that you tried to supply something other than a list of datasets to the excel conversion function.

6

Ok,
Here's my code:

results = system.db.runNamedQuery("CAT/Material")
self.parent.custom.result = type(results)
spreadSheet = system.dataset.toExcel(True, results)
filePath = "C:\\Users\\...\\Data Exports\\results.xls"
system.file.writeFile(filePath, spreadSheet)

The type is a PyDataset, just found that out.
The error is the toExcel line.

Pretty basic query, 2 columns, 9 rows. I followed the docs for this function...oh, hang on, think I just saw something... I realized I was missing the [ ] around results. No error this time but I didn't get a file either.

7

This is a Perspective topic. Writing a file like that happens in the gateway. Are you looking in the gateway filesystem?

1 Like
8

@pturmel I moved this topic to this thread, since the OP has already solved his issue and there are probably going to be more questions.

Thanks!

1 Like