Copying a Excel file to a tables Dataset

As stated in other threads, in v7 the POI libraries were not included. the module was a wrapper for POI and (I think) added some useful programming hooks. Now that POI is included in v8, it’s not really needed, IMO.

Also, since Nick doesn’t really do Ignition stuff anymore, I don’t think there is a v8 of it.

Kymera’s, on the other hand, does have a v8 version of their module, though I’ve not used it.

1 Like

For another option re: reading Excel files, I started and never finished an Ignition module that brings in the fastexcel library: https://github.com/paul-griffith/kotlin-module

3 Likes

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.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(cNum) for cNum 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)

#Read .xls file
fName = 'C:/Test/poi2.xls'
ds2 = excelToDataSet(fName, True)		
util.printDataSet(ds2)
row | t_stamp                      | sValue | iValue | fValue
-------------------------------------------------------------
0   | Thu Jan 21 08:00:00 EST 2021 | A      | 1      | 1.23  
1   | Fri Jan 22 08:30:00 EST 2021 | B      | 2      | 4.56  
2   | Sat Jan 23 09:24:00 EST 2021 | C      | 3      | 7.89  
3   | Sun Jan 24 19:37:00 EST 2021 | D      | 4      | 0.12  
4   | Mon Jan 25 18:42:00 EST 2021 | E      | 5      | 3.45 
 
row | t_stamp                      | sValue | iValue | fValue
-------------------------------------------------------------
0   | Thu Jan 21 08:00:00 EST 2021 | Z      | 5      | 3.45  
1   | Fri Jan 22 08:30:00 EST 2021 | Y      | 3      | 0.12  
2   | Sat Jan 23 09:24:00 EST 2021 | X      | 2      | 7.89  
3   | Sun Jan 24 19:37:00 EST 2021 | W      | 4      | 4.56  
4   | Mon Jan 25 18:42:00 EST 2021 | V      | 1      | 1.23  
21 Likes

I like the this :face_with_monocle: util.printDataSet() I assume that is a custom script module?

Thanks, this may come in handy for a project in the works.

Here you go. :slight_smile:

2 Likes

Hi Jordan
Thanks for the code.
Could you please update the code so it get file bytes instead of file path.
I need this because in perspective we only can upload file to server and deal with file stream.

Untested, but you should just be able to use ByteArrayInputStream instead.

def excelBytesToDataSet(bytesIn, hasHeaders = False):
	import org.apache.poi.ss.usermodel.WorkbookFactory as WorkbookFactory
	import org.apache.poi.ss.usermodel.DateUtil as DateUtil
	from java.io import ByteArrayInputStream

	fileStream = ByteArrayInputStream(bytesIn)
	...
2 Likes

Hi Jordan,
When I use your code it work perfectly but when I want to save the window I always get following error:

SerializationException: Error during serialization for property ‘contentPane’ on object ‘[FPMIWindow]Main Window’
caused by SerializationException: Error during serialization for property ‘data’ on object ‘[PMITable]PMITable[Table]’
caused by SerializationException: Unable to create clean copy of class org.apache.poi.xssf.usermodel.XSSFCell

Ignition v8.1.1 (b2020120808)
Java: Azul Systems, Inc. 11.0.7

And after that the script doesn’t work without any error until I close designer and open it again.

Same here…

1 Like

I successfully test the code in perspective without any error.
So it is only for vision.

Huh. I get the same thing. Looks like we can’t serialize the dataset. I’ll take a closer look when I get some time to do so.

1 Like

Function updated above, hopefully casting values as something other than a cell.

The most easy way!

Okay, now paste it into a dataset. From perspective.

1 Like

Are you[quote=“OscarLucas, post:25, topic:34942, full:true”]
The most easy way!

[/quote]

Are you going to give an operator access to SQL?? Not the best idea. Plus this post was about Excel to an Ignition table, not necessarily SQL table.

@OscarLucas file->save as csv and we can avoid the rest of the adventure through GUI Land

CREATE TABLE Test (
     ID int
    , Data_Description varchar(50)
)
BULK INSERT Test
FROM '/path/to/file.csv'
WITH
(
    FIELDTERMINATOR = ','
    , ROWTERMINATOR = '\n'
)

Then to stay on topic system.db.runQuery('SELECT * FROM Test') gives us the dataset

Are you going to give an operator access to SQL?? Not the best idea. Plus this post was about Excel to an Ignition table, not necessarily SQL table.

tbf @jshaffer09 asked for “easy”, no need to overcomplicate things :grinning:

1 Like

Hi Jordan
For cell with formula I try manipulate your code to get the result of the formula.

					if cell != None:
						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':
							value = cell.getNumericCellValue()
					else:
						value = None

but the problem is I don’t know what is the return type of formula is. So the .getNumericCellValue() is not always work if formula return string. Do you know how can I get the type of return value?

getCachedFormulaResultType() is what you’re looking for.

Script updated.

1 Like

For project with every limited budget instead of using report module we can create report templates in excel and save them in server hard drive. When user ask for report we can just simply open the workbook and put the result of query into it and save it as bytes and send the file to client.