The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

MarcusH's avatar
MarcusH
Valued Contributor
3 years ago
Solved

Create Excel spreadsheet from Business Rules

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 ...
Admin
Spreadsheet
  • Omkareshwar's avatar
    Omkareshwar
    3 years ago

    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's avatar
MarcusH
Valued Contributor
3 years ago

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's avatar
OS_Pizza
Contributor III
3 years ago

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's avatar
    MarcusH
    Valued Contributor
    3 years ago

    How can I use that to create an Excel spreadsheet?

    • OS_Pizza's avatar
      OS_Pizza
      Contributor III
      3 years ago

      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