[BUG-15748] Export excel problem in 8.0.8

Duffanator · February 18, 2020, 1:18pm

Hey all,

I just updated my Ignition servers to 8.0.8 from 7.9.12 and now I’m having a problem with the system.dataset.exportExcel() function. I get the following error:

Traceback (most recent call last):
  File "<event:actionPerformed>", line 2, in <module>
  File "<module:shared.genFunctions>", line 10, in dataExport
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetName(XSSFWorkbook.java:906)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:859)
	at com.inductiveautomation.ignition.common.util.excel.adapters.DatasetExcelAdapter.drawSheet(DatasetExcelAdapter.java:46)
	at com.inductiveautomation.ignition.common.util.excel.adapters.DatasetExcelAdapter.drawWorkbook(DatasetExcelAdapter.java:29)
	at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.dataSetToExcelStreaming(DatasetUtilities.java:1097)
	at com.inductiveautomation.factorypmi.application.script.builtin.ClientDatasetUtilities.lambda$exportExcel$0(ClientDatasetUtilities.java:106)
	at com.inductiveautomation.factorypmi.application.script.builtin.FPMIFileUtilities.saveToFile(FPMIFileUtilities.java:162)
	at com.inductiveautomation.factorypmi.application.script.builtin.ClientDatasetUtilities.exportExcel(ClientDatasetUtilities.java:105)
	at com.inductiveautomation.factorypmi.application.script.builtin.ClientDatasetUtilities.exportExcel(ClientDatasetUtilities.java:116)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
java.lang.IllegalArgumentException: java.lang.IllegalArgumentException: The workbook already contains a sheet named 'Dataset 1'

	at org.python.core.Py.JavaError(Py.java:552)
	at org.python.core.Py.JavaError(Py.java:543)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:519)
	at org.python.core.PyObject.__call__(PyObject.java:515)
	at org.python.core.PyObject.__call__(PyObject.java:519)
	at org.python.pycode._pyx84.dataExport$1(<module:shared.genFunctions>:14)
	at org.python.pycode._pyx84.call_function(<module:shared.genFunctions>)
	at org.python.core.PyTableCode.call(PyTableCode.java:171)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:154)
	at org.python.core.PyFunction.__call__(PyFunction.java:423)
	at org.python.pycode._pyx384.f$0(<event:actionPerformed>:2)
	at org.python.pycode._pyx384.call_function(<event:actionPerformed>)
	at org.python.core.PyTableCode.call(PyTableCode.java:171)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1614)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:770)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:725)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:202)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:291)
	at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
	at com.sun.proxy.$Proxy48.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.desktop/java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
	at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
	at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.Component.processEvent(Unknown Source)
	at java.desktop/java.awt.Container.processEvent(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.IllegalArgumentException: The workbook already contains a sheet named 'Dataset 1'
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetName(XSSFWorkbook.java:906)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:859)
	at com.inductiveautomation.ignition.common.util.excel.adapters.DatasetExcelAdapter.drawSheet(DatasetExcelAdapter.java:46)
	at com.inductiveautomation.ignition.common.util.excel.adapters.DatasetExcelAdapter.drawWorkbook(DatasetExcelAdapter.java:29)
	at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.dataSetToExcelStreaming(DatasetUtilities.java:1097)
	at com.inductiveautomation.factorypmi.application.script.builtin.ClientDatasetUtilities.lambda$exportExcel$0(ClientDatasetUtilities.java:106)
	at com.inductiveautomation.factorypmi.application.script.builtin.FPMIFileUtilities.saveToFile(FPMIFileUtilities.java:162)
	at com.inductiveautomation.factorypmi.application.script.builtin.ClientDatasetUtilities.exportExcel(ClientDatasetUtilities.java:105)
	at com.inductiveautomation.factorypmi.application.script.builtin.ClientDatasetUtilities.exportExcel(ClientDatasetUtilities.java:116)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:188)
	... 55 more

Ignition v8.0.8 (b2020021111)
Java: Azul Systems, Inc. 11.0.5

which doesn’t make too much sense. I am just pulling alarm data from my alarm journal and then sending that data to the funtion:

journal = system.alarm.queryJournal(start, end, "Journal", displaypath=[pathFilter], source=[sourceFilter], includeSystem=systemFilter, state=stateList).getDataset()
fileName = event.source.parent.AlarmGroup + "AlarmData_"
shared.genFunctions.dataExport(journal, fileName)

and

def dataExport(tableData, tableName):
	""" This will export data to excel, requires data and the table name for proper export"""
	# Import time module to get current time to create file name.
	from java.net import URLEncoder
	import system
	import time
	ftime = time.strftime("%d%b%Y", time.localtime())
	
	fileName = tableName + ftime + ".xls"
	filePath = system.dataset.exportExcel(fileName, 1, tableData)
	if filePath != None:
		filePath = filePath.replace(' ', '%20')
		filePath = filePath.replace('\\', '/')
		system.net.openURL("file:" + filePath)

did something change with this function? It looks the same in the manual…

PGriffith · February 18, 2020, 9:44pm

The exportExcel (and related) functions got a significant upgrade in 8.0.something, which allows them to output an actual native Excel document. Unfortunately, a few issues slipped through the cracks - they’re actually pretty high on the backlog and should be fixed pretty soon, so likely available within the next few releases.

Duffanator · February 19, 2020, 1:24am

Ok, thank you! I was able to change it to export a CSV file for now as a workaround.