Copying an Excel file to a table's dataset

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)

Documentation

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  
23 Likes

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:

3 Likes

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