In this article, I will describe how to export data from a GridView to Excel and CSV formats using ASP.NET and C#. Recently, I was assigned a task in a project where I needed to export GridView records to an Excel sheet with specific formatting requirements. This included exporting GridView header names, row background colors, and other formatting details.
After successfully completing the task, I decided to share my experience with other developers who may be facing similar challenges.
For demonstration purposes, I have created a "Users" table in the database.
SQL Create Script
CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[Email] [nvarchar](max) NOT NULL,
[Password] [nvarchar](max) NULL,
[About] [nvarchar](max) NULL,
[DOB] [datetime2](7) NULL,
[Gender] [int] NOT NULL,
[Location] [nvarchar](max) NULL,
[ContactNo] [nvarchar](max) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- Let's begin by creating an empty website and adding a WebForm page to the project. Below is the code to be copy-pasted into the WebForm page.
- The page contains two button controls for exporting the data to Excel and CSV formats. Upon clicking these buttons, the GridView data will be exported to an Excel sheet or a CSV file.
Aspx Code:
<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="GridView.aspx.cs" Inherits="GridView" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:Button ID="btnexportexcel" runat="server" Text="Export To Excel" OnClick="btnexportexcel_Click" />
<asp:Button ID="btnExcportcsv" runat="server" Text="Export To Csv" OnClick="btnExcportcsv_Click" />
<br />
<br />
<asp:GridView ID="UserDataGrid" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" OnPageIndexChanging="GridView1_PageIndexChanging" Width="264px">
<AlternatingRowStyle BackColor="White" />
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
</form>
</body>
</html>
Code Behind
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class GridView : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=ADEQUATE-ASHOK\\SQLEXPRESS01;Initial Catalog=DemoDataBase;User ID=adk;Password=adk@1234");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
public void BindGrid()
{
UserDataGrid.DataSource = GetDataSet();
UserDataGrid.DataBind();
}
public DataSet GetDataSet()
{
SqlCommand cmd = new SqlCommand("select * from Users", con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
return ds;
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
UserDataGrid.DataSource = GetDataSet();
UserDataGrid.PageIndex = e.NewPageIndex;
UserDataGrid.DataBind();
}
protected void btnexportexcel_Click(object sender, EventArgs e)
{
ExportGridToExcel();
}
protected void btnExcportcsv_Click(object sender, EventArgs e)
{
ExportGridToCsv();
}
public void ExportGridToExcel()
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=ExportUserData.xls");
Response.ContentType = "File/Data.xls";
StringWriter StringWriter = new System.IO.StringWriter();
HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
UserDataGrid.RenderControl(HtmlTextWriter);
Response.Write(StringWriter.ToString());
Response.End();
}
public void ExportGridToCsv()
{
// set the resulting file attachment name to the name of the report...
string fileName = "test";
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".csv");
Response.Charset = "";
Response.ContentType = "application/text";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
// Get the header row text form the sortable columns
LinkButton headerLink = new LinkButton();
string headerText = string.Empty;
for (int k = 0; k < UserDataGrid.HeaderRow.Cells.Count; k++)
{
//add separator
headerText = UserDataGrid.HeaderRow.Cells[k].Text;
sb.Append(headerText + ",");
}
//append new line
sb.Append("\r\n");
for (int i = 0; i < UserDataGrid.Rows.Count; i++)
{
for (int k = 0; k < UserDataGrid.HeaderRow.Cells.Count; k++)
{
//add separator and strip "," values from returned content...
sb.Append(UserDataGrid.Rows[i].Cells[k].Text.Replace(",", "") + ",");
}
//append new line
sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
/*we are calling GridView1.RenderControl(htmlTextWriter) for exporting the data,
* hence you will an exception that a Server-Control was rendered outside of a Form.*/
/*To avoid this exception this peace of code Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}
}
You can notice in the code I made two changes for avoiding below error.
- Page_Load: This method is executed when the page is loaded. It binds the GridView with data from the database if it's not a postback.
- BindGrid: Binds the GridView with data fetched from the database using the GetDataSet method.
- GetDataSet: Retrieves data from the "Users" table in the database and returns it as a DataSet.
- GridView1_PageIndexChanging: Handles the page index changing event of the GridView. It rebinds the GridView with updated page index.
- btnexportexcel_Click: Event handler for exporting GridView data to an Excel file. It clears the response, sets headers, content type, and renders the GridView to the response output stream.
- btnExcportcsv_Click: Event handler for exporting GridView data to a CSV file. It constructs a StringBuilder object to build the CSV content, loops through GridView rows and cells, and appends them to the StringBuilder. Finally, it writes the content to the response output stream.
- ExportGridToExcel: Clears the response, sets headers for Excel file attachment, and renders the GridView control to the response output stream in HTML format.
- ExportGridToCsv: Clears the response, sets headers for CSV file attachment, constructs CSV content using StringBuilder, and writes the content to the response output stream.
- VerifyRenderingInServerForm: Overrides the base method to prevent the "Server-Control was rendered outside of a Form" exception when exporting GridView data.
Overall, above code provides functionality to export data from a GridView to Excel and CSV formats in an ASP.NET web application.
<%@ Page ............ EnableEventValidation="false" %>
public override void VerifyRenderingInServerForm(Control control)
{
}If you want to give custom style and color to your excel sheet then use the below code.
Export gridview to excel with custom value formatting and color
public void CustomExcelStyleExport()
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "ExportUserData.xls"));
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
UserDataGrid.AllowPaging = false;
UserDataGrid.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying stlye to gridview header cells
for (int i = 0; i < UserDataGrid.HeaderRow.Cells.Count; i++)
{
UserDataGrid.HeaderRow.Cells[i].Style.Add("background-color", "#009688");
}
int j = 1;
//Set alternate row color
foreach (GridViewRow gvrow in UserDataGrid.Rows)
{
gvrow.BackColor = System.Drawing.Color.White;
if (j <= UserDataGrid.Rows.Count)
{
if (j % 2 != 0)
{
for (int k = 0; k < gvrow.Cells.Count; k++)
{
gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
}
}
}
j++;
}
UserDataGrid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}- Response.ClearContent: Clears the content of the HTTP response.
- Response.Buffer = true: Sets the buffering to true to buffer the output.
- Response.AddHeader: Adds a header to the HTTP response specifying the filename for the downloaded file.
- Response.ContentType: Sets the content type of the response to "application/ms-excel".
- StringWriter sw = new StringWriter(): Initializes a StringWriter object to write the content of the GridView.
- HtmlTextWriter htw = new HtmlTextWriter(sw): Initializes an HtmlTextWriter object with the StringWriter to render the content of the GridView.
- UserDataGrid.AllowPaging = false: Disables paging for the GridView to export all data.
- UserDataGrid.HeaderRow.Style.Add: Adds a background color style to the header row of the GridView.
- for loop: Iterates through each cell in the header row and sets a background color style.
- foreach loop: Iterates through each row in the GridView to set background colors for alternating rows.
- UserDataGrid.RenderControl(htw): Renders the content of the GridView using the HtmlTextWriter.
- Response.Write(sw.ToString()): Writes the content of the StringWriter (which contains the GridView's HTML) to the response output stream.
- Response.End(): Ends the HTTP response.
This method customizes the styling of the exported Excel file by setting background colors for the header row and alternating rows in the GridView. Finally, it writes the formatted GridView content to the response stream for the user to download.