I have previously shared 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.
In the markup section, I have a GridView control, which I am binding with an SqlDataSource control that will populate the GridView with data. The web page also have a button control, 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". Its pagesize is 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
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>
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();
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