Forum Discussion

franciscoamores's avatar
franciscoamores
Contributor II
2 years ago

Anyone was able to use DocumentFormat.OpenXml.dll from Workspace assembly (v8)?

Hi,

has any one used DocumentFormat.OpenXml.dll coming v8 to create excel docs?

It looks like it is working from standard extender BR once you add reference to DocumentFormat.OpenXml.dll and System.IO.Packaging.dll but it does not work when you do in a file within Workspace assembly as it complains about not finding the referenced System.IO.Packaging.dll even if the dependency is added

 

Thanks

 

10 Replies

  • MarkBird's avatar
    MarkBird
    Contributor III

    Hey Francisco

    Is this specifically an issue that you have noticed following an upgrade to v8?

    We're on 7.3.1 and I have used it successfully. But I did need to add the following to Referenced Assemblies in the Business Rule Properties:

    C:\Program Files\OneStream Software\OneStreamAppRoot\OneStreamApp\bin\DocumentFormat.OpenXml.dll; C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\WindowsBase.dll

    Hope this helps... But if not, I'm keen to see how this gets resolved as it will become an issue for us when we upgrade.

    Mark

     

    • franciscoamores's avatar
      franciscoamores
      Contributor II

      It's a new v8 deployment.

      DocumentFormat.OpenXml.dll for .NET Core has now a dependency on System.IO.Packaging.dll. When creating an excel file it uses class Package in assembly System.IO.Packaging.dll

      We can compile an Extender BR and create an empty excel.

      but if we try to put same in an assembly:

      Ot we are missing something or different versions of DocumentFormat.OpenXml.dll used by assemblies and Extender BR or assembly compiles in sequential order and does not compile in memory the dependencies first or....

      Thanks!

       

       

       

       

      Your additional imports:

       

       

       

      • franciscoamores's avatar
        franciscoamores
        Contributor II

        Workaround so far is to have the excel creation in the extender BR and import the extender br as dependency in the Assembly. then it works. We can call the extender br method from a class in the assembly and the excel gets created...

  • MarcusH's avatar
    MarcusH
    Valued Contributor

    Hi Francisco - did you manage to fix this? I have the same issue but the BR is an Extender already (V8.2.1). Thanks Marcus

    • franciscoamores's avatar
      franciscoamores
      Contributor II

      Hey Marcus,

      I was confirmed as bug and it's supposed to be fixed in 8.2.1. Did you check the release notes? I think there is something about it.

      can you share dummy BR you have the issue with and show the BR refs?

      • MarcusH's avatar
        MarcusH
        Valued Contributor

        Hi Francisco

        Release notes for 8.2 say this:

         

        Workspace Assembly System.IO.Packaging.ddl: To reference the
        System.IO.Packaging /access items in the System.IO.Packaging assembly within a
        Workspace assembly or a business rule and compile, you will need to add the following
        code to reference the System.IO.Packaging.dll from the appropriate location:
        compilerParameters.ReferencedAssemblies.Add (typeof
        (System.IO.Packaging.Package).Assembly.Location); (PF6-4824).

        I have no idea what that means. Do you know where can I access compilerParameters from an Extender BR? I suspect the documentation is only talking about Assemblies.

        I have a test BR Extender that demonstrates the error message:

        Imports System
        Imports System.Data
        Imports System.Data.Common
        Imports System.IO
        Imports System.Collections.Generic
        Imports System.Globalization
        Imports System.Linq
        Imports Microsoft.VisualBasic
        Imports System.Windows.Forms
        Imports OneStream.Shared.Common
        Imports OneStream.Shared.Wcf
        Imports OneStream.Shared.Engine
        Imports OneStream.Shared.Database
        Imports OneStream.Stage.Engine
        Imports OneStream.Stage.Database
        Imports OneStream.Finance.Engine
        Imports OneStream.Finance.Database
        Imports DocumentFormat.OpenXml
        Imports DocumentFormat.OpenXml.Packaging
        Imports DocumentFormat.OpenXml.Spreadsheet
        ' Needs these adding to the Referenced Assemblies on the Properties tab:
        ' DocumentFormat.OpenXml.dll; System.IO.Packaging.dll
        
        Namespace OneStream.BusinessRule.Extender.MHTestExcel
        	Public Class MainClass
        		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
        			Try
        				Select Case args.FunctionType
        					
        					Case Is = ExtenderFunctionType.Unknown
                                Dim thisFileName As String = "MyFile.xlsx"
        
                                ' Create a temporary file
                                Dim filePath As String = String.Format("{0}/{1}", Path.GetTempPath(), thisFileName)
                                
                                ' Create the SpreadsheetDocument object and set its type to Workbook
                                Using document As SpreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)
        
                                    'Create the workbook
                                    Dim workbookPart As WorkbookPart = document.AddWorkbookPart()
                                    workbookPart.Workbook = New Workbook()
                                    
                                End Using
        						
        					Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep
        						
        					Case Is = ExtenderFunctionType.ExecuteExternalDimensionSource
        						'Add External Members
        						Dim externalMembers As New List(Of NameValuePair)
        						externalMembers.Add(New NameValuePair("YourMember1Name","YourMember1Value"))
        						externalMembers.Add(New NameValuePair("YourMember2Name","YourMember2Value"))
        						Return externalMembers
        				End Select
        
        				Return Nothing
        			Catch ex As Exception
        				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
        			End Try
        		End Function		
        
        	End Class
        End Namespace
        

        Referenced Assemblies are:

        DocumentFormat.OpenXml.dll; System.IO.Packaging.dll

        This is the error message:

        Thanks

        Marcus