# I want to import the excel sheet in Ignition Perspective

**URL:** <https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [October 31, 2022, 12:57pm UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870 "2022-10-31T12:57:24Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Divya\_Chorge1](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@Divya\_Chorge1](https://forum.inductiveautomation.com/u/Divya_Chorge1)\
**Post date:** [October 31, 2022, 12:57pm UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/1 "2022-10-31T12:57:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 31, 2022, 1:00pm UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/2 "2022-10-31T13:00:05Z")

</div>

> [@Copying an Excel file to a table's dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/13):
>
> 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.apac…

---

<div class="post-metadata">

**Author:** ![Divya\_Chorge1](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@Divya\_Chorge1](https://forum.inductiveautomation.com/u/Divya_Chorge1)\
**Post date:** [November 2, 2022, 10:22am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/3 "2022-11-02T10:22:39Z")

</div>

Thanks JordanCClark

---

<div class="post-metadata">

**Author:** ![Divya\_Chorge](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Divya\_Chorge](https://forum.inductiveautomation.com/u/Divya_Chorge)\
**Post date:** [March 24, 2023, 6:54am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/4 "2023-03-24T06:54:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 24, 2023, 8:31am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/5 "2023-03-24T08:31:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Divya\_Chorge](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Divya\_Chorge](https://forum.inductiveautomation.com/u/Divya_Chorge)\
**Post date:** [March 24, 2023, 9:27am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/6 "2023-03-24T09:27:10Z")

</div>

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](http://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)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 24, 2023, 9:29am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/7 "2023-03-24T09:29:03Z")

</div>

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

You didn't.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 24, 2023, 10:07am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/8 "2023-03-24T10:07:23Z")

</div>

> [@Divya\_Chorge](#):
>
> fName = 'C:/Test/poi1.xlsx'

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.

---

<div class="post-metadata">

**Author:** ![Divya\_Chorge](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Divya\_Chorge](https://forum.inductiveautomation.com/u/Divya_Chorge)\
**Post date:** [March 24, 2023, 10:47am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/9 "2023-03-24T10:47:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Divya\_Chorge](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Divya\_Chorge](https://forum.inductiveautomation.com/u/Divya_Chorge)\
**Post date:** [March 24, 2023, 10:48am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/10 "2023-03-24T10:48:07Z")

</div>

where should I use this format

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 24, 2023, 11:24am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/11 "2023-03-24T11:24:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 24, 2023, 11:48am UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/12 "2023-03-24T11:48:07Z")

</div>

> [@Divya\_Chorge](#):
>
> where should I use this format?

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.

---

<div class="post-metadata">

**Author:** ![Divya\_Chorge](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Divya\_Chorge](https://forum.inductiveautomation.com/u/Divya_Chorge)\
**Post date:** [March 24, 2023, 12:16pm UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/13 "2023-03-24T12:16:11Z")

</div>

![Capture](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/7/57c9a36d9fe364827d629043736cca6e8d9cd144.png)  
This is the screenshot of the sheet that I have to upload.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 24, 2023, 12:23pm UTC](https://forum.inductiveautomation.com/t/i-want-to-import-the-excel-sheet-in-ignition-perspective/65870/14 "2023-03-24T12:23:16Z")

</div>

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.