Copying an Excel file to a table's dataset

Is there a easy way to copy a Excel File to a tables Dataset?


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. (283.0 KB)



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
	    	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.
			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 as Cell
	import as Row
	import as Sheet
	import as Workbook
	import org.apache.poi.xssf.usermodel.XSSFWorkbook as XSSFWorkbook
	import org.apache.poi.xssf.usermodel.XSSFDataFormat as XSSFDataFormat
	from 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
		raise Exception('Not a valid DataSet')

	if fileName == '':
		output = ByteArrayOutputStream()
		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()

	# Create style for data
	rowStyle = wb.createCellStyle()
	rowFont = wb.createFont()

	# Create style for dates.
	dateStyle = wb.createCellStyle()
	dateFont = wb.createFont()

	# Create header row in the sheet
	headerRow = sheet.createRow(firstRow)
	for j, col in enumerate(pyDS.getColumnNames()):
		cell = headerRow.createCell(j+firstCol)
	# 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)
			# Check if it's a date, and set cell format accordingly 
			if 'java.util.Date' in str(type(col)):
	# Resize the columns		
	for i in range(pyDS.getColumnCount()):

	if fileName == '':
		return output.toByteArray()

# --------------------------------------------------------
# Sample dataset
SampleHeaders = ['t_stamp', 'sValue', 'iValue', 'fValue']
SampleData = [['2021-01-21 08:00:00'), 'A', 1, 1.23],
        ['2021-01-22 08:30:00'), 'B', 2, 4.56],
        ['2021-01-23 09:24:00'), 'C', 3, 7.89],
        ['2021-01-24 19:37:00'), 'D', 4, 0.12],
        ['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')


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.


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:


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 as WorkbookFactory
	import as DateUtil
	from 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:
	   		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()
				# if lastCol is specified add 1 to it.
				lastCol += 1
			if hasHeaders:
				headers = list(row)[firstCol:lastCol]
				headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)] 
		rowOut = []
		for j in range(firstCol, lastCol):
			if i == firstRow and hasHeaders:
				cell = row.getCell(j)
				cellType = cell.getCellType().toString()
				if cellType == 'NUMERIC':
					if DateUtil.isCellDateFormatted(cell):
						value =  cell.dateCellValue
						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':
					if formulatype == 'NUMERIC':
						if DateUtil.isCellDateFormatted(cell):
							value =  cell.dateCellValue
							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
					value = None	
		if len(rowOut) > 0:

	return system.dataset.toDataSet(headers, data)

# Read .xlsx file
fName = 'C:/Test/poi1.xlsx'
ds1 = excelToDataSet(fName, True)		

#Read .xls file
fName = 'C:/Test/poi2.xls'
ds2 = excelToDataSet(fName, True)		
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  

I like the this :face_with_monocle: util.printDataSet() I assume that is a custom script module?

Thanks, this may come in handy for a project in the works.

Here you go. :slight_smile:


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 as WorkbookFactory
	import as DateUtil
	from import ByteArrayInputStream

	fileStream = ByteArrayInputStream(bytesIn)

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.XSSFCell

Ignition 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