Create Excel spreadsheet from Business Rules

Works really well. Thank you for sharing your code.

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

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?

Thank you so much MarcusH for the details.