The Apache POI Java library classes can be imported into Python and used.
Here are useful links for the Apache POI library:
In order to use Apache POI classes in Python you will need to import them at the top of your Python scripts, like this:
from org.apache.poi.hssf.usermodel import HSSFWorkbook
See the Javadocs to find the full java paths to the classes.
Since there is already excellent tutorials on Apache POI, we will focus specifically on how you can use Apache POI in Ignition.
Loading A Microsoft Excel XLSX File Into A Power Table
The most obvious thing that you might want to do with the PA Office Document Module is to bring spreadsheet data into Ignition. The closest thing to a spreadsheet that we have in Ignition is a Power Table, so we will use the following script to load data from a spreadsheet into a Power Table. You might put this script on a button, or tie it to a runScript() expression binding to automatically update your Power Table with the most recent version of the Excel sheet.
from java.io import File, FileInputStream from org.apache.poi.ss.usermodel import Cell from org.apache.poi.xssf.usermodel import XSSFWorkbook # open the file, which allows you to open the workbook, # which allows you to open the sheet # which allows you to access the cell fileInputStream = FileInputStream(File('PATHTOYOURSPREADSHEET')) workbook = XSSFWorkbook(fileInputStream) spreadsheet = workbook.getSheetAt(0) dataset = system.dataset.toDataSet(, ) # get the rows one by one rowIterator = spreadsheet.iterator() while rowIterator.hasNext(): row = rowIterator.next() # get the cells one by one cellIterator = row.cellIterator() while(cellIterator.hasNext()): cell = cellIterator.next() rowIndex = cell.getRowIndex() columnIndex = cell.getColumnIndex() # add a column to the dataset if needed if dataset.columnCount < (columnIndex + 1): dataset = system.dataset.addColumn(dataset, [''] * dataset.rowCount, str(columnIndex), str) # add a row to the dataset if needed if dataset.rowCount < (rowIndex + 1): dataset = system.dataset.addRow(dataset, [''] * dataset.columnCount) # depending on the cell type, get the value using the related method dataType = cell.getCellType() if dataType == Cell.CELL_TYPE_BLANK: value = '' elif dataType == Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue() elif dataType == Cell.CELL_TYPE_ERROR: value = cell.getErrorCellValue() elif dataType == Cell.CELL_TYPE_NUMERIC: value = cell.getNumericCellValue() elif dataType == Cell.CELL_TYPE_STRING: value = cell.getStringCellValue() # finally set the value in the dataset dataset = system.dataset.setValue(dataset, rowIndex, columnIndex, value) event.source.parent.getComponent('powerTable').data = dataset fileInputStream.close()
Loading A PowerPoint Presentation Into An Image Component
But just because spreadsheets are the most obvious thing to bring into Ignition doesn’t mean we can’t get a little creative. How about we bring a presentation into Ignition and automatically flip through the slides on an Image component. Add an Image component to your window, and give it two custom properties, both of type Integer: currentSlide and timer. Set currentSlide to an initial value of 0. Set the timer property to be bound to the following expression so that it changes every five seconds:
getSecond(now(1000)) / 5
Next, in add a propertyChange script to your Image component as follows:
if event.propertyName == 'timer': from java.awt import Color, Image, RenderingHints from java.awt.image import BufferedImage from javax.imageio import ImageIO from javax.swing import ImageIcon from java.io import ByteArrayOutputStream, File, FileInputStream from math import ceil from org.apache.poi.hslf.usermodel import HSLFSlideShow # opens the presentation from a file fileInputStream = FileInputStream(File('PATH TO YOUR PPT FILE')) presentation = HSLFSlideShow(fileInputStream) # gets the next slide, wrapping around to the first one when the previous slide was the last one imageComponent = event.source previousSlideIndex = imageComponent.currentSlide slides = presentation.getSlides() slideCount = len(slides) currentSlideIndex = previousSlideIndex + 1 if currentSlideIndex >= slideCount: currentSlideIndex = 0 imageComponent.currentSlide = currentSlideIndex slide = slides[currentSlideIndex] # renders the slides as images pageSize = presentation.getPageSize() bufferedImage = BufferedImage(pageSize.width, pageSize.height, BufferedImage.TYPE_INT_RGB) graphics2D = bufferedImage.createGraphics() slide.draw(graphics2D) graphics2D.dispose() byteArrayOutputStream = ByteArrayOutputStream() ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream) byteArrayOutputStream.flush() byteArray = byteArrayOutputStream.toByteArray() byteArrayOutputStream.close() boundWidth = imageComponent.width boundHeight = imageComponent.height originalWidth = bufferedImage.width originalHeight = bufferedImage.height newWidth = originalWidth newHeight = originalHeight if originalWidth > boundWidth: newWidth = boundWidth newHeight = (newWidth * originalHeight) / originalWidth if newHeight > boundHeight: newHeight = boundHeight newWidth = (newHeight * originalWidth) / originalHeight scaledImage = bufferedImage.getScaledInstance(newWidth, newHeight, Image.SCALE_SMOOTH) # set the image on this component imageComponent.setIcon(ImageIcon(scaledImage))
Now when you open your project, it will cycle through the slides in your presentation and display them. Note that some parts of a presentation may not display, such as graphs. And this may require some changes to display a .pptx file. Your project will now look like this:
Using the Apache POI library, you can also pull in data from Word documents, Outlook messages, and Vision and Publisher files.
Be sure to go through the links at the start of this blog post for full documentation of the capabilities of the PA Office Document Module! How have you used the module in your Ignition project? We’d love to hear about it!