I am working on a project where i want to load/update around 100 recipe at a time in MySQL database.
To achieve this we planned to import excel sheet (.xlsx format) in to ignition tag and using transition group or by database script we can load it in MySQL.
We tried using xlrd python lib version ( 0.7.9 ) since my ignition version is 7.9.16. with this we can achieve the goal but the xlrd 0.7.9 version can extract data from old excel version ( .xls format ).
Is there any other way to read to excel workbook using script. we don’t want to import any new module in platform.
Apache POI is the recommended tool for working with Excel files (reading and writing, including the modern .xlsx) in java/jython. With Ignition v7.9.x, you will need to include a 3rd-party module that provides and exposes them. I recommend Kymera’s Office Document module from the module showcase.
In Ignition v8.1, the Apache POI libraries v4.1.1 are included in Ignition’s core and can be imported in jython directly.
(You can also hack your Ignition install to have those libraries, but such hacks won’t survive an Ignition upgrade.)
I’ve explored with good success, using a .NET-based client/server tool that exposes individual cells to the OPCUA protocol via the native Excel RTD() function. It’s called “Excel Option for QuickOPC”, made by OPC Labs. You can find them on the OPC Foundation website as a registered member.
There’s a single ~$1200 licensing fee if memory serves, but you can get a trial, and get it extended by request.
My intended use was treating an Excel file like a PLC, and used the SQL bridge to link cells from various spreadsheets to a single parameter in a SQL database, or with OPC tags.
Seemed applicable to your recipe case.
Good luck!!
**Disclaimer: I’m in no way affiliated or have vested interest in OPC Labs as a company, except being an arms-length customer.