I have written a new blog post on the Perfect Abstractions blog titled Using the PA Office Document Module. In the blog post I give an overview of the module as well as dive into a couple of examples of how it can be used. Namely, I show how you can bring spreadsheet data into a Power Table and how you can flip through a PowerPoint presentation in your Ignition window. I’d love to hear any feedback, especially if you have any other ideas on how you might use the PA Office Document Module.
Before I get too involved in downloading a demo of the module.
Could you tell me if it can read and write .RTF files with formatting?
We have a ton of documentation written in RTF format that I would love to be able to display in Ignition.
The PA Office Document Module does not support RTF files. However, there are two ways that you might proceed. You could either first convert all your RTF files to Word documents, then import them into Ignition using the PA Office Document Module, or you could use system.file.readFileAsString and do a good deal of parsing to get at the contents of your files.
The blog link doesn’t work anymore. For all future users here’s a starting point:
Instructions
The Perfect Abstractions (PA) Document Module provides access to the Apache POI Java library for read/writing Microsoft office documents.
The Apache POI Java library classes can be imported into Python and used.
Here are useful links for the Apache POI library:
Overview
Excel Overview
Excel quick guide
Excel How To
Javadocs
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.
Example Python Script
### This script reads an existing excel spreadsheet, and inserts a date and tag values into specific cells, then saves and renames the file and closes the instance.
#Import Libs
from java.util import Calendar
from java.io import FileInputStream
from java.io import FileOutputStream;
from java.io import IOException
from org.apache.poi.ss.usermodel import WorkbookFactory
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator
# get current date and format
cal = Calendar.getInstance()
Year = cal.get(cal.YEAR)
Month = cal.get(cal.MONTH)+1
Day = cal.get(cal.DAY_OF_MONTH)
insertDate = str(Day)+'/'+str(Month)+'/'+str(Year)
fileDate = str(Day)+'-'+str(Month)+'-'+str(Year)
#read tags for row 4
row4Tags = ['Values/Value 1','Values/Value 2','Values/Value 3']
row4 = system.tag.readAll(row4Tags)
#define columns for row 4
row4col = [3,4,5]
# set file names
FileIn = 'C://test/Test1.xlsx' #set read file
FileOut = 'C://test/TestBack'+fileDate+'.xlsx' #set write file
# set Sheet name
SheetName = 'Hello'
# read file and select sheet
inp = FileInputStream(FileIn) #readfile
wb = WorkbookFactory.create(inp)
sheet = wb.getSheetAt(0)
# select date cell and insert date
cell = sheet.getRow(2).getCell(0)
cell.setCellValue(insertDate)
# Set Sheet Name
wb.setSheetName(0,str(fileDate)) #Problem with this.....(to be resolved)
# Write tags to cells
row = 4
index = 0
for col in row4col:
cell = sheet.getRow(row).getCell(col)
cell.setCellValue(row4[index].value)
index = index + 1
# force workbook to recalculate cell values before save
wb.setForceFormulaRecalculation(1)
# save workbook
Out = FileOutputStream(FileOut)
wb.write(Out)
# close out files
Out.close()
inp.close()
In v7.9 and prior, the Apache POI libraries are not part of Ignition. Using the POI libraries in this module, one can create arbitrary read/write/edit scripts for ms office documents. In 8.1, the libraries are part of Ignition’s core and you can create arbitrary read/write/edit operations. You just need to learn to use the POI classes directly. Ignition offers the .toExcel() function as a convenience.
