I want to import the excel sheet in Ignition Perspective

I wanted to import the excel sheet in Ignition Perspective.
I am using xlrd python library for importing the excel sheet, but it is giving me error as AttributeError: 'javapackage' object has no attribute 'open_workbook'. Kindly support for the same to resolve the error.

3 Likes

Thanks JordanCClark

Hi JordanCClark,
I am trying to use the script you provided to import the excel sheet but I am getting the following error
Traceback (most recent call last):
File "", line 95, in
File "", line 34, in excelToDataSet
TypeError: 'NoneType' object is not iterable

What should I do for this please suggest.

Show your code (using the </> code formatting button to format it) and show what line 95 and 34 are (since the code formatter doesn't show line numbers).

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:/Test/poi1.xlsx'
ds1 = excelToDataSet(fName, True)
util.printDataSet(ds1)

Line 34 - print str(i).zfill(3), list(row)
Line 95 - ds1 = excelToDataSet(fName, True)

... using the </> code formatting button to format it.

You didn't.

1 Like

Does this file exist? Everything after the return line (line 91) was an example of how to use the function, not part of the function itself.

Yes I have replaced the file path with the file name which exists in my folder.

where should I use this format

Can you share the file? Or PM it to me, and I can take a look at it.

It's a formatting button and operates the same as the B bold button or I italics button. Press the pencil icon on the post to edit it, select the code and press the </> button.
Preview the post and, if it's OK, press Submit.

1 Like


This is the screenshot of the sheet that I have to upload.

It's difficult to run a screenshot through the script. :wink:

That said, the portions of the spreadsheet to process need to look like a dataset. Headers with different names, no merged cells, etc.

1 Like