04-27-2023
08:37 AM
- last edited on
05-02-2023
11:13 AM
by
JackLacava
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.
Solved! Go to Solution.
04-27-2023
04:47 PM
- last edited on
09-08-2023
03:45 AM
by
JackLacava
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
04-27-2023 09:29 AM
Are you looking to extract data to the excel sheet or exporting some report to an excel file ?
04-27-2023 09:43 AM
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).
04-27-2023 10:15 AM
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())
04-27-2023 10:27 AM
How can I use that to create an Excel spreadsheet?
04-28-2023 04:26 AM
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
04-27-2023 10:20 AM
You have two options, use openxml or use a 3rd party. Did you try openxml?
04-27-2023 10:27 AM
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?
04-27-2023 10:32 AM
I have tried:
Imports OpenXmlPowerTools.DocumentBuilder
but I can't see where to go from there
04-27-2023
04:47 PM
- last edited on
09-08-2023
03:45 AM
by
JackLacava
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
04-28-2023 03:34 AM
Thank you so much for that. I will test it next week and post an update.
04-28-2023 10:28 AM
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
05-02-2023 04:56 AM
Works really well. Thank you for sharing your code.
07-27-2024 08:59 AM
We're upgrading to OneStream version 8.2.2 and encountering issues with code compilation due to changes in .NET 8. I've managed to fix many errors by updating the Open XML DLL path to:
C:\Program Files\OneStream Software\OneStreamAppRoot\OneStreamApp\DocumentFormat.OpenXml.dll
However, I'm still facing the following error:
"Reference required to assembly 'System.IO.Packaging, Version=4.0.5.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' containing the type 'Package'. Add one to your project."
Has anyone found a solution to this problem?
07-27-2024 09:32 AM
Never mind, I found the System.IO.Packaging.dll at this location, which resolved all the problems:
C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App\8.0.7\System.IO.Packaging.dll
09-07-2023 12:15 PM
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
09-07-2023 12:24 PM
Hi Mark,
I can't say anything without having a look at your code.
09-07-2023 12:29 PM
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
09-08-2023 04:04 AM
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 !
09-08-2023 04:04 AM
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
10-03-2023 03:31 PM
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?
10-04-2023 03:19 AM
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
10-04-2023 08:23 AM
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?
10-04-2023 08:52 AM
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
10-04-2023 10:23 AM
Got it, it was a path issue. Thank you
11-28-2023 04:29 PM
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
11-29-2023 05:28 AM
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.
11-30-2023 10:28 AM - edited 11-30-2023 10:28 AM
Thank you so much @MarcusH for the details.