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