Export Report as XLS Size Limit?

Gregory_Holmes · August 9, 2023, 8:54pm

I have a report which I can successfully save to PDF via scripting by calling the saveAsPDF function from the Report Viewer. However, when I try saveAsXls i get the following error:

java.lang.IllegalArgumentException: java.lang.IllegalArgumentException: dx1 must be between 0 and 1023, but was: -1

caused by IllegalArgumentException: dx1 must be between 0 and 1023, but was: -1

Is this because I simply have too much data to save as Xls? I have other reports where this function works just fine.

PGriffith · August 9, 2023, 8:59pm

You've got a link or something trying to render off screen?

github.com

cuba-platform/apache-poi/blob/667a58fb053aa3b076f8addcdde922cd5e099596/poi/src/java/org/apache/poi/hssf/usermodel/HSSFClientAnchor.java#L204


      
           * @param x1   the x coordinate within the first cell.
           * @param y1   the y coordinate within the first cell.
           * @param x2   the x coordinate within the second cell.
           * @param y2   the y coordinate within the second cell.
           * @param col1 the column (0 based) of the first cell.
           * @param row1 the row (0 based) of the first cell.
           * @param col2 the column (0 based) of the second cell.
           * @param row2 the row (0 based) of the second cell.
           */
          public void setAnchor(short col1, int row1, int x1, int y1, short col2, int row2, int x2, int y2) {
              checkRange(getDx1(), 0, 1023, "dx1");
              checkRange(getDx2(), 0, 1023, "dx2");
              checkRange(getDy1(), 0, 255, "dy1");
              checkRange(getDy2(), 0, 255, "dy2");
              checkRange(getCol1(), 0, 255, "col1");
              checkRange(getCol2(), 0, 255, "col2");
              checkRange(getRow1(), 0, 255 * 256, "row1");
              checkRange(getRow2(), 0, 255 * 256, "row2");
          
              setCol1(col1);
              setRow1(row1);

The full details of the stacktrace might be helpful (pure luck I was able to find much off the single line exception), but that might be enough of a clue to investigate.

Gregory_Holmes · August 9, 2023, 9:17pm

Here is the full stack trace. I've gone through every component in my report one by one, and there is nothing that is positioned off the edge of the page. Does it have to be within the margin as well? I could have something a pixel or two over the edge if I wasn't careful when lining things up.

Traceback (most recent call last):
  File "<event:actionPerformed>", line 8, in <module>
	at org.apache.poi.hssf.usermodel.HSSFClientAnchor.checkRange(HSSFClientAnchor.java:273)
	at org.apache.poi.hssf.usermodel.HSSFClientAnchor.<init>(HSSFClientAnchor.java:66)
	at com.inductiveautomation.rm.out.RMExcelSheet.addNewShape(RMExcelSheet.java:109)
	at com.inductiveautomation.rm.out.RMExcelWriter.append(RMExcelWriter.java:313)
	at com.inductiveautomation.rm.out.RMExcelWriter.getWorkbook(RMExcelWriter.java:167)
	at com.inductiveautomation.rm.out.RMExcelWriter.getBytes(RMExcelWriter.java:101)
	at com.inductiveautomation.rm.shape.RMDocument.getBytesExcel(RMDocument.java:783)
	at com.inductiveautomation.reporting.components.ReportViewer.lambda$writeBytes$7(ReportViewer.java:1261)
	at com.inductiveautomation.factorypmi.application.script.builtin.FPMIFileUtilities.saveToFile(FPMIFileUtilities.java:162)
	at com.inductiveautomation.reporting.components.ReportViewer.saveAsXls(ReportViewer.java:1338)
	at jdk.internal.reflect.GeneratedMethodAccessor417.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: dx1 must be between 0 and 1023, but was: -1

	at org.python.core.Py.JavaError(Py.java:547)
	at org.python.core.Py.JavaError(Py.java:538)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:192)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:208)
	at org.python.core.PyObject.__call__(PyObject.java:477)
	at org.python.core.PyObject.__call__(PyObject.java:481)
	at org.python.core.PyMethod.__call__(PyMethod.java:141)
	at org.python.pycode._pyx3739.f$0(<event:actionPerformed>:8)
	at org.python.pycode._pyx3739.call_function(<event:actionPerformed>)
	at org.python.core.PyTableCode.call(PyTableCode.java:173)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1687)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:803)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:206)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:299)
	at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
	at com.sun.proxy.$Proxy46.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: dx1 must be between 0 and 1023, but was: -1
	at org.apache.poi.hssf.usermodel.HSSFClientAnchor.checkRange(HSSFClientAnchor.java:273)
	at org.apache.poi.hssf.usermodel.HSSFClientAnchor.<init>(HSSFClientAnchor.java:66)
	at com.inductiveautomation.rm.out.RMExcelSheet.addNewShape(RMExcelSheet.java:109)
	at com.inductiveautomation.rm.out.RMExcelWriter.append(RMExcelWriter.java:313)
	at com.inductiveautomation.rm.out.RMExcelWriter.getWorkbook(RMExcelWriter.java:167)
	at com.inductiveautomation.rm.out.RMExcelWriter.getBytes(RMExcelWriter.java:101)
	at com.inductiveautomation.rm.shape.RMDocument.getBytesExcel(RMDocument.java:783)
	at com.inductiveautomation.reporting.components.ReportViewer.lambda$writeBytes$7(ReportViewer.java:1261)
	at com.inductiveautomation.factorypmi.application.script.builtin.FPMIFileUtilities.saveToFile(FPMIFileUtilities.java:162)
	at com.inductiveautomation.reporting.components.ReportViewer.saveAsXls(ReportViewer.java:1338)
	at jdk.internal.reflect.GeneratedMethodAccessor417.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:190)
	... 50 more

Ignition v8.1.24 (b2023011208)
Java: Azul Systems, Inc. 11.0.16.1

PGriffith · August 9, 2023, 9:24pm

Alright, it's a chart object. May or may not be inside a crosstab, simple table, other table? Maybe check if it's near the edges? I honestly can't really see how it ends up -1 in the first place.

Gregory_Holmes · August 9, 2023, 10:42pm

So the problem is related to the fact that I am scaling x and y to 0.75 in order for the axes (multiple y axes which took up lots of horizontal space) and legend to fit the area better. Evidently, when setting Scale X and Scale Y to anything lower than 1, it simply shrinks the chart relative to it's center point rather than to the top left corner. So the X and Y coordinates stay the same after adjusting the scaling. So to bring it back to the size I want I have to set the x coordinate to negative....

Gregory_Holmes · August 9, 2023, 11:10pm

I've gone through and changed all of my scaling back to 1 (ugly charts now). I am now getting a different error which also makes no sense.

Traceback (most recent call last):
  File "<event:actionPerformed>", line 8, in <module>
	at java.base/jdk.internal.util.Preconditions.outOfBounds(Unknown Source)
	at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Unknown Source)
	at java.base/jdk.internal.util.Preconditions.checkIndex(Unknown Source)
	at java.base/java.util.Objects.checkIndex(Unknown Source)
	at java.base/java.util.ArrayList.get(Unknown Source)
	at com.inductiveautomation.rm.shape.RMCrossTabRow.getCell(RMCrossTabRow.java:53)
	at com.inductiveautomation.rm.shape.RMCrossTab.getCell(RMCrossTab.java:426)
	at com.inductiveautomation.rm.out.RMExcelWriter.appendCrossTable(RMExcelWriter.java:418)
	at com.inductiveautomation.rm.out.RMExcelWriter.append(RMExcelWriter.java:316)
	at com.inductiveautomation.rm.out.RMExcelWriter.addRow(RMExcelWriter.java:388)
	at com.inductiveautomation.rm.out.RMExcelWriter.appendTable(RMExcelWriter.java:351)
	at com.inductiveautomation.rm.out.RMExcelWriter.getWorkbook(RMExcelWriter.java:163)
	at com.inductiveautomation.rm.out.RMExcelWriter.getBytes(RMExcelWriter.java:101)
	at com.inductiveautomation.rm.shape.RMDocument.getBytesExcel(RMDocument.java:783)
	at com.inductiveautomation.reporting.components.ReportViewer.lambda$writeBytes$7(ReportViewer.java:1261)
	at com.inductiveautomation.factorypmi.application.script.builtin.FPMIFileUtilities.saveToFile(FPMIFileUtilities.java:162)
	at com.inductiveautomation.reporting.components.ReportViewer.saveAsXls(ReportViewer.java:1338)
	at jdk.internal.reflect.GeneratedMethodAccessor417.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.IndexOutOfBoundsException: java.lang.IndexOutOfBoundsException: Index 0 out of bounds for length 0

	at org.python.core.Py.JavaError(Py.java:547)
	at org.python.core.Py.JavaError(Py.java:538)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:192)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:208)
	at org.python.core.PyObject.__call__(PyObject.java:477)
	at org.python.core.PyObject.__call__(PyObject.java:481)
	at org.python.core.PyMethod.__call__(PyMethod.java:141)
	at org.python.pycode._pyx4855.f$0(<event:actionPerformed>:8)
	at org.python.pycode._pyx4855.call_function(<event:actionPerformed>)
	at org.python.core.PyTableCode.call(PyTableCode.java:173)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1687)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:803)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:206)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:299)
	at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
	at com.sun.proxy.$Proxy46.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.IndexOutOfBoundsException: Index 0 out of bounds for length 0
	at java.base/jdk.internal.util.Preconditions.outOfBounds(Unknown Source)
	at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Unknown Source)
	at java.base/jdk.internal.util.Preconditions.checkIndex(Unknown Source)
	at java.base/java.util.Objects.checkIndex(Unknown Source)
	at java.base/java.util.ArrayList.get(Unknown Source)
	at com.inductiveautomation.rm.shape.RMCrossTabRow.getCell(RMCrossTabRow.java:53)
	at com.inductiveautomation.rm.shape.RMCrossTab.getCell(RMCrossTab.java:426)
	at com.inductiveautomation.rm.out.RMExcelWriter.appendCrossTable(RMExcelWriter.java:418)
	at com.inductiveautomation.rm.out.RMExcelWriter.append(RMExcelWriter.java:316)
	at com.inductiveautomation.rm.out.RMExcelWriter.addRow(RMExcelWriter.java:388)
	at com.inductiveautomation.rm.out.RMExcelWriter.appendTable(RMExcelWriter.java:351)
	at com.inductiveautomation.rm.out.RMExcelWriter.getWorkbook(RMExcelWriter.java:163)
	at com.inductiveautomation.rm.out.RMExcelWriter.getBytes(RMExcelWriter.java:101)
	at com.inductiveautomation.rm.shape.RMDocument.getBytesExcel(RMDocument.java:783)
	at com.inductiveautomation.reporting.components.ReportViewer.lambda$writeBytes$7(ReportViewer.java:1261)
	at com.inductiveautomation.factorypmi.application.script.builtin.FPMIFileUtilities.saveToFile(FPMIFileUtilities.java:162)
	at com.inductiveautomation.reporting.components.ReportViewer.saveAsXls(ReportViewer.java:1338)
	at jdk.internal.reflect.GeneratedMethodAccessor417.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:190)
	... 50 more

Ignition v8.1.24 (b2023011208)
Java: Azul Systems, Inc. 11.0.16.1

PGriffith · August 9, 2023, 11:32pm

You've got a crosstab, somehow, with zero columns?

Gregory_Holmes · August 17, 2023, 2:45pm

I do not have any crosstabs in this report. I have been through every component in the report by using the navigation tree and no crosstabs exist...

PGriffith · August 17, 2023, 3:50pm

Simple tables are just reworked cross tabs - any of those?

Gregory_Holmes · August 17, 2023, 4:56pm

I have found the problem. I have a simple table inside of a table. This is intentional. I have a Table which does not have structured columns, and I am using it to display a simple table and time series chart. The table has a filter key so I can only display data for certain things. This all works perfectly and exports to pdf just fine and displays fine in the report view component.

I tried deleting the simple table, and it allows me to save as XLS just fine. Also just for laughs I started from scratch and just made a report that had nothing other than a table with a simple table in it and it breaks the save as XLS. It doesn't seem to matter if the simple table is in the header or the details of the table.

PGriffith · August 17, 2023, 5:41pm

I'll make a note to investigate that and see what's going on - thanks for the extra effort!