How to get Report script to put data on one sheet

Ignition
1

I'm using system.report.executeAndDistribute to save an Excel file to a shared location on a server. It puts about 38 rows of data on a sheet, then creates another sheet for the next 38 rows. With over 3000 rows of data, that's a lot of sheets. Is there some way to have Ignition put all the data into one sheet? I know there's a way to do this conversion in Excel, but I'd rather not put that on the user. I've tried both XLS and XLSX file types. Using Vision, version 8.1.33.

2

Is there a particular reason you are using a report to do this? If you only need the table data, you can use system.dataset.exportExcel() to generate your file from a simple dataset.

If you need something more advanced, you can use the Apache POI java library that comes with Ignition to generate any arbitrary content in Excel you like.

2 Likes
3

In other words, via reporting, the only way you can change the output format is by literally making the report "page" tall enough to fit all the rows.

Reports are really not designed to export to Excel; they're meant to be documents, not structured data. If you want structured data, you should deliberately export structured data using one of the approaches Phil mentioned.

1 Like
4

Thanks to both of you for your answers. We want to auto-generate reports. I'd done one with a PDF showing a trend chart. My mind just followed the Report module path for the XLS file. I think the system.dataset.toExcel is what we'll probably use.

I'm not clear on how the Apache POI java library comes with Ignition. There's nothing in the Ignition online manual, though I found some general references on the internet.

5

It's how Ignition accomplishes system.dataset.exportExcel(). But since the libraries are present in the system as jars, and jython lets you use java classes near-natively, you can write your own, completely arbitrary, Excel file generation scripts.

IA doesn't document things that it doesn't want to take support calls for. But that doesn't mean you can't do those things. Look on this forum for Apache POI examples.

2 Likes
6

Phil - That's very helpful. On a side note, my sincere thanks to you for generously sharing your expertise with the community!

2 Likes