Create Excel spreadsheet from Business Rules

[MarcusH]

Does anyone know if you can create an Excel spreadsheet from Business Rules? I do not mean a csv file - I need to create multiple sheets. The OpenXMLBuilder object doesn't seem to have anything with writing files. I don't want to use a third-party object as that would require an installation or download.

Thanks for any suggestions.

[OS_Pizza]

Are you looking to extract data to the excel sheet or exporting some report to an excel file ?

[MarcusH]

It's not a report, it's security information that needs to be formatted in a particular way (Security Audit Reports doesn't have a report that will present the required information).

[OS_Pizza]

You can use Streamwrite to write to files.

Ex-

Using sWriter As StreamWriter = File.CreateText(filePath)

Dim line As New Text.StringBuilder

sWriter.WriteLine(line.ToString())

[MarcusH]

How can I use that to create an Excel spreadsheet?

[OS_Pizza]

You can create a .csv file out of this, if the requirement is for just one sheet/page.

			Dim sql As New Text.StringBuilder
			sql.AppendLine("Your query")
				
				Using dbConnFW As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
					Using dt As DataTable = BRApi.Database.ExecuteSql(dbConnFW, sql.ToString, False)
										
						Dim filePath As String = "Myfile.csv"
						
					' loop through Data Table and write to file
						Using sWriter As StreamWriter = File.CreateText(filePath) 

							Dim line As New Text.StringBuilder
							' temp separator
							
							Dim sep As String = ""	
							' write header by looping through columns
							For Each col As DataColumn In dt.Columns 								
								line.Append(sep).Append(col.ColumnName)
								sep = separator								
							Next
							
							sWriter.WriteLine(line.ToString())
							
						'write all the rows, loop through all rows
						For Each row As DataRow In dt.Rows
							' temp separator, clear string builder
							sep = ""
							line.Clear
				
								' loop through each column for current row
								For Each col As DataColumn In dt.Columns
									line.Append(sep).Append(row(col.ColumnName))
									sep = separator
								Next
								sWriter.WriteLine(line.ToString())
							
						Next

						End Using
					End Using
				End Using

[ckattookaran]

You have two options, use openxml or use a 3rd party.  Did you try openxml?

[MarcusH]

I couldn't see any functions in OpenXMLBuilder that allows me to create Excel files. Or are you suggesting using the generic Open XML package? 

[MarcusH]

I have tried:

Imports OpenXmlPowerTools.DocumentBuilder

but I can't see where to go from there

[Omkareshwar · Accepted solution]

You can use this function it takes a data table as parameter and generates an excel file I don't know what your exact requirements are but this can be a good starting point and you can modify it further.

Imports System
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Public Sub CreateExcelFileFromDataTable(dataTable As DataTable)
	'Create a new Excel file
	Dim filePath As String = "C:\example.xlsx"
	'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()
		'Create the worksheet
		Dim worksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
		worksheetPart.Worksheet = New Worksheet()
		'Create the sheet data
		Dim sheetData As SheetData = worksheetPart.Worksheet.AppendChild(New SheetData())
		'Create the header row
		Dim headerRow As Row = New Row()
		'Loop through each column in the DataTable and add it to the header row
		For Each column As DataColumn In dataTable.Columns
			headerRow.AppendChild(New Cell() With {.DataType = CellValues.String, .CellValue = New CellValue(column.ColumnName)})
		Next

		'Add the header row to the sheet data
		sheetData.AppendChild(headerRow)
		'Populate the data rows
		For Each dataRow As DataRow In dataTable.Rows
			Dim row As Row = New Row()
			'Loop through each column in the DataTable and add the corresponding cell value to the current row
			For Each column As DataColumn In dataTable.Columns
				row.AppendChild(New Cell() With {.DataType = CellValues.String, .CellValue = New CellValue(dataRow(column.ColumnName).ToString())})
			Next

			'Add the row to the sheet data
			sheetData.AppendChild(row)
		Next

		'Create the sheets
		Dim sheets As Sheets = workbookPart.Workbook.AppendChild(New Sheets())
		Dim sheet As Sheet = New Sheet() With {.Id = workbookPart.GetIdOfPart(worksheetPart), .SheetId = 1, .Name = "Sheet1"}
		sheets.Append(sheet)
		'Save changes
		workbookPart.Workbook.Save()
	End Using
End Sub

[MarcusH]

Thank you so much for that. I will test it next week and post an update.

[Omkareshwar]

Also add these references in your BR

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

[MarcusH]

Works really well. Thank you for sharing your code.

[MarkBird]

Hey Omkareshwar

I'm trying to run your code, but I get the following error:

Any idea what it could be? The path definitely exists...

Mark

[Omkareshwar]

Hi Mark,

I can't say anything without having a look at your code.

[MarkBird]

It's pretty much copy paste from yours.

It doesn't get past the file creation line (line 60). Code below

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Namespace OneStream.BusinessRule.Extender.TEMP_CREATE_IMPORT_TEMPLATE
	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
				'Parameters
				Dim y As Integer = 2023
				Dim v As String = "YTD"
				Dim s As String = "2023QBR2"
				
				'Loop through base entities and get list of data.
				Dim dt As New DataTable
				
				'Get list of periods.
				Dim tList As New List(Of String)
				tList.Add($"{y}M1")
				tList.Add($"{y}M2")
				tList.Add($"{y}M3")
				tList.Add($"{y}M4")
				tList.Add($"{y}M5")
				tList.Add($"{y}M6")
				tList.Add($"{y}M7")
				tList.Add($"{y}M8")
				tList.Add($"{y}M9")
				tList.Add($"{y}M10")
				tList.Add($"{y}M11")
				tList.Add($"{y}M12")
				
				'Get list of base entities.
				Dim eList As New List(Of String) 
				Dim ePk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Entities")
				Dim ePId As Integer = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Entity, "EMI_SA")
				eList = BRApi.Finance.Members.GetBaseMembers(si, ePk, ePId).OrderBy(Function(r) r.Name).Select(Function(r) r.Name).ToList()
				
				'Create a new Excel file
				Dim filePath As String = "C:/Users/MarkBird/Documents/ExampleExport.xlsx"

				'Create the SpreadsheetDocument object and set its type to Workbook
				Using document As SpreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)

				BRapi.ErrorLog.LogMessage(si, "Here")
					'Create the workbook
					Dim workbookPart As WorkbookPart = document.AddWorkbookPart()
					workbookPart.Workbook = New Workbook()
					
					'Create the sheets
					Dim sheets As Sheets = workbookPart.Workbook.AppendChild(New Sheets())
					
					Dim counter As Integer = 1
					For Each entity In eList
						Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
							For Each tt In tList
								If entity.Substring(0, 1) = "E" Then
									Dim sMethod As String = "{PIB_GRP}{" & entity & "}{}{Local}{" & s & "}{" & tt & "}{" & v & "}{True}{Origin = 'Import' And (Flow = 'None' Or Flow <> 'EndBalLoaded')}"
									Dim dtMethod As DataTable = BRApi.Database.ExecuteMethodCommand(dbConn, XFCommandMethodTypeId.DataUnit, sMethod, "", Nothing).Tables(0)
									If dt Is Nothing Then
										dt = dtMethod
									Else
										dt.Merge(dtMethod)
									End If
								End If
							Next
						End Using
						
						'Pivot OneStream data.
						Dim dtPivot As New DataTable
						dtPivot = Me.PivotDT_FullYear(si, dt, y)
						
						'Create the worksheet
						Dim worksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
						worksheetPart.Worksheet = New Worksheet()

						'Create the sheet data
						Dim sheetData As SheetData = worksheetPart.Worksheet.AppendChild(New SheetData())

						'Create the header row
						Dim headerRow As Row = New Row()

						'Loop through each column in the DataTable and add it to the header row
						For Each column As DataColumn In dtPivot.Columns
							headerRow.AppendChild(New Cell() With {
							.DataType = CellValues.String,
							.CellValue = New CellValue(column.ColumnName)
							})
						Next

						'Add the header row to the sheet data
						sheetData.AppendChild(headerRow)

						'Populate the data rows
						For Each dataRow As DataRow In dtPivot.Rows
							Dim row As Row = New Row()

							'Loop through each column in the DataTable and add the corresponding cell value to the current row
							For Each column As DataColumn In dtPivot.Columns
								row.AppendChild(New Cell() With {
								.DataType = CellValues.String,
								.CellValue = New CellValue(dataRow(column.ColumnName).ToString())
								})
							Next

							'Add the row to the sheet data
							sheetData.AppendChild(row)
						Next

						Dim sheet As Sheet = New Sheet() With {
						.Id = workbookPart.GetIdOfPart(worksheetPart),
						.SheetId = counter,
						.Name = entity
						}
						
						sheets.Append(sheet)
						
						counter += 1
					
					Next
					
					'Save changes
					workbookPart.Workbook.Save()
				End Using
			
				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
		
#Region "PivotDT_FullYear"

		Private Function PivotDT_FullYear(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal y As Integer) As DataTable
			Dim errorlog As String = ""
			Try
				'Pivot periods to columns for full year.
				Dim dtPivot As New DataTable
				dtPivot.Columns.Add("Entity", GetType(String))
				dtPivot.Columns.Add("EntityDesc", GetType(String))
				dtPivot.Columns.Add("Account", GetType(String))
				dtPivot.Columns.Add("AccountDesc", GetType(String))
				dtPivot.Columns.Add("M1", GetType(Decimal))
				dtPivot.Columns.Add("M2", GetType(Decimal))
				dtPivot.Columns.Add("M3", GetType(Decimal))
				dtPivot.Columns.Add("M4", GetType(Decimal))
				dtPivot.Columns.Add("M5", GetType(Decimal))
				dtPivot.Columns.Add("M6", GetType(Decimal))
				dtPivot.Columns.Add("M7", GetType(Decimal))
				dtPivot.Columns.Add("M8", GetType(Decimal))
				dtPivot.Columns.Add("M9", GetType(Decimal))
				dtPivot.Columns.Add("M10", GetType(Decimal))
				dtPivot.Columns.Add("M11", GetType(Decimal))
				dtPivot.Columns.Add("M12", GetType(Decimal))
				
				If dt.Rows.Count > 0 Then
					Dim query As IEnumerable(Of DataRow) = _
						(From row In dt.AsEnumerable()
						Group By gc = New With {
								Key .Entity = row.Field(Of String)("Entity"),
								Key .EntityDesc = row.Field(Of String)("EntityDesc"),
								Key .Account = row.Field(Of String)("Account"),
								Key .AccountDesc = row.Field(Of String)("AccountDesc")
							} Into g = Group
						Select New With {
								.Entity = gc.Entity,
								.EntityDesc = gc.EntityDesc,
								.Account = gc.Account,
								.AccountDesc = gc.AccountDesc,
								.M1 = g.Where(Function (r) r("Time") = y & "M1").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M2 = g.Where(Function (r) r("Time") = y & "M2").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M3 = g.Where(Function (r) r("Time") = y & "M3").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M4 = g.Where(Function (r) r("Time") = y & "M4").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M5 = g.Where(Function (r) r("Time") = y & "M5").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M6 = g.Where(Function (r) r("Time") = y & "M6").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M7 = g.Where(Function (r) r("Time") = y & "M7").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M8 = g.Where(Function (r) r("Time") = y & "M8").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M9 = g.Where(Function (r) r("Time") = y & "M9").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M10 = g.Where(Function (r) r("Time") = y & "M10").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M11 = g.Where(Function (r) r("Time") = y & "M11").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M12 = g.Where(Function (r) r("Time") = y & "M12").Sum(Function (r) Decimal.Parse(r("Amount")))
							}
						).Select(
							Function(r)
								Dim dr As DataRow = dtPivot.NewRow()
								dr("Entity") = r.Entity
								dr("EntityDesc") = r.EntityDesc
								dr("Account") = r.Account
								dr("AccountDesc") = r.AccountDesc
								dr("M1") = r.M1
								dr("M2") = r.M2
								dr("M3") = r.M3
								dr("M4") = r.M4
								dr("M5") = r.M5
								dr("M6") = r.M6
								dr("M7") = r.M7
								dr("M8") = r.M8
								dr("M9") = r.M9
								dr("M10") = r.M10
								dr("M11") = r.M11
								dr("M12") = r.M12
								Return dr
							End Function
						)
					dtPivot = query.CopyToDataTable
				End If
				
				Return dtPivot
					
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			Finally
				BRApi.ErrorLog.LogMessage(si, errorlog)
			End Try	
		End Function
		
#End Region
	End Class
End Namespace

[MarkBird]

Managed to get it working using the following code:

'Create a new Excel file
Dim currentProfileInfo As WorkflowProfileInfo = BRApi.Workflow.Metadata.GetProfile(si, si.WorkflowClusterPk.ProfileKey)
Dim folderPath As String = BRApi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.ApplicationOutgoing, currentProfileInfo)
Dim fileName As String = "ExampleExport.xlsx"
Dim filePath As String = Path.Combine(folderPath, fileName)

Thanks @JackLacava !

[MarcusH]

I think the problem is that you are trying to create a file on your local file system. Whenever I export to Excel I create a temporary file in the temp folder and then copy that file to where it needs to go. I don't know if it's possible to write to your local file system. I have updated your code so that it writes to the fileshare. Look at lines 58 to 79 and 164 to 171.

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Namespace OneStream.BusinessRule.Extender.MH_ExcelTest
	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
				'Parameters
				Dim y As Integer = 2023
				Dim v As String = "YTD"
				Dim s As String = "2023QBR2"
				
				'Loop through base entities and get list of data.
				Dim dt As New DataTable
				
				'Get list of periods.
				Dim tList As New List(Of String)
				tList.Add($"{y}M1")
				tList.Add($"{y}M2")
				tList.Add($"{y}M3")
				tList.Add($"{y}M4")
				tList.Add($"{y}M5")
				tList.Add($"{y}M6")
				tList.Add($"{y}M7")
				tList.Add($"{y}M8")
				tList.Add($"{y}M9")
				tList.Add($"{y}M10")
				tList.Add($"{y}M11")
				tList.Add($"{y}M12")
				
				'Get list of base entities.
				Dim eList As New List(Of String) 
				Dim ePk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Entities")
				Dim ePId As Integer = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Entity, "EMI_SA")
				eList = BRApi.Finance.Members.GetBaseMembers(si, ePk, ePId).OrderBy(Function(r) r.Name).Select(Function(r) r.Name).ToList()
				
				'Create a new Excel file
				' Dim filePath As String = "C:/Temp/ExampleExport.xlsx"
	            Dim currUser As String = si.AuthToken.UserName.Replace(" ", "")		
				Dim thisFileName As String = "ExampleExport.xlsx"
				Dim RootFileExplorerFolder As String = String.Format("Documents/Public", currUser)
				Dim thisExportFolder As String = "ExportData"
				Dim filePath As String = String.Format("{0}/{1}", RootFileExplorerFolder, thisExportFolder)
			
				' Create the folder if it does not exist
				BRApi.FileSystem.CreateFullFolderPathIfNecessary(si, FileSystemLocation.ApplicationDatabase, 
													RootFileExplorerFolder, thisExportFolder)

				Dim fullSaveToFolder As XFFolderEx = BRApi.FileSystem.GetFolder(si, FileSystemLocation.ApplicationDatabase, filePath)
				' Delete the file if it already exists because the OS function is Insert or Update
				If BRApi.FileSystem.DoesFileExist(si, FileSystemLocation.ApplicationDatabase, filePath & "/" & thisFileName) Then
					' BRApi.ErrorLog.LogMessage(si, CurrentPosition)
					BRApi.FileSystem.DeleteFile(si, FileSystemLocation.ApplicationDatabase, filePath & "/" & thisFileName)
				End If

				' Create a temporary file
				Dim tmpfilePath 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(tmpfilePath, SpreadsheetDocumentType.Workbook)

				BRapi.ErrorLog.LogMessage(si, "Here")
					'Create the workbook
					Dim workbookPart As WorkbookPart = document.AddWorkbookPart()
					workbookPart.Workbook = New Workbook()
					
					'Create the sheets
					Dim sheets As Sheets = workbookPart.Workbook.AppendChild(New Sheets())
					
					Dim counter As Integer = 1
					For Each entity In eList
						Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
							For Each tt In tList
								If entity.Substring(0, 1) = "E" Then
									Dim sMethod As String = "{PIB_GRP}{" & entity & "}{}{Local}{" & s & "}{" & tt & "}{" & v & "}{True}{Origin = 'Import' And (Flow = 'None' Or Flow <> 'EndBalLoaded')}"
									Dim dtMethod As DataTable = BRApi.Database.ExecuteMethodCommand(dbConn, XFCommandMethodTypeId.DataUnit, sMethod, "", Nothing).Tables(0)
									If dt Is Nothing Then
										dt = dtMethod
									Else
										dt.Merge(dtMethod)
									End If
								End If
							Next
						End Using
						
						'Pivot OneStream data.
						Dim dtPivot As New DataTable
						dtPivot = Me.PivotDT_FullYear(si, dt, y)
						
						'Create the worksheet
						Dim worksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
						worksheetPart.Worksheet = New Worksheet()

						'Create the sheet data
						Dim sheetData As SheetData = worksheetPart.Worksheet.AppendChild(New SheetData())

						'Create the header row
						Dim headerRow As Row = New Row()

						'Loop through each column in the DataTable and add it to the header row
						For Each column As DataColumn In dtPivot.Columns
							headerRow.AppendChild(New Cell() With {
							.DataType = CellValues.String,
							.CellValue = New CellValue(column.ColumnName)
							})
						Next

						'Add the header row to the sheet data
						sheetData.AppendChild(headerRow)

						'Populate the data rows
						For Each dataRow As DataRow In dtPivot.Rows
							Dim row As Row = New Row()

							'Loop through each column in the DataTable and add the corresponding cell value to the current row
							For Each column As DataColumn In dtPivot.Columns
								row.AppendChild(New Cell() With {
								.DataType = CellValues.String,
								.CellValue = New CellValue(dataRow(column.ColumnName).ToString())
								})
							Next

							'Add the row to the sheet data
							sheetData.AppendChild(row)
						Next

						Dim sheet As Sheet = New Sheet() With {
						.Id = workbookPart.GetIdOfPart(worksheetPart),
						.SheetId = counter,
						.Name = entity
						}
						
						sheets.Append(sheet)
						
						counter += 1
					
					Next
					
					'Save changes
					workbookPart.Workbook.Save()
				End Using

				' Now move the file to the application area of the OS database
				Dim fileBytes As Byte() = File.ReadAllBytes(tmpfilePath)
			    Dim dbFileInfo As New XFFileInfo(FileSystemLocation.ApplicationDatabase, thisFileName, filePath, XFFileType.Unknown)
			    dbFileInfo.ContentFileContainsData = True
			    dbFileInfo.ContentFileExtension = dbFileInfo.Extension

			    Dim dbFile As New XFFile(dbFileInfo, String.Empty, fileBytes)
			    BRApi.FileSystem.InsertOrUpdateFile(si, dbFile)				

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
		
#Region "PivotDT_FullYear"

		Private Function PivotDT_FullYear(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal y As Integer) As DataTable
			Dim errorlog As String = ""
			Try
				'Pivot periods to columns for full year.
				Dim dtPivot As New DataTable
				dtPivot.Columns.Add("Entity", GetType(String))
				dtPivot.Columns.Add("EntityDesc", GetType(String))
				dtPivot.Columns.Add("Account", GetType(String))
				dtPivot.Columns.Add("AccountDesc", GetType(String))
				dtPivot.Columns.Add("M1", GetType(Decimal))
				dtPivot.Columns.Add("M2", GetType(Decimal))
				dtPivot.Columns.Add("M3", GetType(Decimal))
				dtPivot.Columns.Add("M4", GetType(Decimal))
				dtPivot.Columns.Add("M5", GetType(Decimal))
				dtPivot.Columns.Add("M6", GetType(Decimal))
				dtPivot.Columns.Add("M7", GetType(Decimal))
				dtPivot.Columns.Add("M8", GetType(Decimal))
				dtPivot.Columns.Add("M9", GetType(Decimal))
				dtPivot.Columns.Add("M10", GetType(Decimal))
				dtPivot.Columns.Add("M11", GetType(Decimal))
				dtPivot.Columns.Add("M12", GetType(Decimal))
				
				If dt.Rows.Count > 0 Then
					Dim query As IEnumerable(Of DataRow) = _
						(From row In dt.AsEnumerable()
						Group By gc = New With {
								Key .Entity = row.Field(Of String)("Entity"),
								Key .EntityDesc = row.Field(Of String)("EntityDesc"),
								Key .Account = row.Field(Of String)("Account"),
								Key .AccountDesc = row.Field(Of String)("AccountDesc")
							} Into g = Group
						Select New With {
								.Entity = gc.Entity,
								.EntityDesc = gc.EntityDesc,
								.Account = gc.Account,
								.AccountDesc = gc.AccountDesc,
								.M1 = g.Where(Function (r) r("Time") = y & "M1").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M2 = g.Where(Function (r) r("Time") = y & "M2").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M3 = g.Where(Function (r) r("Time") = y & "M3").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M4 = g.Where(Function (r) r("Time") = y & "M4").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M5 = g.Where(Function (r) r("Time") = y & "M5").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M6 = g.Where(Function (r) r("Time") = y & "M6").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M7 = g.Where(Function (r) r("Time") = y & "M7").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M8 = g.Where(Function (r) r("Time") = y & "M8").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M9 = g.Where(Function (r) r("Time") = y & "M9").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M10 = g.Where(Function (r) r("Time") = y & "M10").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M11 = g.Where(Function (r) r("Time") = y & "M11").Sum(Function (r) Decimal.Parse(r("Amount"))),
								.M12 = g.Where(Function (r) r("Time") = y & "M12").Sum(Function (r) Decimal.Parse(r("Amount")))
							}
						).Select(
							Function(r)
								Dim dr As DataRow = dtPivot.NewRow()
								dr("Entity") = r.Entity
								dr("EntityDesc") = r.EntityDesc
								dr("Account") = r.Account
								dr("AccountDesc") = r.AccountDesc
								dr("M1") = r.M1
								dr("M2") = r.M2
								dr("M3") = r.M3
								dr("M4") = r.M4
								dr("M5") = r.M5
								dr("M6") = r.M6
								dr("M7") = r.M7
								dr("M8") = r.M8
								dr("M9") = r.M9
								dr("M10") = r.M10
								dr("M11") = r.M11
								dr("M12") = r.M12
								Return dr
							End Function
						)
					dtPivot = query.CopyToDataTable
				End If
				
				Return dtPivot
					
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			Finally
				BRApi.ErrorLog.LogMessage(si, errorlog)
			End Try	
		End Function
		
#End Region
	End Class
End Namespace

[SeanV]

How do you get these imports to work?

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Is there a DLL or something that needs to be installed? 

[MarkBird]

Hey Sean

You need to add these two references to the business rule:

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

[SeanV]

So I get an error

could not find library C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\WindowsBase.dll

This is an on prem 7.2.2 environment.  Any ideas?

[MarkBird]

You could try C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\WindowsBase.dll

Otherwise you may have to reach out to your IT department

[SeanV]

Got it, it was a path issue.  Thank you

[Sridhar_M]

We are using Extender BR to export data from table to excel with .xls extension. When I try to open that excel I am getting below warning message. I tried options to resolve by changing some registry settings but that's not an option for us as this file is being consumed by downstream processes from S3 bucket. Any thoughts here ?

Regards

Sridhar

[MarcusH]

There are two formats for Excel workbooks: xls and xlsx. xls is the orignal proprietary Excel format. xlsx is a zip file containing XML files. OneStream uses the OpenXML modules that create xlsx files only. So the solution is to rename the extension to xlsx and the error will disappear.

[Sridhar_M]

Thank you so much @MarcusH for the details.