Export Data from a Paging Enabled GridView to Excel in Asp.Net using C# and VB

Last updated: 7th July 2024

In this article I am going to show you how to export all the data from a paging enabled GridView control to an Excel worksheet in Asp.Net using C# and VB. The data exported to the worksheet, will show the running total at the end.

export gridView to excel in asp.net using c#

A GridView control often contains many rows and it’s usual to see that paging is enabled to the GridView to make data browsing easy. I'll show you how easily you can export every data from a paging enabled GridView to an Excel worksheet, format the sheet (using code behing procedure) and finally show a running total at the end.

Remember: The Excel file will be created dynamically using C# and VB.

The GridView

I will add a GridView control to a web page and enable paging to it, and set the page size to five. With paging enabled, now I wish to export entire GridView to an Excel file with little formatting too.

<asp:GridView ID="grdBooks" runat="server" 
    AutoGenerateColumns="False" 
    DataKeyNames="BookID" 
    DataSourceID="SqlDataSource1" 
    AllowPaging="true" 
    PageSize="5">

    <Columns>
        <asp:BoundField DataField="BookID" HeaderText="BookID" InsertVisible="False" 
            ReadOnly="True" SortExpression="BookID" />
        <asp:BoundField DataField="BookName" HeaderText="BookName" 
            SortExpression="BookName" />
        <asp:BoundField DataField="Category" HeaderText="Category" 
            SortExpression="Category" />
        <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:DNA_CLASSIFIEDConnectionString %>" 
                    
    SelectCommand="SELECT [BookID], [BookName], [Category], [Price] FROM [Books]">
</asp:SqlDataSource>

<div>
    <input type="button" id="btExportToExcel" value="Export GridView To Excel" 
        onserverclick="ExportToExcel" runat="server" />
</div>

I have populated the GridView with data using SqlDataSource.

Binding data to a GridView control using SqlDataSource is simple and perfect for our example. In addition, I have added a "Button", which when clicked will call a function at code behind to export the data from the GridView.

Code behind (C#)

using System;
using System.Data;                      // FOR DATABASE.
using System.Data.SqlClient;            // FOR SQL CONNECTION.
using System.Web.UI.WebControls;        // FOR DATAGRID.

public partial class SiteMaster : System.Web.UI.MasterPage
{
    System.Data.DataTable mytable = new System.Data.DataTable();
    System.Data.DataRow dr = null;

    protected void ExportToExcel(object sender, EventArgs e)
    {

        create_DataTable();     // CREATE A DATATABLE.

        // CALCULATE VALUES FOR GRAND TOTAL.
        Decimal dTotalPrice = 0;
        for (int i = 0; i <= mytable.Rows.Count - 1; i++)
        {
            dTotalPrice += mytable.Rows[i].Field<Decimal>(3);
        }

        // NOW ASSIGN DATA TO A DATAGRID.
        DataGrid dg = new DataGrid();
        dg.DataSource = mytable;
        dg.DataBind();

        // THE EXCEL FILE.
        string sFileName = "BooksList-" + System.DateTime.Now.Date + ".xls";
        sFileName = sFileName.Replace("/", "");

        // SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT".
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment; filename=" + sFileName);
        Response.ContentType = "application/vnd.ms-excel";
        EnableViewState = false;

        System.IO.StringWriter objSW = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter objHTW = new System.Web.UI.HtmlTextWriter(objSW);

        dg.HeaderStyle.Font.Bold = true;     // SET HEADER AS BOLD.
        dg.RenderControl(objHTW);

        // STYLE THE SHEET AND WRITE DATA TO IT.
        Response.Write("<style> TABLE { border:dotted 1px #999; } " +
                "TD { border:dotted 1px #D5D5D5; text-align:center } </style>");
        Response.Write(objSW.ToString());

        // ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE.
        Response.Write("<table><tr><td><b>Total: </b></td><td></td><td></td><td><b>" +
                    dTotalPrice.ToString("N2") + "</b></td></tr></table>");

        Response.End();
        dg = null;
    }

    private void create_DataTable()
    {
        int iRowCnt = 0;
        
        // CREATE A DATATABLE AND ADD COLUMNS TO IT.
        mytable.Columns.Add(new System.Data.DataColumn("Book ID", System.Type.GetType("System.String")));
        mytable.Columns.Add(new System.Data.DataColumn("Name of the Book", 
            System.Type.GetType("System.String")));
        mytable.Columns.Add(new System.Data.DataColumn("Category", System.Type.GetType("System.String")));
        mytable.Columns.Add(new System.Data.DataColumn("Price ($)", 
            System.Type.GetType("System.Decimal")));

        // REMOVE PAGING TO HELP EXPORT ENTIRE GRIDVIEW TO EXCEL.
        grdBooks.AllowPaging = false;
        grdBooks.DataBind();

        foreach (GridViewRow row in grdBooks.Rows)
        {
            dr = mytable.NewRow();
            dr[0] = grdBooks.Rows[iRowCnt].Cells[0].Text;
            dr[1] = grdBooks.Rows[iRowCnt].Cells[1].Text;
            dr[2] = grdBooks.Rows[iRowCnt].Cells[2].Text;
            dr[3] = grdBooks.Rows[iRowCnt].Cells[3].Text;

            mytable.Rows.Add(dr);

            iRowCnt += 1;
        }
    }
}

How it actualy work?

When we click the button, it will call a code behind procedure named "ExportToExcel". The procedure another private procedure named "create_DataTable()". Here, I am creating a DataTable and bind it to the GridView. The DataTable will extract all the data from the grid.

There are two that are important.

First, while adding columns to the "DataTable", I have set the "Type" of the Price ($) column as Decimal. This will now allow me add the Grand Total at the end of the Excel file’s Price column.

mytable.Columns.Add(new System.Data.DataColumn("Price ($)", System.Type.GetType("System.Decimal")));

A String data type would not allow me to get the cumulative values of the price. I am calculating the price inside the ExportToExcel procedure.

Decimal dTotalPrice = 0;
for (int i = 0; i <= mytable.Rows.Count - 1; i++)
{
    dTotalPrice += mytable.Rows[i].Field<Decimal>(3);
}

Second, in the procedure "create_DataTable()", I have set Paging as false. This will temporarily switch of "paging" allowing me to extract all the rows into the DataTable.

Its a simple workaround. But it works.

// REMOVE PAGING TO HELP EXPORT ENTIRE GRIDVIEW TO EXCEL.
grdBooks.AllowPaging = false;
grdBooks.DataBind();

Finally, we'll export the data with little formatting (styling with CSS) of the header and footer of the Excel sheet.

Vb Code

Option Explicit On
Imports System.Data                 ' FOR DATABASE.
Imports System.Data.SqlClient       ' FOR SQL CONNECTION.

Partial Class Site
    Inherits System.Web.UI.MasterPage

    Dim mytable As New Data.DataTable()
    Dim row As Data.DataRow

    Protected Sub ExportToExcel(ByVal sender As Object, ByVal e As EventArgs)

        Call create_DataTable()         ' CREATE A DATATABLE.

        Dim dTotalPrice As Double = 0

        ' CALCULATE VALUES FOR GRAND TOTAL.
        For i = 0 To mytable.Rows.Count - 1
            dTotalPrice += mytable.Rows(i).Item("Price ($)")
        Next

        ' NOW ASSIGN DATA TO A DATAGRID.
        Dim dg As New DataGrid
        dg.DataSource = mytable : dg.DataBind()

        ' THE EXCEL FILE.
        Dim sFileName As String = "BooksList-" & Replace(Date.Now.Date, "/", "") & ".xls"

        ' SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT".
        Response.ClearContent()
        Response.Buffer = True
        Response.AddHeader("content-disposition", "attachment; filename=" & sFileName)
        Response.ContentType = "application/vnd.ms-excel"
        Me.EnableViewState = False

        Dim objSW As New System.IO.StringWriter
        Dim objHTW As New HtmlTextWriter(objSW)

        dg.HeaderStyle.Font.Bold = True     ' SET HEADER AS BOLD.
        dg.RenderControl(objHTW)

        ' STYLE THE SHEET AND WRITE DATA TO IT.
        Response.Write("<style> TABLE { border:dotted 1px #999; } " & _
                    "TD { border:dotted 1px #D5D5D5; text-align:center } </style>")
        Response.Write(objSW.ToString())

        ' ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE.
        Response.Write("<table><tr><td><b>Total: </b></td><td></td><td></td><td><b>" & _
                    Decimal.Parse(dTotalPrice).ToString("N2") & "</b></td></tr></table>")

        Response.End()
        dg = Nothing

    End Sub

    Private Sub create_DataTable()
        
        Dim iRowCnt As Integer = 0

        ' CREATE A DATATABLE AND ADD COLUMNS TO IT.
        mytable.Columns.Add(New Data.DataColumn("Book ID", System.Type.GetType("System.String")))
        mytable.Columns.Add(New Data.DataColumn("Name of the Book", 
                System.Type.GetType("System.String")))
        mytable.Columns.Add(New Data.DataColumn("Category", System.Type.GetType("System.String")))
        mytable.Columns.Add(New Data.DataColumn("Price ($)", System.Type.GetType("System.String")))

        ' REMOVE PAGING TO HELP EXPORT ENTIRE GRIDVIEW TO EXCEL.
        grdBooks.AllowPaging = False
        grdBooks.DataBind()

        'POPULATE DATATABLE USING GRIDVIEW ROW VALUES.
        For Each GridViewRow In grdBooks.Rows

            row = mytable.NewRow
            row(0) = grdBooks.Rows(iRowCnt).Cells(0).Text
            row(1) = grdBooks.Rows(iRowCnt).Cells(1).Text
            row(2) = grdBooks.Rows(iRowCnt).Cells(2).Text
            row(3) = grdBooks.Rows(iRowCnt).Cells(3).Text

            mytable.Rows.Add(row)

            iRowCnt += 1
        Next
    End Sub
End Class

← PreviousNext →