Export Data from a Paging Enabled GridView to a CSV File using Asp.Net StringBuilder Class in C# and VB

While reviewing the comments in my blog, I came across a query from a reader who wanted to know if its possible to export data from a GridView control to a CSV file in Asp.Net. The answer is yes and its very simple. I’ll show you how to export data from paging enabled GridView to a CSV file using the StringBuilder class in Asp.Net C#. The example here also has code for Visual Basic developers.

Export data from Gridview control a CSV file in Asp.Net C#

I have previously written an article similar to the one you are reading now and it was on how to export a paging enabled GridView data to an Excel file in Asp.Net. It is be useful too. However, exporting data to a CSV file is as important as an Excel. CSV is a common data exchange format supported by a variety of applications and you can very easily create CSV files in Asp.Net.

The Markup

In the markup section, I have a GridView control, which I am binding with a SqlDataSource control that will populate the GridView with data. I also have a button, which will call a procedure in the code behind section on-click, to export data from the GridView to a CSV file.

Note: I have set the GridView’s AllowPaging attribute to true with a PageSizes of 5.

In case you are a newbie and you are not acquainted with the “SqlDataSource” control, I have two useful articles on SqlDataSource, just for you. The first is a beginner’s guide on how to bind data to a GridView control using a SqlDataSource and the second is how to bind an SQL Server Stored Procedure with SqlDataSource and a GridView control. Both the articles are useful and I am sure you don’t want to miss it.

Now, let’s design our page.

<!DOCTYPE html>
<html>
<head>
    <title>Export GridView Data to CSV</title>
</head>
<body>
    <form runat="server">
    <div>
        <%--ADD A GRIDVIEW CONTROL WITH ITS “AllowPaging” ATTRIBUTE SET TO "true".--%>
        <asp:GridView ID="grdBooks" 
            AutoGenerateColumns="False" 
            DataKeyNames="BookID" DataSourceID="SqlDataSource1" 
            AllowPaging="true" PageSize="5"
            runat="server">

            <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>

        <%--BIND THE GRIDVIEW TO AN SqlDataSource CONTROL.--%>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ENCODEDNAConnectionString2 %>" 
                    
            SelectCommand="SELECT [BookID], [BookName], [Category], [Price] FROM [Books]">
        </asp:SqlDataSource>

        <div>
            <input type="button" id="btExportToCSV" value="Export To CSV"
                onserverclick="ExportToCSV" runat="server" />
        </div>
    </div>
    </form>
</body>
</html>
Code behind (C#)
using System;
using System.Text;

public partial class SiteMaster : System.Web.UI.MasterPage
{
    protected void ExportToCSV(object sender, EventArgs e)
    {
        // THE CSV FILE.
        string sFileName = "BooksList-" + System.DateTime.Now.Date + ".csv";
        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/text";
        EnableViewState = false;

        StringBuilder objSB = new StringBuilder();
        int iCol = 0;           // COUNTER FOR COLUMNS.
        int iRow = 0;           // COUNTER FOR ROWS.
        double dTotalPrice = 0;

        // EXTRACT GRIDVIEW HEADERS AND APPEND THE HEADERS TO THE STRING BUILDER OBJECT
        // WITH A SEPERATOR (COMMA).
        for (iCol = 0; iCol < grdBooks.Columns.Count; iCol++)
        {
            objSB.Append(grdBooks.Columns[iCol].HeaderText + ',');
        }

        // APPEND A NEW LINE.
        objSB.Append(Environment.NewLine);

        for (iRow = 0; iRow < grdBooks.Rows.Count; iRow++)
        {
            for (iCol = 0; iCol < grdBooks.Columns.Count; iCol++)
            {
                // APPEND ROW VALUES WITH A COMMA SEPERATOR.
                objSB.Append(grdBooks.Rows[iRow].Cells[iCol].Text + ',');
            }

            // APPEND A NEW LINE.
            objSB.Append(Environment.NewLine);

            // CALCULATE THE TOTAL PRICE. Cells(3) IS THE PRICE COLUMN.
            // THIS IS OPTIONAL.
            if (double.Parse(grdBooks.Rows[iRow].Cells[3].Text) != 0)
            {
                dTotalPrice += double.Parse(grdBooks.Rows[iRow].Cells[3].Text);
            }
        }

        // APPEND THE TOTAL PRICE VALUE AT THE END OF THE CSV (AT THE 3RD COLUMN).
        objSB.Append("," + "," + "Total:," + dTotalPrice.ToString());

        Response.Write(objSB.ToString());
        Response.End();
    }
}

I am using the StringBuilder class of namespace System.Text in my code behind procedure. This is ideal for converting GridView data to a CSV format. The Append() method adds data, separated by comma to the StringBuilder. Later, you can write the StringBuilder data to the file (a specified CSV file).

If you have noticed, at the end of the procedure, I am calculating the total price, storing it in a variable named dTotalPrice.

if (double.Parse(grdBooks.Rows[iRow].Cells[3].Text) != 0)
{
    dTotalPrice += double.Parse(grdBooks.Rows[iRow].Cells[3].Text);
}

This method is optional as you typically do not create a CSV to calculate totals and sub totals etc. I just wanted to show you how you can use the Append() method to add extract data at a particular location in the CSV.

// APPEND THE TOTAL PRICE VALUE AT THE END OF THE CSV (AT THE 3RD COLUMN).
objSB.Append("," + "," + "Total:," + dTotalPrice.ToString());

Note: There are two ways you can use the StringBuilder class in your project. You can either add the namespace using System.Text; on the top of the code, followed by the declaration. Alternatively, you can simply initialize it in your code as shown below.

System.Text.StringBuilder();

Code behind (VB)

Now, here’s the code for Visual Basic developers. You do not have to bother about importing the namespace System.Text in VB.

Import System.Text

You can straight away initialize the StringBuilder class in your code.

Dim objSB As New StringBuilder()

However, there is no harm in importing the namespace, either.

Option Explicit On

Partial Class Site
    Inherits System.Web.UI.MasterPage

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

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

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

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

        Dim objSB As New StringBuilder()
        Dim iCol As Integer = 0             ' COUNTER FOR COLUMNS.
        Dim iRow As Integer = 0             ' COUNTER FOR ROWS.
        Dim dTotalPrice As Double = 0

        ' EXTRACT GRIDVIEW HEADERS AND APPEND THE HEADERS TO THE STRING BUILDER OBJECT
        ' WITH A SEPERATOR (COMMA).
        For iCol = 0 To grdBooks.Columns.Count - 1
            objSB.Append(grdBooks.Columns(iCol).HeaderText + ",")
        Next

        ' APPEND A NEW LINE.
        objSB.Append(Environment.NewLine)

        For iRow = 0 To grdBooks.Rows.Count - 1
            For iCol = 0 To grdBooks.Columns.Count - 1
                ' APPEND ROW VALUES WITH A COMMA SEPERATOR.
                objSB.Append(grdBooks.Rows(iRow).Cells(iCol).Text + ",")
            Next

            ' APPEND A NEW LINE.
            objSB.Append(Environment.NewLine)

            ' CALCULATE THE TOTAL PRICE. Cells(3) IS THE PRICE COLUMN.
            ' THIS IS OPTIONAL.
            If Val(grdBooks.Rows(iRow).Cells(3).Text) <> 0 Then
                dTotalPrice += Val(grdBooks.Rows(iRow).Cells(3).Text)
            End If
        Next

        ' APPEND THE TOTAL PRICE VALUE AT THE END OF THE CSV (AT THE 3RD COLUMN).
        objSB.Append("," + "," + "Total:," + dTotalPrice.ToString)

        Response.Write(objSB.ToString())
        Response.End()
    End Sub
End Class

Well, that is it. Thanks for reading.

← PreviousNext →


Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner

Related Posts: