The code below works perfectly in the script console but does not work at all in a perspective window or when running in the designer. There are no errors or anything showing in the output console. It just won't generate a file outside of the script console.
results = system.db.runQuery("Select Top 100 * From BreadBatching_Dough")
spreadsheet = system.dataset.toExcel(True, [results])
filePath = "D:\results.xlsx"
system.file.writeFile(filePath, spreadsheet)
The script console is running on your local machine.
The Perspective session is not allowed access to the file system. The designer is probably subject to the same Perspective restrictions. Maybe check that location one the gateway, it could have been saved there.
You'll have to read up on the different scopes.
You can use some Perspective download function, but it will land in your browser's default download location.
3 Likes
Look in your gateway's filesystem. It the target folder exists and the Ignition service has privileges there, that's where your file is.
Some topics with nearly identical discussions:
https://forum.inductiveautomation.com/search?q=system.perspective.download%20writeFile
No it is not on the gateway, I checked that last night. I already had it working with system.perspective.download but wanted to see what else I could do with the write function. It sounds like it is not really usable with Perspective and better suited with Vision. The write function was fixing an export problem I had getting the date column to show up correctly. I was losing the day and hour while only getting the minutes and seconds because excel did not know what the data type was and defaulted to nothing. The write function at least let excel see it was a datetime datatype. So, how do I take an exported datetime column and tell excel how to format the column? That is my real need.
You invoke the Apache POI libraries yourself instead using Ignition's .toExcel() wrapper. Some discussions:
https://forum.inductiveautomation.com/search?q=apache%20poi%20perspective
I have the code automatically creating the columns and formatting the data as needed but during the export to excel it loses it, at least excel get confused as to what it is.
#list = self.parent.parent.getChild("DBList").getChild("Table").custom.ColHeaders
list = self.props.data.getColumnNames()
returnlist =
for hdr in list:
returnlist.append(
{
"field": hdr,
"visible": True,
"editable": False,
"render": "auto",
"justify": "auto",
"align": "center",
"resizable": True,
"sortable": True,
"sort": "none",
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": True,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"numberFormat": "0,0.##",
"dateFormat": "MM/DD/YYYY HH:mm:ss",
"width": "auto",
"strictWidth": False,
"style": {
"classes": ""
},
"header": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
}
})
return returnlist
The structure and presentation of a Perspective table has zero influence on the output of system.dataset.toExcel. The only thing toExcel knows about is the dataset you give it. Use system.dataset.formatDates to format all date columns in a dataset into a particular string representation in one operation.
That did it thanks. Thought I would show my code for others who have the same issue.
tablename = self.getSibling("Table").custom.Table
query = ('Select Top 100 * From ' + tablename)
if self.parent.parent.getChild("DBTable").getChild("Table").custom.ID_yes == True:
query = ('Select Top 100 * From ' + tablename + ' Order by id')
results = system.db.runQuery(query)
formattedData = system.dataset.formatDates(results, "MM/dd/YYYY HH:mm:ss")
results = formattedData
spreadsheet = system.dataset.toExcel(True, [results])
system.perspective.download("myExport.csv",spreadsheet)
When sharing your code please use the preformatted text function. Plain text is unreadable and screenshots make it impossible for anyone to use your code.
1 Like
tablename = self.getSibling ("Table").custom.Table
query = ('Select Top 100 From + tablename)
results = system.db.runQuery(query)
formattedData = system.dataset.formatDates (results, "MM/dd/YYYY HH:mm:ss")
results = formattedData
spreadsheet = system.dataset.toExcel (True, [results]) system.perspective.download ("myExport.csv", spreadsheet)
Honestly, I'm pretty impressed with Google's Lens OCR. I see a couple extra spaces and a single missed single quote?
Who wouldn't want to practice their typing!!
Thanks for the tip. I have changed it.