So I’m looking at a project that needs to insert data in to a worksheet in an excel workbook, about 200 cells. this will happen once a week and the worksheet changes very week.
I’d like to do this within Ignition using a script. I found the openpyxl python library and this seems to fit the bill, however the latest versions only support Python down to 2.6 not 2.5 that Ignition uses. I found version 1.7 (the latest to support python 2.5) and it works fine except it will not maintain the spreadsheet conditional formatting that I need. If I try to use a newer openpyxl version then I get errors in the first few lines of library as it loads.
I don’t really know how big the changes between python 2.5 and 2.6 are, is it worth me trying to modify openpyxl to work with 2.5 (feels like a massive job) or do we know if 2.6 or higher will be implemented in Ignition 7.8?
I have a completed Ignition module that exposes Apache POI to Python scripting. I am just waiting for the module to be released on Module Marketplace. Hopefully it will be there sometime today.
I didn’t realize that. Before I made an Office Document module I downloaded the documentation for the Kymera Office Document Module and read the documentation. The documentation lists functions for creating a new excel document and adding datasets to it. But Apache POI can do much much more than that. I just now downloaded the module and looked inside and I see Apache POI in it. So once that module is installed Python should be able to access all of Apache POI.
One of the first things I did was look at your module but the manual seemed to indicate that it could only produce Excel files from datasets and save them etc, not update cell values from existing and without disturbing the rest of the file.
I’ll take a look, do you have anymore documentation?
To get a workbook, you can call either system.poi.file.openWorkbook(String filepath) or system.poi.file.openDBWorkbook(byte[])(I think).
From there, you can use the POI API to work with the work book. When you are done, you call system.poi.file.saveWorkbook(file,workbook)(Again, I think). I’ll get one of our devs to update this thread. Enclosed is a working example of importing from a XLS file.
from org.apache.poi.ss.usermodel import Row,Cell
path = system.file.openFile("xlsx")
columns = ["cimplicity_tag_path","tag_path","display_name"]
if path:
wb = system.poi.file.openWorkbook(path)
sheet = wb.getSheet("Sheet1")
for row in sheet:
values = {}
for cn in range(3):
cell = row.getCell(cn,Row.RETURN_BLANK_AS_NULL)
if cell is None:
print "blank!!!!"
else:
if cell.getCellType() == Cell.CELL_TYPE_STRING:
values[columns[cn]] = cell.getRichStringCellValue().getString()
elif cell.getCellType() == Cell.CELL_TYPE_NUMERIC:
if system.poi.utils.DateUtil.isCellDateFormatted(cell):
values[columns[cn]] = cell.getDateCellValue()
else:
values[columns[cn]] = cell.getNumericCellValue()
elif cell.getCellType() == Cell.CELL_TYPE_BOOLEAN:
values[columns[cn]] = cell.getBooleanCellValue()
elif cell.getCellType() == Cell.CELL_TYPE_FORMULA:
values[columns[cn]] = cell.getCellFormula()
system.db.runSFPrepUpdate("INSERT INTO tags (`cimplicity_tag_path`,`tag_path`,`display_name`,`definition`) VALUES (?,?,?,'{}')",values.values(),["ignition"])
java.lang.IllegalAccessError: java.lang.IllegalAccessError: tried to access method org.apache.poi.util.POILogger.log(ILjava/lang/Object;)V from class org.apache.poi.openxml4j.opc.PackageRelationshipCollection
caused by IllegalAccessError: tried to access method org.apache.poi.util.POILogger.log(ILjava/lang/Object;)V from class org.apache.poi.openxml4j.opc.PackageRelationshipCollection