#Region "Create Excel File"
Public Sub CreateExcelFileFromDataTable(ByVal si As SessionInfo, dataTable As DataTable)
'Create a new Excel file
Dim filePath As String = "\\FolderPath\"FileName".xlsx"
BRApi.ErrorLog.LogMessage(si,filePath & dataTable.Rows.Count)
'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 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
Dim cellValue As String = dataRow(column.ColumnName).ToString()
Dim cell As New Cell()
' Check the data type and set the cell data type accordingly
If column.DataType Is GetType(Integer) OrElse column.DataType Is GetType(Long) OrElse column.DataType Is GetType(Double) OrElse column.DataType Is GetType(Decimal) Then
' Handle numeric types
If Not String.IsNullOrEmpty(cellValue) Then
cell.DataType = CellValues.Number
cell.CellValue = New CellValue(cellValue)
Else
' Handle empty numeric cells as blank
cell.DataType = CellValues.Number
cell.CellValue = New CellValue("0")
End If
ElseIf column.DataType Is GetType(DateTime) Then
' Handle DateTime types
Dim dateValue As DateTime
If DateTime.TryParse(cellValue, dateValue) Then
cell.DataType = CellValues.Date
cell.CellValue = New CellValue(dateValue.ToString("yyyy-MM-ddTHH:mm:ss"))
Else
cell.DataType = CellValues.String
cell.CellValue = New CellValue("")
End If
Else
' Default to string for all other data types
cell.DataType = CellValues.String
cell.CellValue = New CellValue(cellValue)
End If
row.AppendChild(cell)
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 = "Data"}
sheets.Append(sheet)
'Save changes
workbookPart.Workbook.Save()
End Using
End Sub
#End Region
