Is there a easy way to copy a Excel File to a tables Dataset?
4 Likes
any ideas? I have looked at the manual but don’t understand the steps.
Have you looked at this topic?
There’s a module for ignition that will read Excel sheets
Ok, Thanks for the info. I will check it out. I really didn’t want to pay for something but that might be the only option if there isn’t any other way.
xlrd works with Jython 2.7.
Unzip this into the user-lib/pylib/site-packages folder.
xlrd.zip (283.0 KB)
6 Likes
Hi Jordan
Is there any option to read and write xlsx in ignition?
The Apache POI libraries are included with Ignition. At the time I was only needing to read files, so xlrd was all that was necessary.
I'm just starting to dig into POI, myself, but here's a start:
EDIT 2021-08-23: Updated to allow JsonDataSets. Changed to allow for a ByteArray output instead of writing to a file.
EDIT 2023-01-20: Added option for date formats. Default is 'yyyy-MM-dd hh:mm:ss'.
EDIT 2023-05-12: Added options for first row and column, if needed to offset from A1
def datasetToExcel(dsIn, fileName = '', sheetName = 'Sheet1', firstRow = 0, firstCol= 0, dateFormat = 'yyyy-mm-dd hh:mm:ss'):
''' Convert a dataset to Excel
params:
dsIn: The dataset to convert. Valid types are:
- com.inductiveautomation.ignition.common.BasicDataset
- com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet
- com.inductiveautomation.ignition.common.JsonDataset
fileName: The name to save the Excel file. If omitted, the output will be a byte array.
This is useful to use with perspective (e.g. system.perspective.download)
sheetName: The tab name of the workbook. Default is Sheet1.
firstRow: Starting row for the export. Default is 0 (Row 1)
firstCol: Starting column for the export. Default is 0 (Col A)
dateFormat: How dates should be formatted, e.g. 'yyyy-mm-dd hh:mm:ss'.
Note that Excel does not use capital letters, because... Microsoft.
The script will auto lower-case your format.
'''
import org.apache.poi.ss.usermodel.Cell as Cell
import org.apache.poi.ss.usermodel.Row as Row
import org.apache.poi.ss.usermodel.Sheet as Sheet
import org.apache.poi.ss.usermodel.Workbook as Workbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook as XSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFDataFormat as XSSFDataFormat
from java.io import FileOutputStream, ByteArrayOutputStream
dsType = str(type(dsIn))
# Convert to PyDataSet, if needed
if 'com.inductiveautomation.ignition.common.BasicDataset' in dsType:
pyDS = system.dataset.toPyDataSet(dsIn)
elif 'com.inductiveautomation.ignition.common.JsonDataset' in dsType:
pyDS = system.dataset.toPyDataSet(dsIn)
elif 'com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet' in dsType:
pyDS = dsIn
else:
raise Exception('Not a valid DataSet')
if fileName == '':
output = ByteArrayOutputStream()
else:
output = FileOutputStream(fileName)
# Create workbook
wb = XSSFWorkbook()
# Create Sheet
sheet = wb.createSheet(sheetName)
# Create formatter
fmt = wb.createDataFormat()
# Create style for headers
headerStyle = wb.createCellStyle()
headerFont = wb.createFont()
headerFont.setBold(True)
headerFont.setFontHeightInPoints(10)
headerFont.setFontName('Arial')
headerStyle.setFont(headerFont)
# Create style for data
rowStyle = wb.createCellStyle()
rowFont = wb.createFont()
rowFont.setBold(False)
rowFont.setFontHeightInPoints(10)
rowFont.setFontName('Arial')
rowStyle.setFont(rowFont)
# Create style for dates.
dateStyle = wb.createCellStyle()
dateFont = wb.createFont()
dateFont.setBold(False)
dateFont.setFontHeightInPoints(10)
dateFont.setFontName('Arial')
dateStyle.setFont(dateFont)
dateStyle.setDataFormat(fmt.getFormat(dateFormat.lower()))
# Create header row in the sheet
headerRow = sheet.createRow(firstRow)
for j, col in enumerate(pyDS.getColumnNames()):
cell = headerRow.createCell(j+firstCol)
cell.setCellStyle(headerStyle)
cell.setCellValue(col)
# Create data rows
for i, row in enumerate(pyDS):
dataRow = sheet.createRow(i+1+firstRow)
for j, col in enumerate(list(row)):
cell = dataRow.createCell(j+firstCol)
cell.setCellValue(col)
cell.setCellStyle(rowStyle)
# Check if it's a date, and set cell format accordingly
if 'java.util.Date' in str(type(col)):
cell.setCellStyle(dateStyle)
# Resize the columns
for i in range(pyDS.getColumnCount()):
sheet.autoSizeColumn(i)
wb.write(output)
output.close()
if fileName == '':
return output.toByteArray()
else:
return
# --------------------------------------------------------
# Sample dataset
SampleHeaders = ['t_stamp', 'sValue', 'iValue', 'fValue']
SampleData = [[system.date.parse('2021-01-21 08:00:00'), 'A', 1, 1.23],
[system.date.parse('2021-01-22 08:30:00'), 'B', 2, 4.56],
[system.date.parse('2021-01-23 09:24:00'), 'C', 3, 7.89],
[system.date.parse('2021-01-24 19:37:00'), 'D', 4, 0.12],
[system.date.parse('2021-01-25 18:42:00'), 'E', 5, 3.45]
]
ds = system.dataset.toDataSet(SampleHeaders, SampleData)
datasetToExcel(ds, 'c:/test/poi1.xlsx', 'Dataset')
pds = system.dataset.toPyDataSet(ds)
datasetToExcel(pds, 'c:/test/poi2.xlsx', 'PyDataset')
15 Likes
Awesome Jordan. Have you tried reading xlsx files yet?
As stated elsewhere, I’ve been using xlrd for it, but I may have some time this weekend to play a bit.
Unless my lovely bride has other ideas. Then all bets are off.
4 Likes
Can you explain what is the benefits of using the PA Office module from market when we can use and call appachi poi directly as you show here?
As stated in other threads, in v7 the POI libraries were not included. the module was a wrapper for POI and (I think) added some useful programming hooks. Now that POI is included in v8, it’s not really needed, IMO.
Also, since Nick doesn’t really do Ignition stuff anymore, I don’t think there is a v8 of it.
Kymera’s, on the other hand, does have a v8 version of their module, though I’ve not used it.
1 Like
For another option re: reading Excel files, I started and never finished an Ignition module that brings in the fastexcel library: https://github.com/paul-griffith/kotlin-module
4 Likes
Got my paperwork done early, so here's a sample to read from an Excel file
EDIT: Reworked a bit to work with both .xls and .xlsx files
EDIT: Modified to properly cast data types
EDIT: Added handler for formula cell types
EDIT: Added parameters to optionally specify sheet, row, and column numbers
Reading back a file created from my other example:
def excelToDataSet(fileName, hasHeaders = False, sheetNum = 0, firstRow = None, lastRow = None, firstCol = None, lastCol = None):
import org.apache.poi.ss.usermodel.WorkbookFactory as WorkbookFactory
import org.apache.poi.ss.usermodel.DateUtil as DateUtil
from java.io import FileInputStream
from java.util import Date
"""
Function to create a dataset from an Excel spreadsheet. It will try to automatically detect the boundaries of the data,
but helper parameters are available:
params:
fileName - The path to the Excel spreadsheet. (required)
hasHeaders - If true, uses the first row of the spreadsheet as column names.
sheetNum - select the sheet to process. defaults to the first sheet.
firstRow - select first row to process.
lastRow - select last row to process.
firstCol - select first column to process
lastCol - select last column toprocess
"""
fileStream = FileInputStream(fileName)
wb = WorkbookFactory.create(fileStream)
sheet = wb.getSheetAt(sheetNum)
if firstRow is None:
firstRow = sheet.getFirstRowNum()
if lastRow is None:
lastRow = sheet.getLastRowNum()
data = []
for i in range(firstRow , lastRow + 1):
row = sheet.getRow(i)
print str(i).zfill(3), list(row)
if i == firstRow:
if firstCol is None:
firstCol = row.getFirstCellNum()
if lastCol is None:
lastCol = row.getLastCellNum()
else:
# if lastCol is specified add 1 to it.
lastCol += 1
if hasHeaders:
headers = list(row)[firstCol:lastCol]
else:
headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)]
rowOut = []
for j in range(firstCol, lastCol):
if i == firstRow and hasHeaders:
pass
else:
cell = row.getCell(j)
cellType = cell.getCellType().toString()
if cellType == 'NUMERIC':
if DateUtil.isCellDateFormatted(cell):
value = cell.dateCellValue
else:
value = cell.getNumericCellValue()
if value == int(value):
value = int(value)
elif cellType == 'STRING':
value = cell.getStringCellValue()
elif cellType == 'BOOLEAN':
value = cell.getBooleanCellValue()
elif cellType == 'BLANK':
value = None
elif cellType == 'FORMULA':
formulatype=str(cell.getCachedFormulaResultType())
if formulatype == 'NUMERIC':
if DateUtil.isCellDateFormatted(cell):
value = cell.dateCellValue
else:
value = cell.getNumericCellValue()
if value == int(value):
value = int(value)
elif formulatype == 'STRING':
value = cell.getStringCellValue()
elif formulatype == 'BOOLEAN':
value = cell.getBooleanCellValue()
elif formulatype == 'BLANK':
value = None
else:
value = None
rowOut.append(value)
if len(rowOut) > 0:
data.append(rowOut)
fileStream.close()
return system.dataset.toDataSet(headers, data)
# Read .xlsx file
fName = 'C:/Test/poi1.xlsx'
ds1 = excelToDataSet(fName, True)
util.printDataSet(ds1)
#Read .xls file
fName = 'C:/Test/poi2.xls'
ds2 = excelToDataSet(fName, True)
util.printDataSet(ds2)
row | t_stamp | sValue | iValue | fValue
-------------------------------------------------------------
0 | Thu Jan 21 08:00:00 EST 2021 | A | 1 | 1.23
1 | Fri Jan 22 08:30:00 EST 2021 | B | 2 | 4.56
2 | Sat Jan 23 09:24:00 EST 2021 | C | 3 | 7.89
3 | Sun Jan 24 19:37:00 EST 2021 | D | 4 | 0.12
4 | Mon Jan 25 18:42:00 EST 2021 | E | 5 | 3.45
row | t_stamp | sValue | iValue | fValue
-------------------------------------------------------------
0 | Thu Jan 21 08:00:00 EST 2021 | Z | 5 | 3.45
1 | Fri Jan 22 08:30:00 EST 2021 | Y | 3 | 0.12
2 | Sat Jan 23 09:24:00 EST 2021 | X | 2 | 7.89
3 | Sun Jan 24 19:37:00 EST 2021 | W | 4 | 4.56
4 | Mon Jan 25 18:42:00 EST 2021 | V | 1 | 1.23
24 Likes
I like the this 
util.printDataSet() I assume that is a custom script module?
Thanks, this may come in handy for a project in the works.
Hi Jordan
Thanks for the code.
Could you please update the code so it get file bytes instead of file path.
I need this because in perspective we only can upload file to server and deal with file stream.
Untested, but you should just be able to use ByteArrayInputStream instead.
def excelBytesToDataSet(bytesIn, hasHeaders = False):
import org.apache.poi.ss.usermodel.WorkbookFactory as WorkbookFactory
import org.apache.poi.ss.usermodel.DateUtil as DateUtil
from java.io import ByteArrayInputStream
fileStream = ByteArrayInputStream(bytesIn)
...
3 Likes
Hi Jordan,
When I use your code it work perfectly but when I want to save the window I always get following error:
SerializationException: Error during serialization for property 'contentPane' on object '[FPMIWindow]Main Window'
caused by SerializationException: Error during serialization for property 'data' on object '[PMITable]PMITable[Table]'
caused by SerializationException: Unable to create clean copy of class org.apache.poi.xssf.usermodel.XSSFCellIgnition v8.1.1 (b2020120808)
Java: Azul Systems, Inc. 11.0.7
And after that the script doesn't work without any error until I close designer and open it again.
Same here…
1 Like