Import excel to power table in vision

Hi Guys,

Any idea how to import an excel file to the power table in vision?

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(i) for i 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:\\IgnitionProjectBackup\\Record.xlsx'
ds1 = excelToDataSet(fName, True)		
util.printDataSet(ds1)
event.source.parent.getComponent('ptbl_Data').data = ds1

Found this error:

Ignition - 8.1.17

Thanks,
Priyanka

Try this one, it's essentially the same with a few minor changes, specifically to handle empty cells, which appears is what you have based on the error you got.

def excelToDataSet(fileName, hasHeaders = False, forceString = 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
	from os.path import exists
	
	"""
	Description:
		Function to create a dataset from an Excel spreadsheet. This is typically used in Vision and the file path is
		directly referenced to its location in the filesystem.
		
	Arguments:
		fileName: The path to the Excel spreadsheet. (required)
		hasHeaders: If true, uses the first row of the spreadsheet as column names.
		forceString: If true, forces all cell values to be strings. This can be useful if cell values in columns are not consistent. 
			To create a dataset, the data type is determined by the first row. If the data types are different, you can get errors. 
			If you force all cells to strings, then this is not an issue.
		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
	
	History:
		No.		Date		  Author				Comment
		1.0		2021-01-22	  Jordan Clark		    Initial - https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/13
		1.1		2023-02-17	  James Landwerlen	    Updated to handle blank cells
													and to force strings
	"""
	
	if exists(fileName):
		
		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)
			
			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(i) for i in range(firstCol, lastCol)] 
			
			rowOut = []
			for j in range(firstCol, lastCol):
				if i == firstRow and hasHeaders:
					pass
				else:
					cell = row.getCell(j)
					if cell is not None:
						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	
					else:
						value = None
					if forceString:
						rowOut.append(str(value))
					else:
						rowOut.append(value)
						
			if len(rowOut) > 0:
				data.append(rowOut)
	
		fileStream.close()
	
		return system.dataset.toDataSet(headers, data)
4 Likes

@jlandwerlen Thank you so much.

Worked perfectly!!

You're welcome, more @JordanCClark than me though.

1 Like

@jlandwerlen I'm facing another issue.

Can you please help?

Please see this post: