Export data into excel

Ignition
1

I need to generate daily report based on historical data; I would like to group the data into two separate work sheets; Based on ignition documentation, it looks like I can use the function system.dataset.dataSetToExcel; However, the code I have is not working;

The following is the code I have:
normalDataset = [‘a’,‘b’]
repdate = system.db.dateFormat(event.source.parent.getComponent(‘Calendar’).date, “MM-dd-yyyy”)
call = system.db.createSProcCall(“GetZoneTempData1”)
call.registerInParam(1, system.db.VARCHAR, repdate)
system.db.execSProcCall(call)
results = system.dataset.toPyDataSet(call.getResultSet())
normalDataset[0] = system.dataset.toDataSet(results)
call = system.db.createSProcCall(“GetZoneTempData2”)
call.registerInParam(1, system.db.VARCHAR, repdate)
system.db.execSProcCall(call)
results = system.dataset.toPyDataSet(call.getResultSet())
normalDataset[1] = system.dataset.toDataSet(results)
spreadsheet = system.dataset.dataSetToExcel(1, [normalDataset])
filename = system.file.saveFile(“tempreport.xls”)
if filename != None:
system.file.writeFile(filename,spreadsheet)

I am not sure how to declare array of datasets; right now when I run this code, I do not get any error, but I cannot open the file
Any help to resolve this issue is appreciated

2

The dataSetToExcel() function expects the parameter to be one or more individual datasets, not a list of objects, which is what the variable normalDataset[] is. Try using this instead:

spreadsheet = system.dataset.dataSetToExcel(1, [normalDataset[0], normalDataset[1]])

This will put the datasets on different sheets within the workbook. If you want them on the same sheet, you’ll have to concatenate those two datasets before sending it off to the dataSetToExcel() function.

1 Like
3

A related question:
When we export the data into excel, is there way to specify to use a particular xls template file?

4

Unfortunately, there is no way to specify a template, Ignition just creates a basic XLS format file that is readable by Excel.

5

Is there any plan for an update in the near future that would allow us to use Excel templates when exporting (eg having a graph automatically created on the Excel spreadsheet)?

6

I don’t see anything on the radar for a feature like this, but feel free to post your request in the “Feature Requests” forum.

7

Any update on this?
i am looking to generation excel report but I want the file format to remain same?

8

This is not a planned feature. http://ideas.inductiveautomation.com is still the place to go.

9

Is their a way to generate a daily report so it doesn’t save over itself? This is the code so far. I am trying to get it to save to a C Drive location daily. Any help would be appricated.
Code so far.
1DataSet = event.source.parent.getComponent(‘Table’).data
2#Spreadsheet = system.dataset.dataSetToExcel(True, [DataSet])
3#system.db.dateFormat(event.source.parent.getComponent(“Calendar”).date,“yyyy-mm-dd”
4#filePath = “C:\output\results.xls”
5#system.file.writeFile(filePath, Spreadsheet)
6system.dataset.exportCSV(“Daliy Tank Volumes.csv”, 1, DataSet)

10

Just save the file name that includes more than only date, maybe a full datetime.
Another way would be to add the append flag to system.file.writeFile(filePath, Spreadsheet, True).