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')