Error exporting selected table lines to Excel

Archived Topics
1

I have an fpmi table that the users select rows from and export them to Excel. I sometimes get an error, which I think may be because some of the fields are null and I can’t seem to get this to work.

Here is what I have:

table = event.source.parent.getComponent("Table")
rows = table.getSelectedRows()
headers = ["ID","Code","DtFilled","Gen","FermTank","DtReck","pH","CellCt","Trub","Viability"]
data = []
line = []
pdata = fpmi.db.toPyDataSet(table.data)
for row in rows:
	for col in range(table.data.columnCount):
		line.append(table.data.getValueAt(row,col))
	data.append(line)
	line = []
exportds = fpmi.db.toDataSet(headers,data)
fpmi.dataset.exportExcel("YeastData",1,exportds)

There are actually 18 columns in the dataset, I just shorted the header listing in the code here.
The error I get varies as to line and column reporting the error depending on what rows I selected.

ERROR - Error executing script for event: mouseClicked
on component: Table. [AWT-EventQueue-0]
Traceback (innermost last):
File “event:mouseClicked”, line 13, in ?
TypeError: Unable to convert row 4, column 5 to type class java.lang.String

at org.python.core.Py.TypeError(Py.java)
at com.inductiveautomation.factorypmi.application.script.builtin.DatasetUtilities.toDataSet(DatasetUtilities.java:177)
at com.inductiveautomation.factorypmi.application.script.builtin.DBUtilities.toDataSet(DBUtilities.java:204)
at sun.reflect.GeneratedMethodAccessor584.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)
at org.python.core.PyObject.__call__(PyObject.java)
at org.python.core.PyObject.invoke(PyObject.java)
at org.python.pycode._pyx108.f$0(<event:mouseClicked>:13)
at org.python.pycode._pyx108.call_function(<event:mouseClicked>)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyCode.call(PyCode.java)
at org.python.core.Py.runCode(Py.java)
at com.inductiveautomation.factorypmi.application.script.ScriptManager.runCode(ScriptManager.java:245)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:145)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:287)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
at $Proxy1.mouseClicked(Unknown Source)
at com.inductiveautomation.factorypmi.application.components.util.EventDelegateDispatcher$MouseEventDispatcher.mouseClicked(EventDelegateDispatcher.java:94)
2

Yes, I think you’re right - null values are throwing it off.
fpmi.dataset.toDataSet() uses the types of the values in the first row to intuit the column types. If any values in the first row are null, it guesses the type, but clearly it is guessing wrong.

I think that this could be improved so that column types are defined by the first non-null value encountered in each column. I’ve put this in for 3.3.1.

The workaround would be to “seed” the data list with a fake row with all non-null values of the proper type, and then pop that first row off before you convert data into pdata.

3

Uh, scratch that. That clearly won't work. Sorry, wasn't thinking straight.

4

Thanks anyway for the attempt. I should mention that this is not something I have to impliment this week as the user can export the entire table and take just what they want from the spreadsheet itself, so I can wait for the release of 3.3.1.