To export Gridview data to Excel using devexpress XtraGrid have a look at the sample below.
Samples
C# Sample
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
using System; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; using DevExpress.XtraEditors; using DevExpress.XtraGrid.Columns; namespace de.Fesslersoft.ExportXtraGridToExcel { public partial class ExportForm : Form { private DataTable _dataTable; public ExportForm() { InitializeComponent(); } private void ExportForm_Load(object sender, EventArgs e) { FillDataTable(); gridViewExport.OptionsBehavior.AutoPopulateColumns = true; gridViewExport.OptionsBehavior.ReadOnly = true; gridViewExport.OptionsBehavior.Editable = false; gridControlExport.DataSource = _dataTable; } private void FillDataTable() { //https://documentation.devexpress.com/#WindowsForms/CustomDocument2240 SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder { DataSource = "localhost", IntegratedSecurity = true, InitialCatalog = "Northwind" }; using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString())) { connection.Open(); string queryCommand = "SELECT TOP 100 [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Northwind].[dbo].[Products]"; using (SqlDataAdapter adapter = new SqlDataAdapter(queryCommand, connection)) { _dataTable = new DataTable(); adapter.Fill(_dataTable); } } } private void ExportToExcel() { //https://www.devexpress.com/Support/Center/Question/Details/Q430217 //https://documentation.devexpress.com/#WindowsForms/CustomDocument1874 //storing current layout gridViewExport.SaveLayoutToXml(@"C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml"); foreach (GridColumn column in gridViewExport.Columns) { //make all export columns visible column.Visible = true; } gridViewExport.ExportToXls(@"C:\Users\mf\Desktop\Neuer Ordner\export.xls"); //restoring the layout, the layout file needs to be deleted manually gridViewExport.RestoreLayoutFromXml(@"C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml"); } private void buttonExportToExcel_Click(object sender, EventArgs e) { ExportToExcel(); XtraMessageBox.Show("Export successfull!", this.Text, MessageBoxButtons.OK,MessageBoxIcon.Information,MessageBoxDefaultButton.Button1); } } } |
VB.NET Sample
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
Imports System.Data.SqlClient Imports DevExpress.XtraEditors Imports DevExpress.XtraGrid.Columns Public Partial Class ExportForm Inherits Form Private _dataTable As DataTable Public Sub New() InitializeComponent() End Sub Private Sub ExportForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load FillDataTable() gridViewExport.OptionsBehavior.AutoPopulateColumns = True gridViewExport.OptionsBehavior.[ReadOnly] = True gridViewExport.OptionsBehavior.Editable = False gridControlExport.DataSource = _dataTable End Sub Private Sub FillDataTable() 'https://documentation.devexpress.com/#WindowsForms/CustomDocument2240 Dim connectionStringBuilder As New SqlConnectionStringBuilder() With { _ .DataSource = "localhost", _ .IntegratedSecurity = True, _ .InitialCatalog = "Northwind" _ } Using connection As New SqlConnection(connectionStringBuilder.ToString()) connection.Open() Dim queryCommand As String = "SELECT TOP 100 [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Northwind].[dbo].[Products]" Using adapter As New SqlDataAdapter(queryCommand, connection) _dataTable = New DataTable() adapter.Fill(_dataTable) End Using End Using End Sub Private Sub ExportToExcel() 'https://www.devexpress.com/Support/Center/Question/Details/Q430217 'https://documentation.devexpress.com/#WindowsForms/CustomDocument1874 'storing current layout gridViewExport.SaveLayoutToXml("C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml") For Each column As GridColumn In gridViewExport.Columns 'make all export columns visible column.Visible = True Next gridViewExport.ExportToXls("C:\Users\mf\Desktop\Neuer Ordner\export.xls") 'restoring the layout, the layout file needs to be deleted manually gridViewExport.RestoreLayoutFromXml("C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml") End Sub Private Sub buttonExportToExcel_Click(sender As Object, e As EventArgs) Handles SimpleButton1.Click ExportToExcel() XtraMessageBox.Show("Export successfull!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1) End Sub End Class |
If you have any questions or suggestions feel free to rate this snippet, post a comment or Contact Us via Email.
Related links: