Export GridView Data to Excel using ClosedXml

Description:-

ClosedXML makes it easier for developers to create Excel 2007/2010 files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB).

ClosedXML allows you to create Excel 2007/2010 files without the Excel application. The typical example is creating Excel reports on a web server.

If you've ever used the Microsoft Open XML Format SDK you know just how much code you have to write to get the same results as the following 4 lines of code.

var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
workbook.SaveAs("HelloWorld.xlsx");

Here we will create Excel file from Sql database and download it directly. using OpenXML.dll we can create it. using it Create XLWorkbook, WorkSheets etc. 

Download File:- ClosedXML.dll, AccessDatabaseEngine.exe

Here is the demo example to Export gridview data to Excel file.

Create Table:-

CREATE TABLE [dbo].[Customers](
 [ContactName] [varchar](20) NOT NULL,
 [City] [varchar](20) NOT NULL,
 [PostalCode] [int] NOT NULL,
 [Country] [varchar](20) NOT NULL,
 [Phone] [bigint] NOT NULL,
 [Fax] [bigint] NOT NULL,
 [CustomerId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Web.Config:-

<connectionStrings>
    <add connectionString="ConnectionString" name="DBCS" providerName="System.Data.SqlClient"/>   
    <add name ="Excel3ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
    <add name ="Excel7ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
    <add name ="Excel3ConString" connectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
    <add name ="Excel7+ConString" connectionString ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
</connectionStrings>

Namespaces:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration;
using ClosedXML.Excel;

Default.aspx:-

<div>
  <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White" runat="server" AutoGenerateColumns="false">
    <Columns>
      <asp:BoundField DataField="CustomerId" HeaderText="Id" ItemStyle-Width="30" />
      <asp:BoundField DataField="ContactName" HeaderText="Name" ItemStyle-Width="150" />
      <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150" />
      <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" ItemStyle-Width="150" />
      <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
      <asp:BoundField DataField="Phone" HeaderText="Phone" ItemStyle-Width="150" />
      <asp:BoundField DataField="Fax" HeaderText="Fax" ItemStyle-Width="150" />
    </Columns>
  </asp:GridView>
  <br />
  <asp:Button ID="Button1" Text="Export" OnClick="ExportExcel" runat="server" />
</div>

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)
{
  if (!this.IsPostBack)
  {
    if (!IsPostBack)
    {
      this.BindGrid();
    }
  }
}

private void BindGrid()
{
  string strConnString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  using (SqlConnection con = new SqlConnection(strConnString))
  {
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
    {
      using (SqlDataAdapter sda = new SqlDataAdapter())
      {
        cmd.Connection = con;
        sda.SelectCommand = cmd;
        using (DataTable dt = new DataTable())
        {
          sda.Fill(dt);
          GridView1.DataSource = dt;
          GridView1.DataBind();
        }
      }
    }
  }
}

protected void ExportExcel(object sender, EventArgs e)
{
  DataTable dt = new DataTable("GridView_Data");
  foreach (TableCell cell in GridView1.HeaderRow.Cells)
  {
    dt.Columns.Add(cell.Text);
  }
  foreach (GridViewRow row in GridView1.Rows)
  {
    dt.Rows.Add();
    for (int i = 0; i < row.Cells.Count; i++)
    {
      dt.Rows[dt.Rows.Count - 1][i] = row.Cells[i].Text;
    }
  }
  using (XLWorkbook wb = new XLWorkbook())
  {
    wb.Worksheets.Add(dt);

    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
    using (MemoryStream MyMemoryStream = new MemoryStream())
    {
      wb.SaveAs(MyMemoryStream);
      MyMemoryStream.WriteTo(Response.OutputStream);
      Response.Flush();
      Response.End();
    }
  }
}

Author : Umesh Patel

Related Posts