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
It's difficult to run a screenshot through the script. 
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