Copying a Excel file to a tables 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'.

def datasetToExcel(dsIn, fileName = '', sheetName = 'Sheet1', 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.
				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(0)
		for i, col in enumerate(pyDS.getColumnNames()):
			cell = headerRow.createCell(i)
		# Create data rows	
		for i, row in enumerate(pyDS):
			dataRow = sheet.createRow(i+1)
			for j, col in enumerate(list(row)):
				cell = dataRow.createCell(j)
				# 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(i) for i 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