Export gridview data to multiple excel sheets in asp.net

Description:-

In this article we will see how to export data from grid view control and data Table to excel file in a simple way to export grid view data to excel file. Records of each page of GridView with paging enabled will be exported to a different Excel sheets. There are different ways to export grid view data to excel and here I’m explaining a simple way to export grid view and data table data to excel. Follow the below steps to achieve this.

Download :- ClosedXml.dll

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"/>"DBCS" providerName="System.Data.SqlClient"/>-->
    <add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
    <add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
    <add name ="Excel03ConString_1" connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
    <add name ="Excel07+ConString" connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
  </connectionStrings>

Default.aspx:-

    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
            OnPageIndexChanging="OnPageIndexChanging" PageSize="10">
            <Columns>
                <asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
                <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" ItemStyle-Width="100px" />
                <asp:BoundField DataField="Phone" HeaderText="Phone" ItemStyle-Width="100px" />
                <asp:BoundField DataField="Fax" HeaderText="Fax" ItemStyle-Width="100px" />
                <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px" />
                <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportExcel" />
    </div>

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.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 OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

protected void ExportExcel(object sender, EventArgs e)
{
    using (XLWorkbook wb = new XLWorkbook())
    {
        //Loop through the GridView pages.
        for (int i = 0; i < GridView1.PageCount; i++)
        {
            //Set the Current Page.
            GridView1.PageIndex = i;
            this.BindGrid();

            //Create a DataTable with schema same as GridView columns.
            DataTable dt = new DataTable("Page_" + (i + 1));
            foreach (TableCell cell in GridView1.HeaderRow.Cells)
            {
                dt.Columns.Add(cell.Text);
            }

            //Loop and add rows from GridView to DataTable.
            foreach (GridViewRow row in GridView1.Rows)
            {
                dt.Rows.Add();
                for (int j = 0; j < row.Cells.Count; j++)
                {
                    dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
                }
            }

            //Add DataTable as Worksheet.
            wb.Worksheets.Add(dt);
        }

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

Author : Umesh Patel

Related Posts