Updrading Ignition to the latest (8.1.42, as of this writing) will update POI from 3.1.7 to 4.1.2.
When I originally posted this question, it took me awhile to find a solution that worked for me. I have only used it on one project the whole time. My solution was to copy each column in to a dataset from the excel sheet. I created a screen with a table that displays the dataset and a submit button. The submit button has a simple script to insert it into the database table. In the end it was a work around but it worked.
Screen Shot
Script
#imports
import system
#get data from dataset
data = event.source.parent.getComponent('Table').data
for insertrow in range(0,data.getRowCount()):
query = "INSERT INTO PartsLocation(Location) VALUES(?)"
args = [data.getValueAt(insertrow, 0)]
system.db.runPrepUpdate(query, args)
I have upgraded Ignition and now it seems to work... Really strange 
How to link excel to dataset function filepath with the file upload component. Actually i am trying to pass the file path dynamically so user will be able to select the file path
You cannot supply a file path to an Upload component. That would be malware-ish behavior. The user must always control where the file comes from in their system.
2 Likes
nice job @JordanCClark , it works with the example files but iĀ“ve a file with the next sequenece
test.xlsx (10.4 KB)
i try change to diferent formats but i canĀ“t find the issue.
Your first column has differing datatypes. Excel doesn't give a wet slap what you format the cells for.
I have revamped the processing a bit. You can find it on my GitHub page.
Assuming you use the same package structure I show there, the functions of interest will be:
- util.dataset.fromExcel()
- util.dataset.fromExcelBytes() -- useful for using with perspective
- util.dataset.processExceltoDataset()
system.dataset.toDataSet(headers,data) does not like different cellTypes in the same column when it converts to a Dataset. I don't care so much for numbers being numbers until after I'm handling aggregation within the dataset. I came across the same issue you had. Here is how I fixed it.
if cellType == 'NUMERIC':
if DateUtil.isCellDateFormatted(cell):
value = cell.dateCellValue
else:
value = str(cell.getNumericCellValue())
elif cellType == 'STRING':
value = cell.getStringCellValue()
From the Documentation on XSSFCell(POI API Documentation)
XSSF API Documentation
"public java.lang.String getStringCellValue()
Get the value of the cell as a string
For numeric cells we throw an exception. For blank cells we return an empty string. For formulaCells that are not string Formulas, we throw an exception
Returns:
the value of the cell as a string"
Hi,
the following is the data iāve stored in excel -
and i get this error -
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)
system.perspective.print(i)
system.perspective.print(j)
system.perspective.print(cell)
cellType = cell.getCellType().toString()
system.perspective.print(cellType)
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)
fName = 'C:/Program Files/Inductive Automation/Ignition/data/excel/readExcel4.xlsx'
ds1 = excelToDataSet(fName, True)
self.getSibling("Table").props.data = ds1
Havenāt made any changes in the code except adding a few print statements to debugā¦
This happens after accessing the row 2 cells when an empty cell is encountered, not sure what to add/change in the code?
Sorry for the late reply. Spending a few days off for the Thanksgiving holiday.
Can you post the file that youāre having issues with?
2 Likes
Do you mean the Excel file?
test.xlsx (8.7 KB)
Edit: Ran the code for this on your github, i got the right output, with one caveat i.e., it works if none of the cells are Null.
what do i need to change for the code to work even for cells with nothing in them?
Error:
Seems to have trouble with single column datasets in Excel. I updated processExceltoDataset() to handle that.
1 Like
Yess that worked! Thanks a lot! 