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"