File upload component / excel read

rfolkes · January 17, 2024, 2:54pm

8.1.33

Trying to use File upload component to upload a excel file. Then I am trying to test out excel dataset read (I might when done put the read script inside the file upload component but currently just testing with script console while I figure out how I want to do script functions.)

So I have file upload that is saving file on gateway.
confirmed file is actually on the gateway

then in script console running this code that I found in forum

from org.apache.poi.ss.usermodel import WorkbookFactory as WorkbookFactory, DateUtil as DateUtil, Row, CellType
from java.io import FileInputStream
from java.util import Date

def excelToDataSet(fileName, hasHeaders = False, sheetNum = 0, firstRow = None, lastRow = None, firstCol = None, lastCol = None):
	fileStream = FileInputStream(fileName)

	wb = WorkbookFactory.create(fileStream)
	
	sheet = wb.getSheetAt(sheetNum)

	if firstRow is None:
		firstRow = sheet.firstRowNum
	if lastRow is None:
		lastRow = sheet.lastRowNum

	data = []
	for i in range(firstRow , lastRow + 1):
		row = sheet.getRow(i)
		if i == firstRow:
			if firstCol is None:
				firstCol = row.firstCellNum

			if lastCol is None:
				lastCol  = row.lastCellNum
			else:
				# if lastCol is specified add 1 to it.
				lastCol += 1
			if hasHeaders:
				headers = list(row)[firstCol:lastCol]
			else:
				headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)] 
		rowOut = []
		for j in range(firstCol, lastCol):
			if not (i == firstRow and hasHeaders):
				cell = row.getCell(j,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
				
				cellTypes = (cell.cellType,cell.cachedFormulaResultType if cell.cellType == CellType.FORMULA else None)				
				if CellType.NUMERIC in cellTypes:
					if DateUtil.isCellDateFormatted(cell):
						value =  cell.dateCellValue
					else:
						value = cell.numericCellValue
						if value == int(value):
							value = int(value)
				elif CellType.STRING in cellTypes:
					value = cell.stringCellValue
				elif CellType.BOOLEAN in cellTypes:
					value = cell.booleanCellValue
				elif CellType.BLANK in cellTypes:
					value = None
				else:
					value = None
				rowOut.append(value)
		if len(rowOut) > 0:
			data.append(rowOut)

	fileStream.close()
	
	return system.dataset.toDataSet(headers, data)
	
dataset= excelToDataSet('<U+202A>C:\IgnitionUploads\Loadsheet1.xlsx', hasHeaders = False, sheetNum = 5, firstRow = 3, lastRow = 5, firstCol = 1, lastCol = 4)

But getting this response

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 62, in <module>
  File "<input>", line 6, in excelToDataSet
	at java.base/java.io.FileInputStream.open0(Native Method)

	at java.base/java.io.FileInputStream.open(Unknown Source)

	at java.base/java.io.FileInputStream.<init>(Unknown Source)

	at java.base/java.io.FileInputStream.<init>(Unknown Source)

	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Unknown Source)

	at java.base/java.lang.reflect.Constructor.newInstance(Unknown Source)

	at org.python.core.PyReflectedConstructor.constructProxy(PyReflectedConstructor.java:213)

java.io.FileNotFoundException: java.io.FileNotFoundException: âªC:\IgnitionUploads\Loadsheet1.xlsx (The filename, directory name, or volume label syntax is incorrect)

any ideas why it is failing to get the file?

Kevin.Herron · January 17, 2024, 3:08pm

Do you not see the goofy character you have at the beginning of your file path?

rfolkes · January 17, 2024, 3:14pm

I don't know where that is coming from

pturmel · January 17, 2024, 3:31pm

In a file upload, don't use a file stream. Just wrap the event's file bytes in a ByteArrayInputStream, and feed that to POI.

JordanCClark · January 17, 2024, 3:54pm

Look a few posts further down from where you found my original script.

rfolkes · January 17, 2024, 4:25pm

ok Here is my script in onFileReceived of file upload

def runAction(self, event):
	event.file.copyTo('c:\IgnitionUploads\Loadsheet1.xlsx')
	from org.apache.poi.ss.usermodel import WorkbookFactory as WorkbookFactory, DateUtil as DateUtil, Row, CellType
	from java.io import ByteArrayInputStream
	from java.util import Date
	
	hasHeaders = False
	sheetNum = 0
	firstRow = 3
	lastRow = 5
	firstCol = 1
	lastCol = 3
	
		
	fileStream = ByteArrayInputStream(event.file.getBytes())


	wb = WorkbookFactory.create(fileStream)
	
	sheet = wb.getSheetAt(sheetNum)

	if firstRow is None:
		firstRow = sheet.firstRowNum
	if lastRow is None:
		lastRow = sheet.lastRowNum

	data = []
	for i in range(firstRow , lastRow + 1):
		row = sheet.getRow(i)
		if i == firstRow:
			if firstCol is None:
				firstCol = row.firstCellNum

			if lastCol is None:
				lastCol  = row.lastCellNum
			else:
				# if lastCol is specified add 1 to it.
				lastCol += 1
			if hasHeaders:
				headers = list(row)[firstCol:lastCol]
			else:
				headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)] 
		rowOut = []
		for j in range(firstCol, lastCol):
			if not (i == firstRow and hasHeaders):
				cell = row.getCell(j,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
				
				cellTypes = (cell.cellType,cell.cachedFormulaResultType if cell.cellType == CellType.FORMULA else None)				
				if CellType.NUMERIC in cellTypes:
					if DateUtil.isCellDateFormatted(cell):
						value =  cell.dateCellValue
					else:
						value = cell.numericCellValue
						if value == int(value):
							value = int(value)
				elif CellType.STRING in cellTypes:
					value = cell.stringCellValue
				elif CellType.BOOLEAN in cellTypes:
					value = cell.booleanCellValue
				elif CellType.BLANK in cellTypes:
					value = None
				else:
					value = None
				rowOut.append(value)
		if len(rowOut) > 0:
			data.append(rowOut)

	fileStream.close()
	
	self.view.getChild("root").custom.ds1 = system.dataset.toDataSet(headers, data)

Here is the sheet I am uploading

results in

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 46, in runAction
AttributeError: 'NoneType' object has no attribute 'getCell'

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 46, in runAction
AttributeError: 'NoneType' object has no attribute 'getCell'


Ignition v8.1.33 (b2023101913)
Java: Azul Systems, Inc. 17.0.8

line 46 is

				cell = row.getCell(j,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

so looks like I am getting None data into row

rfolkes · January 17, 2024, 4:49pm

ok fixed that by changing my references to base 0

||hasHeaders = False|
|---|---|
||sheetNum = 0|
||firstRow = 2|
||lastRow = 4|
||firstCol = 0|
||lastCol = 3|

Tamilvanan_S · January 31, 2024, 4:33pm

Hi, I Have one issue. I want read multiple sheets. any solution?
Thanks in Advance.

rfolkes · January 31, 2024, 5:18pm

take the first part of the "runAction" function above up to sheet=wb.getSheetAt(sheetNum)

make the rest of it its own function and call it for each sheet you want to parse data from. You will get a new dataset from each call. so you can either combine them if it is all the same data or figure out where to store the datasets created.

Tamilvanan_S · February 1, 2024, 6:56am

Thank you for the inputs.
I want to Read last sheet number...
Thanks in Advance.

JordanCClark · February 1, 2024, 12:29pm

numSheets = wb.getNumberOfSheets()
sheet = wb.getSheetAt(numSheets-1)

Apache POI Workbook docs. I suggest some reading is in order.