Lieven De Foor (MIPS) asked a question.

June 1, 2021 at 1:00 PM
Has anyone successfully used Open XML SDK from ABL?

Hi,

 

I'm trying to get the Microsoft Open XML SDK running in ABL.

I've downloaded the nuget package here, extracted the .NET 4.6 DocumentFormat.OpenXml.dll assembly from it and added it to a new empty workspace/project.

While the assembly seems to be found for code completion and adding a USING statement, not even the simplest statement is compiling:

  1. USING DocumentFormat.OpenXml.Packaging.SpreadsheetDocument FROM ASSEMBLY.
  2.  
  3. DEFINE VARIABLE spreadsheetDocument AS SpreadsheetDocument NO-UNDO.

Trying the same with the OEDT plugin active even crashes eclipse while applying the OEDT facet/building the assembly model.

 

Has anyone successfully used the Open XML SDK from ABL and if so how did you succeed?

  • 10 answers
  • 721 views

  • Neil Treeby (D&H Distributing)

    As a short answer, no.

     

    I assume the interest in Open XML is because COM is no longer reliable (or just plain doesn't work) on PASOE? Sadly, having looked at Open XML, I can see why COM is still very popular. Open XML is not easy to work with.

  • Lars Neumeier (Customer)

    It looks like, that the .net bridge has a problem with this dll. Browsing through the "Class Browser" View for this assembly results in a jvm crash:

     

    1. Current thread (0x00000208ab9de800): JavaThread "main" [_thread_in_native, id=5460, stack(0x000000f39f200000,0x000000f39f300000)]
    2.  
    3. Stack: [0x000000f39f200000,0x000000f39f300000], sp=0x000000f39f2fc9c0, free space=1010k
    4. Native frames: (J=compiled Java code, j=interpreted, Vv=VM code, C=native code)
    5. C [oeacb.dll+0x46eb]
    6.  
    7. Java frames: (J=compiled Java code, j=interpreted, Vv=VM code)
    8. j com.openedge.core.otm.OTM.loadAssemblyType(Ljava/lang/String;)Z+0
    9. j com.openedge.core.otm.OTM.loadTypeInfo(Lcom/openedge/core/otm/OBTypeInfo;)Z+65
    10. j com.openedge.core.otm.OBTypeInfo.loadTypeInfo()Z+100
    11. j com.openedge.core.otm.OBTypeInfo.getConstructors()[Lcom/openedge/core/ooabl/model/IMethodInfo;+1
    12. j com.openedge.pdt.text.objectbrowser.OBContentProvider.getOBMemberNodes(Lcom/openedge/core/otm/OBTypeInfo;)[Lcom/openedge/pdt/text/objectbrowser/OBMemberNode;+9
    13. j com.openedge.pdt.text.objectbrowser.OBContentProvider.getElements(Ljava/lang/Object;)[Ljava/lang/Object;+12
    14. j org.eclipse.jface.viewers.StructuredViewer.getRawChildren(Ljava/lang/Object;)[Ljava/lang/Object;+20
    15. j org.eclipse.jface.viewers.ColumnViewer.getRawChildren(Ljava/lang/Object;)[Ljava/lang/Object;+12
    16. j org.eclipse.jface.viewers.AbstractTreeViewer.getRawChildren(Ljava/lang/Object;)[Ljava/lang/Object;+55
    17. j org.eclipse.jface.viewers.TreeViewer.getRawChildren(Ljava/lang/Object;)[Ljava/lang/Object;+14
    18. j org.eclipse.jface.viewers.StructuredViewer.getFilteredChildren(Ljava/lang/Object;)[Ljava/lang/Object;+2
    19. j org.eclipse.jface.viewers.AbstractTreeViewer.getSortedChildren(Ljava/lang/Object;)[Ljava/lang/Object;+2
    20. j org.eclipse.jface.viewers.AbstractTreeViewer.createChildren(Lorg/eclipse/swt/widgets/Widget;Z)V+176
    21. j org.eclipse.jface.viewers.TreeViewer.createChildren(Lorg/eclipse/swt/widgets/Widget;Z)V+138
    22. j org.eclipse.jface.viewers.AbstractTreeViewer.createChildren(Lorg/eclipse/swt/widgets/Widget;)V+3
    23. j org.eclipse.jface.viewers.AbstractTreeViewer.internalInitializeTree(Lorg/eclipse/swt/widgets/Control;)V+2
    24. j org.eclipse.jface.viewers.TreeViewer.internalInitializeTree(Lorg/eclipse/swt/widgets/Control;)V+33
    25. j org.eclipse.jface.viewers.AbstractTreeViewer.lambda$1()V+25
    26. j org.eclipse.jface.viewers.AbstractTreeViewer$$Lambda$509.run()V+4
    27. j org.eclipse.jface.viewers.StructuredViewer.preservingSelection(Ljava/lang/Runnable;Z)V+26
    28. j org.eclipse.jface.viewers.TreeViewer.preservingSelection(Ljava/lang/Runnable;Z)V+29
    29. j org.eclipse.jface.viewers.StructuredViewer.preservingSelection(Ljava/lang/Runnable;)V+3
    30. j org.eclipse.jface.viewers.AbstractTreeViewer.inputChanged(Ljava/lang/Object;Ljava/lang/Object;)V+7
    31. j org.eclipse.jface.viewers.ContentViewer.setInput(Ljava/lang/Object;)V+72
    32. j org.eclipse.jface.viewers.StructuredViewer.setInput(Ljava/lang/Object;)V+33
    33. j com.openedge.pdt.text.objectbrowser.ObjectBrowserForm.setMemberPaneTreeViewer(Ljava/lang/Object;[Ljava/lang/Object;[Ljava/lang/Object;Z)V+27
    34. j com.openedge.pdt.text.objectbrowser.ObjectBrowserForm$6.selectionChanged(Lorg/eclipse/jface/viewers/SelectionChangedEvent;)V+150
    35. J 15543 c1 org.eclipse.jface.viewers.Viewer$1.run()V (14 bytes) @ 0x00000208b696c744 [0x00000208b696c640+0x0000000000000104]
    36. J 14796 c1 org.eclipse.jface.util.SafeRunnable.run(Lorg/eclipse/core/runtime/ISafeRunnable;)V (10 bytes) @ 0x00000208b4c6ca8c [0x00000208b4c6c740+0x000000000000034c]
    37. j org.eclipse.jface.viewers.Viewer.fireSelectionChanged(Lorg/eclipse/jface/viewers/SelectionChangedEvent;)V+31
    38. j org.eclipse.jface.viewers.StructuredViewer.updateSelection(Lorg/eclipse/jface/viewers/ISelection;)V+12
    39. j org.eclipse.jface.viewers.StructuredViewer.handleSelect(Lorg/eclipse/swt/events/SelectionEvent;)V+21
    40. j org.eclipse.jface.viewers.StructuredViewer$4.widgetSelected(Lorg/eclipse/swt/events/SelectionEvent;)V+15
    41. j org.eclipse.jface.util.OpenStrategy.fireSelectionEvent(Lorg/eclipse/swt/events/SelectionEvent;)V+41
    42. J 13300 c1 org.eclipse.jface.util.OpenStrategy$1.handleEvent(Lorg/eclipse/swt/widgets/Event;)V (579 bytes) @ 0x00000208b653ed6c [0x00000208b653e820+0x000000000000054c]
    43. J 13301 c2 org.eclipse.swt.widgets.EventTable.sendEvent(Lorg/eclipse/swt/widgets/Event;)V (592 bytes) @ 0x00000208bc581858 [0x00000208bc5817c0+0x0000000000000098]
    44. J 15483 c2 org.eclipse.swt.widgets.Display.runDeferredEvents()Z (109 bytes) @ 0x00000208bc6e1784 [0x00000208bc6e15a0+0x00000000000001e4]
    45. J 15508 c2 org.eclipse.swt.widgets.Display.readAndDispatch()Z (94 bytes) @ 0x00000208bc6e91c4 [0x00000208bc6e8f60+0x0000000000000264]
    46. j org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$5.run()V+564
    47. j org.eclipse.core.databinding.observable.Realm.runWithDefault(Lorg/eclipse/core/databinding/observable/Realm;Ljava/lang/Runnable;)V+12
    48. j org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(Lorg/eclipse/e4/ui/model/application/MApplicationElement;Lorg/eclipse/e4/core/contexts/IEclipseContext;)Ljava/lang/Object;+57
    49. j org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(Lorg/eclipse/e4/ui/model/application/MApplicationElement;)V+20
    50. j org.eclipse.ui.internal.Workbench.lambda$3(Lorg/eclipse/swt/widgets/Display;Lorg/eclipse/ui/application/WorkbenchAdvisor;[I)V+452
    51. j org.eclipse.ui.internal.Workbench$$Lambda$102.run()V+12
    52. j org.eclipse.core.databinding.observable.Realm.runWithDefault(Lorg/eclipse/core/databinding/observable/Realm;Ljava/lang/Runnable;)V+12
    53. j org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Lorg/eclipse/swt/widgets/Display;Lorg/eclipse/ui/application/WorkbenchAdvisor;)I+16
    54. j org.eclipse.ui.PlatformUI.createAndRunWorkbench(Lorg/eclipse/swt/widgets/Display;Lorg/eclipse/ui/application/WorkbenchAdvisor;)I+2
    55. j org.eclipse.ui.internal.ide.application.IDEApplication.start(Lorg/eclipse/equinox/app/IApplicationContext;)Ljava/lang/Object;+113
    56. j org.eclipse.equinox.internal.app.EclipseAppHandle.run(Ljava/lang/Object;)Ljava/lang/Object;+138
    57. j org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(Ljava/lang/Object;)Ljava/lang/Object;+85
    58. j org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(Ljava/lang/Object;)Ljava/lang/Object;+82
    59. j org.eclipse.core.runtime.adaptor.EclipseStarter.run(Ljava/lang/Object;)Ljava/lang/Object;+99
    60. j org.eclipse.core.runtime.adaptor.EclipseStarter.run([Ljava/lang/String;Ljava/lang/Runnable;)Ljava/lang/Object;+132
    61. v ~StubRoutines::call_stub
    62. j jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Ljava/lang/reflect/Method;Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;+0 java.base@11.0.10
    63. j jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;+100 java.base@11.0.10
    64. j jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;+6 java.base@11.0.10
    65. j java.lang.reflect.Method.invoke(Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;+59 java.base@11.0.10
    66. j org.eclipse.equinox.launcher.Main.invokeFramework([Ljava/lang/String;[Ljava/net/URL;)V+201
    67. j org.eclipse.equinox.launcher.Main.basicRun([Ljava/lang/String;)V+159
    68. j org.eclipse.equinox.launcher.Main.run([Ljava/lang/String;)I+4
    69. j org.eclipse.equinox.launcher.Main.main([Ljava/lang/String;)V+10
    70. v ~StubRoutines::call_stub
    71.  
    72. siginfo: EXCEPTION_ACCESS_VIOLATION (0xc0000005), writing address 0x0000000000000018
    73.  
    74.  
    75. Register to memory mapping:
    76.  
    77. RIP=0x00007ffac53a46eb oeacb.dll
    78. RAX=0x00000208d639f2b0 points into unknown readable memory: 0x00000208d0b7b3d4 | d4 b3 b7 d0 08 02 00 00
    79. RBX=0x0 is NULL
    80. RCX=0x00000208d0b7b3e4 points into unknown readable memory: 00 00 00 00
    81. RDX=0x00000000054b30cc is an unknown value
    82. RSP=0x000000f39f2fc9c0 is pointing into the stack for thread: 0x00000208ab9de800
    83. RBP=0x000000f39f2fcb20 is pointing into the stack for thread: 0x00000208ab9de800
    84. RSI=0x00000208ceda69a0 points into unknown readable memory: 0x00007ffac53b24e8 | e8 24 3b c5 fa 7f 00 00
    85. RDI=0x0000000000000041 is an unknown value
    86. R8 =0x0 is NULL
    87. R9 =0x0 is NULL
    88. R10=0x0000000000000080 is an unknown value
    89. R11=0x00000208d639f2b0 points into unknown readable memory: 0x00000208d0b7b3d4 | d4 b3 b7 d0 08 02 00 00
    90. R12=0x00000208dbae5298 points into unknown readable memory: 0x00007ffabdbfc408 | 08 c4 bf bd fa 7f 00 00
    91. R13={method} {0x00000208d86bb6c8} 'loadAssemblyType' '(Ljava/lang/String;)Z' in 'com/openedge/core/otm/OTM'
    92. R14=0x0 is NULL
    93. R15=0x00000208d102c7f0 points into unknown readable memory: 0x00007ffabfa74720 | 20 47 a7 bf fa 7f 00 00
    94.  

     

  • Tom Bergman (Customer)

    Two things come to mind.

    1) I had a problem with a different MS .net DLL downloaded from Nuget. The solution to my problem was provided by Progress tech support and is referenced in this kbase. https://community.progress.com/s/article/Unable-to-use-classes-from-Microsoft-SqlServer-SqlManagementObjects-in-Developer-Studio

     

    2) Once you get it working and waste a lot of time testing it out, you will soon discover that the use of this DLL requires access to generic methods that Progress can't handle. It's been a while but if I recall, statements like this are the problem. spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>()

     

    I gave up

  • Lieven De Foor (MIPS)

    If we can get the assemblies using the KB articles approach we might be able to create some helper class in C# to provide typed versions of the most common generic method we would like to use.

    So instead of writing

    1. spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>()

    we might be able to do:

    1. OpenXmlHelper:GetFirstChildSheet(workBook).

     

    • Lieven De Foor (MIPS)

      We're not looking for a commercial solution at the moment.

      We're currently looking at ClosedXML which might be an alternative (and uses OpenXML under the hood).

    • Marko Myllymäki (Customer)

      I also recommend EPPlus. I've been using version 4 for many years, it has been working fine and it's easy to use.