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

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

namespace WindowsFormsApplication
{
    public partial class ExportToExcelForm : Form
    {
        public ExportToExcelForm()
        {
            InitializeComponent();
            FillDataGridView();
        }
        
        private void btnExportToExcel_Click(object sender, EventArgs e)
        {
            // Create an instance of the class that exports Excel files, having one sheet
            ExcelDocument workbook = new ExcelDocument(1);
            
            // Set sheet name
            ExcelWorksheet xlsWorksheet = (ExcelWorksheet)workbook.easy_getSheetAt(0);
            xlsWorksheet.setSheetName("DataGridView");
                            
            // Get the sheet table that stores the data
            ExcelTable xlsTable = xlsWorksheet.easy_getExcelTable();
            int tableRow = 0;
            
            // Export DataGridView header if the header is visible
            if (dataGridView.ColumnHeadersVisible)
            {
                // Create the formatting style for the header
                ExcelStyle xlsStyleHeader = new ExcelStyle();
                xlsStyleHeader.setBackground(dataGridView.ColumnHeadersDefaultCellStyle.BackColor);
                xlsStyleHeader.setForeground(dataGridView.ColumnHeadersDefaultCellStyle.ForeColor);
                
                // Add data in cells for header
                for (int column = 0; column < dataGridView.Columns.Count; column++)
                {
                    xlsTable.easy_getCell(tableRow, column).setValue(
                                          dataGridView.Columns[column].HeaderText);
                    xlsTable.easy_getCell(tableRow, column).setStyle(xlsStyleHeader);
                }
                tableRow++;
            }
            
            // Create a formatting style for cells
            ExcelStyle xlsStyleCell = new ExcelStyle();
            xlsStyleCell.setBackground(dataGridView.DefaultCellStyle.BackColor);
            xlsStyleCell.setForeground(dataGridView.DefaultCellStyle.ForeColor);
            
            // Add data in cells
            for (int row = 0; row < dataGridView.Rows.Count-1; row++)
            {
                for (int column = 0; column < dataGridView.Columns.Count; column++)
                {
                    xlsTable.easy_getCell(tableRow, column).setValue(
                                          dataGridView.Rows[row].Cells[column].Value.ToString());
                    xlsTable.easy_getCell(tableRow, column).setStyle(xlsStyleCell);
                }
                tableRow++;
            }
            
            // Export Excel file
            workbook.easy_WriteXLSXFile("C:\\Samples\\Export To Excel.xlsx");
        }
        
        private void FillDataGridView()
        {
            // 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 DataGridView
            dataGridView.DataSource = dataset.Tables[0];
        }
    }
}
                                                                    

Imports EasyXLS

Public Class ExportToExcelForm

    Private Sub ExportToExcelForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                                                                                 Handles MyBase.Load
        FillDataGridView()
    End Sub
    
    Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                                                                      Handles btnExportToExcel.Click
        ' Create an instance of the class that exports Excel files, having one sheet
        Dim workbook As New ExcelDocument(1)
        
        ' Set sheet name
        workbook.easy_getSheetAt(0).setSheetName("DataGridView")
        
        ' Get the sheet table that stores the data
        Dim xlsTab As ExcelWorksheet = workbook.easy_getSheetAt(0)
        Dim xlsTable = xlsTab.easy_getExcelTable()
        Dim tableRow = 0
        
        ' Export DataGridView header if the header is visible
        If (dataGridView.ColumnHeadersVisible) Then
        
            ' Create the formatting style for the header
            Dim xlsStyleHeader As New ExcelStyle()
            xlsStyleHeader.setBackground(dataGridView.ColumnHeadersDefaultCellStyle.BackColor)
            xlsStyleHeader.setForeground(dataGridView.ColumnHeadersDefaultCellStyle.ForeColor)
            
            ' Add data in cells for header
            For column As Integer = 0 To dataGridView.Columns.Count() - 1
                xlsTable.easy_getCell(tableRow, column).setValue( _
                                      dataGridView.Columns(column).HeaderText)
                xlsTable.easy_getCell(tableRow, column).setStyle(xlsStyleHeader)
            Next
            tableRow = tableRow + 1
        End If
        
        ' Create a formatting style for cells
        Dim xlsStyleCell As New ExcelStyle()
        xlsStyleCell.setBackground(dataGridView.DefaultCellStyle.BackColor)
        xlsStyleCell.setForeground(dataGridView.DefaultCellStyle.ForeColor)
        
        ' Add data in cells
        For row As Integer = 0 To dataGridView.Rows.Count() - 2
            For column As Integer = 0 To dataGridView.Columns.Count() - 1
                xlsTable.easy_getCell(tableRow, column).setValue( _
                                      dataGridView.Rows(row).Cells(column).Value.ToString())
                xlsTable.easy_getCell(tableRow, column).setStyle(xlsStyleCell)
            Next
            tableRow = tableRow + 1
        Next
        
        ' Export Excel file
        workbook.easy_WriteXLSXFile("C:\Samples\Export To Excel.xlsx")
    End Sub
    
    Private Sub FillDataGridView()
    
        ' Create the database connection
        Dim sConnectionString As String = "Initial Catalog=Northwind;"
        sConnectionString += Data Source=localhost;"
        sConnectionString += User ID=sa;Password=;"
        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 "
        sQueryString += "FROM Orders AS ord, [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 ds As DataSet = New DataSet
        adp.Fill(ds)
        
        ' Set the data source of the DataGridView
        dataGridView.DataSource = ds.Tables(0)
    End Sub
    
End Class