Using The PA Office Document Module

The PA Office Document Module “provides access to the Apache POI Java library for read/writing Microsoft office documents.” A further introduction from the official documentation says:

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!

Leave a Reply

Your email address will not be published. Required fields are marked *