How to export the WPF DataGrid (SfDataGrid) to excel in VB.NET?
WPF DataGrid (SfDataGrid) provides support to export data to excel. It provides exporting support for grouping, filtering, sorting, paging, unbound rows, stacked headers, merged cells and details view.
The following assemblies needs to be added for exporting to excel.
- Syncfusion.SfGridConverter.WPF
- Syncfusion.XlsIO.Base
Exporting the DataGrid to excel can be performed by using ExportToExcel extension method present in Syncfusion.UI.Xaml.Grid.Converter namespace.
Imports Syncfusion.UI.Xaml.Grid.Converter
Imports Syncfusion.XlsIO
Dim options = New ExcelExportingOptions()
options.ExcelVersion = ExcelVersion.Excel2013
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Excel exporting options
Exporting operation can be customized by passing an instance of ExcelExportingOptions as argument to ExportToExcel method.
Export mode
By default, actual value only will be exported to excel. If you want to export the display text, you need to set ExportMode property as Text.
Dim options = New ExcelExportingOptions()
options.ExportMode = ExportMode.Text
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Exclude columns while exporting
By default, all the columns (including hidden columns) in DataGrid will be exported to Excel. You can exclude some columns while exporting by using the ExcludeColumns field in ExcelExportingOptions.
Dim options = New ExcelExportingOptions()
options.ExcludeColumns.Add("CustomerName")
options.ExcludeColumns.Add("Country")
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Exporting stacked headers
Stacked headers can be exported to excel by enabling ExportStackedHeaders property in ExcelExportingOptions.
Dim options = New ExcelExportingOptions()
options.ExportStackedHeaders = True
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Exporting merged cells
Merged cells can be exported to excel by enabling ExportMergedCells property in ExcelExportingOptions.
Dim options = New ExcelExportingOptions()
options.ExportMergedCells = True
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Export groups with outlines
By default, all the groups in the DataGrid will be exported in expanded state. You can enable outlines in excel based on groups by setting the AllowOutlining property as true in ExcelExportingOptions.
Dim options = New ExcelExportingOptions()
options.AllowOutlining = True
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Exporting unbound rows
Unbound rows can be exported to excel by enabling ExportUnBoundRows in ExcelExportingOptions.
Dim options = New ExcelExportingOptions()
options.ExportUnBoundRows = True
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Changing start row and column index while exporting
You can export the data to specified row index and column index in worksheet, by setting StartRowIndex and StartColumnIndex properties.
Dim options = New ExcelExportingOptions()
options.StartColumnIndex = 3
options.StartRowIndex = 3
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Saving Options
Save directly to file
After exporting to excel, you can save exported workbook directly to file system by using SaveAs method.
Dim options = New ExcelExportingOptions()
options.ExcelVersion = ExcelVersion.Excel2013
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Save as stream
After exporting to excel, you can save exported workbook to stream by using SaveAs method.
Dim options = New ExcelExportingOptions()
options.ExcelVersion = ExcelVersion.Excel2013
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
Dim fileStream As New FileStream("Sample.xlsx", FileMode.Create)
workBook.SaveAs(fileStream)
You can refer XlsIO documentation.
Save using File dialog
After exporting to excel, you can save exported workbook by opening FileDialog.
Dim options = New ExcelExportingOptions()
options.ExcelVersion = ExcelVersion.Excel2013
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
Dim sfd As SaveFileDialog = New SaveFileDialog With {.FilterIndex = 2, .Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"}
If sfd.ShowDialog() = True Then
Using stream As Stream = sfd.OpenFile()
If sfd.FilterIndex = 1 Then
workBook.Version = ExcelVersion.Excel97to2003
ElseIf sfd.FilterIndex = 2 Then
workBook.Version = ExcelVersion.Excel2010
Else
workBook.Version = ExcelVersion.Excel2013
End If
workBook.SaveAs(stream)
End Using
'Message box confirmation to view the created workbook.
If MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) = MessageBoxResult.Yes Then
'Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(sfd.FileName)
End If
End If
Opening exported excel without saving
You can open the exported workbook without saving by using SfSpreadsheet control.
Dim options = New ExcelExportingOptions() Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options) Dim workBook = excelEngine.Excel.Workbooks(0) Dim window1 As New Window() Dim spreadsheet As New SfSpreadsheet() spreadsheet.Open(workBook) window1.Content = spreadsheet
Export DataGrid pages to Excel
While exporting DataGrid with paging, only the current page will be exported to the excel by default. All the pages in the DataGrid can be exported to excel by enabling the ExportAllPages property in ExcelExportingOptions.
Dim options = New ExcelExportingOptions()
options.ExportAllPages = True
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Data from all the pages will be exported to single sheet by default. It is also possible to export each page to different sheets by enabling ExportPageOptions property.
Dim options = New ExcelExportingOptions()
options.ExportAllPages = True
options.ExportPageOptions = ExportPageOptions.ExportToDifferentSheets
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Export DataGrid SelectedItems to Excel
By default, entire grid will be exported to Excel. You can export only the selected items by passing SelectedItems to ExportToExcel method.
Dim options = New ExcelExportingOptions()
Dim excelEngine As New ExcelEngine()
Dim workBook As IWorkbook = excelEngine.Excel.Workbooks.Create()
workBook.Worksheets.Create()
dataGrid.ExportToExcel(dataGrid.SelectedItems, options, workBook.Worksheets(0))
workBook.Version = ExcelVersion.Excel2013
workBook.SaveAs("Sample.xlsx")
Row Height and Column Width customization
After exporting data to excel, you can set different row height and column width for the columns based on your requirement. You can refer here for more information.
Dim options = New ExcelExportingOptions()
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.Worksheets(0).SetRowHeight(2, 50)
workBook.Worksheets(0).SetColumnWidth(2, 50)
workBook.SaveAs("Sample.xlsx")
Styling cells based on CellType in Excel
You can customize the cell styles based on CellType by using ExportingEventHandler.
Dim options = New ExcelExportingOptions()
options.ExportingEventHandler = AddressOf ExportingHandler
options.AllowOutlining = True
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Private Shared Sub ExportingHandler(ByVal sender As Object, ByVal e As GridExcelExportingEventArgs)
If e.CellType = ExportCellType.HeaderCell Then
e.CellStyle.BackGroundBrush = New SolidColorBrush(Colors.LightPink)
e.CellStyle.ForeGroundBrush = New SolidColorBrush(Colors.White)
e.Handled = True
ElseIf e.CellType = ExportCellType.RecordCell Then
e.CellStyle.BackGroundBrush = New SolidColorBrush(Colors.LightSkyBlue)
e.Handled = True
ElseIf e.CellType = ExportCellType.GroupCaptionCell Then
e.CellStyle.BackGroundBrush = New SolidColorBrush(Colors.Wheat)
e.Handled = True
End If
End Sub
Cell customization in Excel while exporting
You can customize the cells by setting CellsExportingEventHandler in ExcelExportingOptions.
Customize cell value while exporting
You can customize the call values while exporting to excel by using CellsExportingEventHandler and ExcelExportingOptions.
Dim options = New ExcelExportingOptions()
options.CellsExportingEventHandler = AddressOf CellExportingHandler
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Private Shared Sub CellExportingHandler(ByVal sender As Object, ByVal e As GridCellExcelExportingEventArgs)
If e.CellType = ExportCellType.RecordCell AndAlso e.ColumnName = "UnitsInStock" Then
If e.CellValue = 0 Then
e.Range.Cells(0).Value = "Stock is not available"
Else
e.Range.Cells(0).Value = "Stock is available"
End If
e.Handled = True
End If
End Sub
Changing row style in excel based on data
You can customize the rows based on the record values by using CellsExportingEventHandler.
Dim options = New ExcelExportingOptions()
options.CellsExportingEventHandler = AddressOf CellExportingHandler
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Private Shared Sub CellExportingHandler(ByVal sender As Object, ByVal e As GridCellExcelExportingEventArgs)
If e.CellType = ExportCellType.RecordCell Then
If (TryCast(e.NodeEntry, Products)).SupplierID > 3 Then
e.Range.CellStyle.ColorIndex = ExcelKnownColors.Green
e.Range.CellStyle.Font.Color = ExcelKnownColors.White
End If
End If
End Sub
Customize the cells based on Column Name
You can customize the cells based on GridCellExcelExportingEventArgs.ColumnName property in the CellsExportingEventHandler.
Dim options = New ExcelExportingOptions()
options.CellsExportingEventHandler = AddressOf CellExportingHandler
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Private Shared Sub CellExportingHandler(ByVal sender As Object, ByVal e As GridCellExcelExportingEventArgs)
If e.ColumnName <> "SupplierID" Then
Return
End If
e.Range.CellStyle.Font.Size = 12
e.Range.CellStyle.Font.Color = ExcelKnownColors.Pink
e.Range.CellStyle.Font.FontName = "Segoe UI"
End Sub
Exporting DetailsView
By default, DetailsViewDataGrid will be exported to Excel. You can customize its exporting operation by using ChildExportingEventHandler.
Excluding DetailsViewDataGrid while exporting
You can exclude particular DetailsViewDataGrid while exporting, by using the ChildExportingEventHandler and GridChildExportingEventArgs.Cancel .
Dim options = New ExcelExportingOptions()
options.ChildExportingEventHandler = AddressOf ChildExportingHandler
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Private Shared Sub ChildExportingHandler(ByVal sender As Object, ByVal e As GridChildExportingEventArgs)
Dim recordEntry = TryCast(e.NodeEntry, RecordEntry)
If (TryCast(recordEntry.Data, OrderInfo)).OrderID = 10002 Then
e.Cancel = True
End If
End Sub
Excluding DetailsViewDataGrid columns from exporting
You can exclude DetailsViewDataGrid columns while exporting, by using ChildExportingEventHandler and GridChildExportingEventArgs.ExcludeColumns.
Customizing DetailsViewDataGrid cells
Like parent DataGrid, You can customize the DetailsViewDataGrid cells also by using CellsExportingEventHandler. Based on GridCellExcelExportingEventArgs.GridViewDefinition property, you can identify the particular DetailsViewDataGrid and customize it.
Dim options = New ExcelExportingOptions()
options.CellsExportingEventHandler = AddressOf CellExportingHandler
Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options)
Dim workBook = excelEngine.Excel.Workbooks(0)
workBook.SaveAs("Sample.xlsx")
Private Shared Sub CellExportingHandler(ByVal sender As Object, ByVal e As GridCellExcelExportingEventArgs)
If e.GridViewDefinition Is Nothing OrElse e.GridViewDefinition.RelationalColumn <> "OrderDetails" Then
Return
End If
If e.ColumnName = "OrderID" Then
e.Range.CellStyle.Font.Size = 12
e.Range.CellStyle.Font.Color = ExcelKnownColors.Blue
e.Range.CellStyle.Font.FontName = "Segoe UI"
End If
End Sub
Take a moment to peruse the documentation, where you can find more about exporting DataGrid to excel.
Please refer this link to know about the essential features of Syncfusion WPF DataGrid.
View WPF DataGrid Exporting Demo in GitHub
