Copying an Excel file to a table's dataset

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