I’m thinking that this may be less that easy to do if at all possible.
A customer has a screen where a report is accessible via the Report Viewer component.
He has asked that we provide a button on that screen to export the data in the report bound to the viewer into an Excel spreadsheet. Is it possible to do this directly from the report view component?
Are you asking if you can script the equivalent of right clicking the Report Viewer component and selecting save as XLS?
Some how I missed the scripting section of the help doc.
Easy once I’m drug to the right spot!
So I was just made aware that this saveAsXls(fileName) method of the Report Viewer doesn’t put the data into the spreadsheet proper. What it does is creates a bunch of text boxes in the spreadsheet and places the data in the text boxes.
Is there a way to make this create a spreadsheet with the data actually in the cells of said spreadsheet?
You could export the report as a csv with system.report.executeReport
I have implemented the system.report.executeReport method.
It did not throw an exception, so I assume it executed successfully.
However, if it did, I have know idea where it put the exported file.
Is there a default location?
From the documentation this seems to execute at the Gateway. Is there a way to prompt the user to specify a location, specifically a client side location?
It didn’t put the file anywhere if you used executeReport. It generated a byte array of the file and from there you could use system.file.writeFile to save the file wherever you would like.
You can also use system.report.executeAndDistribute with the save action but this will be relative to the gateway.
Here’s a screenshot of my save to XLS export:
The data fields in the report are wrapped in a table component- so those get set into cells properly.
Shapes/and charts are essentially value images and placed in different containers in excel.
Here is what the xls looks like when I execute the Save to XLS method:
So each value is placed in a text box, not cells in the spreadsheet. This makes it next to useless for the customer as they want to be able to manipulate the data for their purposes in generating other presentations, etc. To have to go into each text box and copy/paste the data would be time consuming, tedious, and error prone.
I’m hoping that the system.report.exeuteReport method may be a solution. BUT, if you know how to utilize this Save as XLS method in a way that will put the data in actual spreadsheet cells, I’d be ecstatic to hear about it!
Gotcha, looks like you’re using a cross tab report component to show the data?
I just tested with that and confirmed it just creates a bunch of text objects since there isn’t a good way to correlate the multiple row spans to cell without some additional logic.
The basic table can render into cells… if splitting up the data into chunks and into separate regular tables the excel port could work…
Otherwise you may need to script a custom dataset that renders the data and exportToExcel to achieve the formatting.
Based on Ethomason’s comments I have gotten the system.report.executeReport and the system.file.writeFile functionality to work by saving the report byte array content to a CSV file.
One follow on question I have is this. The documentation for the system.repor.exeuteReport method indicates that as of version 7.9.5 the xls file format was available for this method. When I try to use that file format with the method it throws an exception ("com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unknown file format requested
"). The value I used when this happens is ‘xls’. I noticed that there is a “EXCEL” save as option in the Report Schedule Actions Save File feature:
I tried using the text “EXCEL” in my system.report.executeReport method and that throws the same exception.
Any suggestions on why this isn’t working or what I may be doing wrong?
This is a bug, unfortunately - we know about it, but it hasn’t risen to the top of the pile of things to fix.
XLReporter, (www.sytech.com) has a viewer that could be launched from the HMI screen and can report on Ignition Real-time, Historical or Transaction Manager Values. Reports are Excel based.