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
