MarcusH
2 years agoContributor III
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 ...
- 2 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