Report to usable excel file

General Discussion
1

Hi, Guys,

I have a report designed with simple table, which I export to excel file. It works, but actually this excel file is not usable, since the data is in text boxes. Of course, there are ways to convert the text boxes to cells using VBA scripts, but for sure in Ignition there must be smarter solution, which I cannot see.
Any ideas? Perhaps some script in the report schedule options?

2

Perhaps my issue definition is not clear enough. So, this is how the excel table looks now after the report has been created:

Excel
Excel769×556 23.1 KB
The data for counter or working minutes cannot be processed further, because there are in text boxes, and not in cells.

3

Found this post, which treats absolutely the same issue - Report Viewer Component and Excel Export. However, it seems there is no clear conclusion and solution. Correct me if I am wrong.

4

The only reporting component that is exported as a real table in Excel is the basic table component. Try converting your data to a format that works with this component.

5

Thank you, Chi, I will give a try.

6

Not sure if this is still relevant, but I recently ran into a similar issue with the default EXCEL formatting. I was able extract data from any data source in a report and then convert it to an excel file using system.dataset.toExcel(). I used this in a scheduled Run Script Action to build an excel spreadsheet that I can send out monthly via email. You can specify multiple datasets and sheet names to build out an entire workbook as well. Its worth looking into the Run Script Action Documentation, but here is some sample code to follow.

def parseDataObject(data):
	headers = list(data[0].getKeys())
	rows = []
	for row in data:
		rowList = []
		for header in headers:
			value = row.getKeyValue(header)
			rowList.append(value)
		rows.append(rowList)
	return headers, rows

#get the desired dataObject from the data map by specifying which data source in the report to pull data from
dataObject = dataMap['dataSource']
#capture function return
headers, rows = parseDataObject(dataObject)
#convert headers and rows to a dataset
dataset = system.dataset.toDataSet(headers, rows)
#build excel spreadsheet
spreadsheet = system.dataset.toExcel(True, [dataset], True, sheetNames = ["SheetName"])
#spreadsheet can then be saved as a file or sent as an attachment in an email
#email example
system.net.sendEmail(smtpProfile='your smtp profile', fromAddr="from@emailaddress.com", subject='Email Subject', body="Email Body", html = 0, to = 'to@emailaddress.com', attachmentNames = ["AttachedFileName.xlsx"], attachmentData = [spreadsheet])
1 Like