How to export DataGrid to Excel file in C# and VB.NET

using System;
using System.Windows.Forms;
using System.Data;
using EasyXLS;

namespace WindowsApplication
{
    public class ExportToExcelForm : System.Windows.Forms.Form
    {
        private System.Windows.Forms.DataGrid dataGrid;
        private System.Windows.Forms.Button btnExportToExcel;
    	
        private System.ComponentModel.Container components = null;

        public ExportToExcelForm ()
        {
            InitializeComponent();
            FillDataGrid();
        }
    			
        [STAThread]
        static void Main() 
        {
            Application.Run(new ExportToExcelForm());
        }

        protected void btnExportToExcel_Click(object sender, EventArgs e)
        {
            // Create a DataSet that keeps the DataGrid DataTable
            DataSet dataSet = new DataSet();
            dataSet.Tables.Add(((DataTable)dataGrid.DataSource).Copy());

            // Apply the formatting of the DataGrid to the exported cells
            ExcelAutoFormat xlsAutoformat = new ExcelAutoFormat();
    		
            // Format the cells - DataGrid header
            ExcelStyle xlsStyleHeader = new ExcelStyle();
            xlsStyleHeader.setBackground(dataGrid.HeaderBackColor); 
            xlsStyleHeader.setForeground(dataGrid.HeaderForeColor); 
            if (dataGrid.HeaderFont.Name.Length == 0)
	            xlsStyleHeader.setFont("Times New Roman");
            else
	            xlsStyleHeader.setFont(dataGrid.HeaderFont.Name);
            if (dataGrid.HeaderFont.Size == 0)
	            xlsStyleHeader.setFontSize(12);
            else
	            xlsStyleHeader.setFontSize((int)dataGrid.HeaderFont.Size);
            xlsStyleHeader.setBold(dataGrid.HeaderFont.Bold);
            xlsAutoformat.setHeaderRowStyle(xlsStyleHeader);
    		
            // Format the cells, alternating rows - DataGrid row data
            ExcelStyle xlsEvenRowStripesStyle = new ExcelStyle();
            xlsEvenRowStripesStyle.setBackground(dataGrid.AlternatingBackColor);
            xlsEvenRowStripesStyle.setFont(dataGrid.Font.Name);
            xlsEvenRowStripesStyle.setFontSize((int)dataGrid.Font.Size);
            ExcelStyle xlsOddRowStripesStyle = new ExcelStyle();
            xlsOddRowStripesStyle.setBackground(dataGrid.BackColor);
            xlsOddRowStripesStyle.setFont(dataGrid.Font.Name);
            xlsOddRowStripesStyle.setFontSize((int)dataGrid.Font.Size);
            xlsAutoformat.setEvenRowStripesStyle(xlsEvenRowStripesStyle);
            xlsAutoformat.setOddRowStripesStyle(xlsOddRowStripesStyle);
    		               
            // Export Excel file 
            ExcelDocument workbook = new ExcelDocument();
            workbook.easy_WriteXLSXFile_FromDataSet("C:\\Samples\\DataGrid to Excel.xlsx", dataSet, 
                                                                 xlsAutoformat, "DataGrid");
                
            // Dispose memory
            workbook.Dispose();
        }

        private void FillDataGrid()
        {
            // Create the database connection
            String sConnectionString = "Initial Catalog=Northwind;" +
	            "Data Source=localhost;" +
	            "Integrated Security=SSPI;";
            System.Data.SqlClient.SqlConnection sqlConnection = new 
	            System.Data.SqlClient.SqlConnection(sConnectionString);
            sqlConnection.Open();
            
            // Create the adapter used to fill the dataset
            String sQueryString = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar)+'/' +" 
              + "CAST(Day(ord.OrderDate) AS varchar) + '/' +" + 
              "CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', " +
              " P.ProductName AS 'Product Name', O.UnitPrice AS Price, O.Quantity , " + 
              "O.UnitPrice * O. Quantity AS Value FROM Orders AS ord, [Order Details] AS O, " + 
              "Products AS P WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID";
    		
            System.Data.SqlClient.SqlDataAdapter adp = new 
	            System.Data.SqlClient.SqlDataAdapter(sQueryString, sqlConnection);
            
            // Populate the dataset
            DataSet dataset = new DataSet();
            adp.Fill(dataset);
            
            // Set the data source of the DataGrid
            dataGrid.DataSource = dataset.Tables[0];
        }

        protected override void Dispose( bool disposing )
        {
            if( disposing )
            {
                if (components != null) 
                {
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

        private void InitializeComponent()
        {
            this.dataGrid = new System.Windows.Forms.DataGrid();
            this.btnExportToExcel = new System.Windows.Forms.Button();
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid)).BeginInit();
            this.SuspendLayout();
            // 
            // dataGrid
            // 
            this.dataGrid.AlternatingBackColor = System.Drawing.Color.FromArgb((
                        (System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(192)));
            this.dataGrid.BackColor = System.Drawing.Color.FromArgb((
                        (System.Byte)(255)), ((System.Byte)(224)), ((System.Byte)(192)));
            this.dataGrid.DataMember = "";
            this.dataGrid.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F,
                System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point,
                ((System.Byte)(0)));
            this.dataGrid.ForeColor = System.Drawing.Color.Black;
            this.dataGrid.HeaderBackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), 
	                                        ((System.Byte)(192)), ((System.Byte)(128)));
            this.dataGrid.HeaderForeColor = System.Drawing.Color.Black;
            this.dataGrid.Location = new System.Drawing.Point(24, 24);
            this.dataGrid.Name = "dataGrid";
            this.dataGrid.Size = new System.Drawing.Size(432, 168);
            this.dataGrid.TabIndex = 0;
            // 
            // btnExportToExcel
            // 
            this.btnExportToExcel.Location = new System.Drawing.Point(176, 216);
            this.btnExportToExcel.Name = "btnExportToExcel";
            this.btnExportToExcel.Size = new System.Drawing.Size(96, 23);
            this.btnExportToExcel.TabIndex = 1;
            this.btnExportToExcel.Text = "Export to Excel";
            this.btnExportToExcel.Click += new System.EventHandler(this.btnExportToExcel_Click);
            // 
            // ExportToExcelForm
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(480, 273);
            this.Controls.Add(this.btnExportToExcel);
            this.Controls.Add(this.dataGrid);
            this.Name = "ExportToExcelForm";
            this.Text = "Export DataGrid to Excel in C#";
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid)).EndInit();
            this.ResumeLayout(false);
        }
    }
}

                                                                    

Imports EasyXLS

Public Class ExportToExcelForm
    Inherits System.Windows.Forms.Form
    Friend WithEvents dgDataGrid As System.Windows.Forms.DataGrid
    Friend WithEvents btnExportToExcel As System.Windows.Forms.Button

    Private Sub ExportToExcelForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                                                                                 Handles MyBase.Load
        InitializeComponent()
        FillDataGrid()
    End Sub

    Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                                                                      Handles btnExportToExcel.Click

        ' Create a DataSet that keeps the DataGrid DataTable
        Dim dataSet = New DataSet
        dataSet.Tables.Add((dgDataGrid.DataSource).Copy())

        ' Apply the formatting of the DataGrid to the exported cells
        Dim xlsAutoformat = New ExcelAutoFormat
        ' Format the cells - DataGrid header
        Dim xlsStyleHeader = New ExcelStyle
        xlsStyleHeader.setBackground(dgDataGrid.HeaderBackColor)
        xlsStyleHeader.setForeground(dgDataGrid.HeaderForeColor)
        If (dgDataGrid.HeaderFont.Name.Length = 0) Then
            xlsStyleHeader.setFont("Times New Roman")
        Else
            xlsStyleHeader.setFont(dgDataGrid.HeaderFont.Name)
        End If
        If (dgDataGrid.HeaderFont.Size = 0) Then
            xlsStyleHeader.setFontSize(12)
        Else
            xlsStyleHeader.setFontSize(dgDataGrid.HeaderFont.Size)
        End If
        xlsStyleHeader.setBold(dgDataGrid.HeaderFont.Bold)
        xlsAutoformat.setHeaderRowStyle(xlsStyleHeader)

        ' Format the cells, alternating rows - DataGrid row data
        Dim xlsEvenRowStripesStyle = New ExcelStyle
        xlsEvenRowStripesStyle.setBackground(dgDataGrid.AlternatingBackColor)
        xlsEvenRowStripesStyle.setFont(dgDataGrid.Font.Name)
        xlsEvenRowStripesStyle.setFontSize(dgDataGrid.Font.Size)
        Dim xlsOddRowStripesStyle = New ExcelStyle
        xlsOddRowStripesStyle.setBackground(dgDataGrid.BackColor)
        xlsOddRowStripesStyle.setFont(dgDataGrid.Font.Name)
        xlsOddRowStripesStyle.setFontSize(dgDataGrid.Font.Size)
        xlsAutoformat.setEvenRowStripesStyle(xlsEvenRowStripesStyle)
        xlsAutoformat.setOddRowStripesStyle(xlsOddRowStripesStyle)

        ' Export Excel file 
        Dim workbook = New ExcelDocument
        workbook.easy_WriteXLSXFile_FromDataSet("C:\Samples\DataGrid to Excel.xlsx", dataSet, _
                                                                 xlsAutoformat, "DataGrid")

        ' Dispose memory
        workbook.Dispose()
    End Sub


    Private Sub FillDataGrid()

        ' Create the database connection
        Dim sConnectionString As String = "Initial Catalog=Northwind;"
        sConnectionString += "Data Source=pmoeterm;"
        sConnectionString += "User ID=usr_easyxls;Password=usr_easyxls123"
        Dim sqlConnection As System.Data.SqlClient.SqlConnection = New _
        System.Data.SqlClient.SqlConnection(sConnectionString)
        sqlConnection.Open()

        ' Create the adapter used to fill the dataset
        Dim sQueryString As String = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar)+'/' + "
        sQueryString += "CAST(Day(ord.OrderDate) AS varchar) + '/' + "
        sQueryString += "CAST(year(ord.OrderDate) AS varchar) AS 'Order Date',"
        sQueryString += " P.ProductName AS 'Product Name', O.UnitPrice AS Price, O.Quantity, "
        sQueryString += "O.UnitPrice * O.Quantity AS Value FROM Orders AS ord, "
        sQueryString += "[Order Details] AS O, Products AS P "
        sQueryString += "WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID"
        Dim adp As System.Data.SqlClient.SqlDataAdapter = New _
        System.Data.SqlClient.SqlDataAdapter(sQueryString, sqlConnection)

        ' Populate the dataset
        Dim dataset As DataSet = New DataSet
        adp.Fill(dataset)

        ' Set the data source of the DataGrid
        dgDataGrid.DataSource = dataset.Tables(0)
    End Sub


    Private Sub InitializeComponent()
        Me.dgDataGrid = New System.Windows.Forms.DataGrid
        Me.btnExportToExcel = New System.Windows.Forms.Button
        CType(Me.dgDataGrid, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'dgDataGrid
        '
        Me.dgDataGrid.AlternatingBackColor = System.Drawing.Color.FromArgb(CType(255, Byte), _
                                                            CType(255, Byte), CType(192, Byte))
        Me.dgDataGrid.BackColor = System.Drawing.Color.FromArgb(CType(255, Byte), _
                                                            CType(224, Byte), CType(192, Byte))
        Me.dgDataGrid.DataMember = ""
        Me.dgDataGrid.HeaderBackColor = System.Drawing.Color.FromArgb(CType(255, Byte), _
                                                            CType(192, Byte), CType(128, Byte))
        Me.dgDataGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.dgDataGrid.Location = New System.Drawing.Point(65, 29)
        Me.dgDataGrid.Name = "dgDataGrid"
        Me.dgDataGrid.Size = New System.Drawing.Size(439, 156)
        Me.dgDataGrid.TabIndex = 2
        '
        'btnExportToExcel
        '
        Me.btnExportToExcel.Location = New System.Drawing.Point(240, 216)
        Me.btnExportToExcel.Name = "btnExportToExcel"
        Me.btnExportToExcel.Size = New System.Drawing.Size(96, 23)
        Me.btnExportToExcel.TabIndex = 1
        Me.btnExportToExcel.Text = "Export to Excel"
        '
        'ExportToExcelForm
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(576, 273)
        Me.Controls.Add(Me.btnExportToExcel)
        Me.Controls.Add(Me.dgDataGrid)
        Me.Name = "ExportToExcelForm"
        Me.Text = "Export DataGrid to Excel in VB.NET"
        CType(Me.dgDataGrid, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub
End Class