Export Gridview to Excel and Csv in Asp .Net With Formatting Using c#

Export Gridview to Excel and Csv in Asp .Net With Formatting Using c#

In this article, I’m describing ,how to export data from grid view to excel  and Csv  using ASP.NET and C#.Recently I’m working on a project in which I got a task, we need to export Grid View records to Excel sheet with an instruction. I need to focus on Excel sheet formatting while exporting records to Excel sheet.

we need  to export GridView header name, row background color also. I have completed my task successfully Now I decided to share my experience with other developers who are looking for the same.

For explanation, I have also 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]
  • So let’s start and add an empty website.
  • Add a Webform page in the project and copy-paste below code

In my Web page, I have two button controls to export the data to excel and Csv. Onclick of that Button we are going to export the Gridview data to excel sheet or 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 ............  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();
    }

Download Source Code

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Your Header Sidebar area is currently empty. Hurry up and add some widgets.