Re: Create Excel spreadsheet from Business Rules

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

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 !