Exporting a report as a spreadsheet

Evan_Curry · April 10, 2025, 7:45pm

I am creating a monthly operating report for a client (it is a water plant), who needs a data sheet populated so that they can return a mandatory worksheet to the state every month. It will have a row for every day of the month (figured 11 days was plenty for an example).

There are two methods I am considering:

Utilizing the CrossTab component, use the Built In @Day@ parameter to populate the leftmost column and then I was thinking I could use some sort of time parameter to get the column headerss to display the 4 hour intervals you see in the picture. However, there is no time parameter. Assuming I can make one, called "hr", how would I script it to understand it needs to take the highest turbidity value and lowest chlorine residual in those 4 hour intervals?

So far as the data goes, I have my 2 tags brought in to the data sources and am assuming that once I have the column headers worked out, I can put that data source in the first cell (under header) of each column. Is this method possible? Does it make sense? Am I speaking jibberish?
Export the data to Excel - I have already figured out how to do this, but it is just with a simple table. This will work fine, but here is the question - is it possible to export this data to an excel speadsheet that has 2 tabs? The second tab would be the data dump in 2 columns with 15 minute intervals. It will be very, very long as it is for a month. But the first tab will be a premade spreadsheet that essentially looks like the above picture and all of the cells will have references to the datasheet

I think this is probably the better, easier, quicker idea. Interested in what people think either way, thanks!

pturmel · April 10, 2025, 7:51pm

You will have to post-process your historian query in a report scripted data source. Fundamentally, you need a values on 4-hour intervals, starting at midnight. The historian won't do the pivot for you--that's the part you need to do in the script. You also need to ensure the start date/time for the history query is precisely at midnight.

PGriffith · April 10, 2025, 7:58pm

Not via the reporting module.

I would seriously consider not using the reporting module for this at all.
If you have a strictly defined export format, you could create that once as a template, then use the Apache POI library embedded in Ignition to fill it with the selected data.

Put all the logic to generate your report into the project library, then invoke it via a gateway scheduled script.

Some prior art on POI:

github.com/JordanCClark/ignition_utils

dataset

30fcfb9cc

def printDataSet(datasetIn):
	from itertools import izip
	scope = util.scope.getScope()
	if 'PropertyTreeScriptWrapper$ArrayWrapper' in str(type(datasetIn)):
		headers = datasetIn[0].keys()
		data = []
		for row in datasetIn:
			data.append([item['value'] if 'PropertyTreeScriptWrapper$ObjectWrapper' in str(type(item)) else item for item in row.values()])
		
		datasetIn = system.dataset.toDataSet(headers, data)
		
	# Get Row and Column counts
	nrows = datasetIn.getRowCount()
	ncols = datasetIn.getColumnCount()
        		        			
	# Get max length of row labels
	rowLen = len(max(['row']+[unicode(i) for i in range(nrows)], key=len))
	# Get column names
	colNames = datasetIn.getColumnNames()
	# initialize lists to process columns

This file has been truncated. show original

And a (Java) reference for loading an existing workbook as a 'template':