Export Gridview data to excel file

Hi Sonali,

Try this code,

<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" Font-Names="Arial"
Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green"
AllowPaging="true" OnPageIndexChanging="OnPaging">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkCol0" runat="server" Checked="true" />
<asp:Label ID="lblCol0" runat="server" Text="CustomerID" />
HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblCustomerID" runat="server" Text='<%# Eval("CustomerID")%>' />
ItemTemplate>
asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkCol1" runat="server" Checked="true" />
<asp:Label ID="lblCol1" runat="server" Text="ContactName" />
HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName")%>' />
ItemTemplate>
asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkCol2" runat="server" Checked="true" />
<asp:Label ID="lblCol2" runat="server" Text="City">asp:Label>
HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblCity" runat="server" Text='<%# Eval("City")%>' />
ItemTemplate>
asp:TemplateField>
Columns>
asp:GridView>
div>
<br />
<asp:Button ID="btnExportExcel" runat="server" Text="ExportToExcel" OnClick="btnExportExcel_Click" />

using System.Data;
using System.Data.SqlClient;
using System.IO;
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
GetCheckBoxStates();
BindGrid();
}
private void BindGrid()
{
string strQuery = "select CustomerID,City,ContactName from customers";
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(strQuery);
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
private void GetCheckBoxStates()
{
CheckBox chkCol0 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol0");
CheckBox chkCol1 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol1");
CheckBox chkCol2 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol2");
ArrayList arr;
if (ViewState["States"] == null)
{
arr = new ArrayList();
}
else
{
arr = (ArrayList)ViewState["States"];
}
arr.Add(chkCol0.Checked);
arr.Add(chkCol1.Checked);
arr.Add(chkCol2.Checked);
ViewState["States"] = arr;
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
GridView1.DataBind();
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
ArrayList arr = (ArrayList)ViewState["States"];
GridView1.HeaderRow.Cells[0].Visible = Convert.ToBoolean(arr[0]);
GridView1.HeaderRow.Cells[1].Visible = Convert.ToBoolean(arr[1]);
GridView1.HeaderRow.Cells[2].Visible = Convert.ToBoolean(arr[2]);
GridView1.HeaderRow.Cells[0].FindControl("chkCol0").Visible = false;
GridView1.HeaderRow.Cells[1].FindControl("chkCol1").Visible = false;
GridView1.HeaderRow.Cells[2].FindControl("chkCol2").Visible = false;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
row.Cells[0].Visible = Convert.ToBoolean(arr[0]);
row.Cells[1].Visible = Convert.ToBoolean(arr[1]);
row.Cells[2].Visible = Convert.ToBoolean(arr[2]);
row.BackColor = System.Drawing.Color.White;
row.Attributes.Add("class", "textmode");
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
}
}
GridView1.RenderControl(hw);
string style = @"";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.End();
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

the below code will export all columns ,please test this

protected void ExportToExcel(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages
GridView1.AllowPaging = false;
this.BindGrid();
GridView1.HeaderRow.BackColor = Color.White;
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
cell.BackColor = GridView1.HeaderStyle.BackColor;
}
foreach (GridViewRow row in GridView1.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = GridView1.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
in that case you can export your datatable/dataset to excell
Mageshwaran R5y ago
Hi Sonali,
Try this code,
<div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" Font-Names="Arial"
        Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green"
        AllowPaging="true" OnPageIndexChanging="OnPaging">
        <Columns>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="chkCol0" runat="server" Checked="true" />
                    <asp:Label ID="lblCol0" runat="server" Text="CustomerID" />
                HeaderTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblCustomerID" runat="server" Text='<%# Eval("CustomerID")%>' />
                ItemTemplate>
            asp:TemplateField>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="chkCol1" runat="server" Checked="true" />
                    <asp:Label ID="lblCol1" runat="server" Text="ContactName" />
                HeaderTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName")%>' />
                ItemTemplate>
            asp:TemplateField>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="chkCol2" runat="server" Checked="true" />
                    <asp:Label ID="lblCol2" runat="server" Text="City">asp:Label>
                HeaderTemplate>
                <ItemTemplate>
                    <asp:Label ID="lblCity" runat="server" Text='<%# Eval("City")%>' />
                ItemTemplate>
            asp:TemplateField>
        Columns>
    asp:GridView>
div>
<br />
<asp:Button ID="btnExportExcel" runat="server" Text="ExportToExcel" OnClick="btnExportExcel_Click" />

using System.Data;
using System.Data.SqlClient;
using System.IO;

protected void Page_Load(object sender, EventArgs e)
{
    if (IsPostBack)
        GetCheckBoxStates();
    BindGrid();
}

private void BindGrid()
{
    string strQuery = "select CustomerID,City,ContactName from customers";
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand(strQuery);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
        sda.Dispose();
        con.Dispose();
    }
}

private void GetCheckBoxStates()
{
    CheckBox chkCol0 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol0");
    CheckBox chkCol1 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol1");
    CheckBox chkCol2 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol2");
    ArrayList arr;
    if (ViewState["States"] == null)
    {
        arr = new ArrayList();
    }
    else
    {
        arr = (ArrayList)ViewState["States"];
    }
    arr.Add(chkCol0.Checked);
    arr.Add(chkCol1.Checked);
    arr.Add(chkCol2.Checked);
    ViewState["States"] = arr;
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;

    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.AllowPaging = false;
    GridView1.DataBind();

    GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
    GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
    GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
    GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");

    ArrayList arr = (ArrayList)ViewState["States"];
    GridView1.HeaderRow.Cells[0].Visible = Convert.ToBoolean(arr[0]);
    GridView1.HeaderRow.Cells[1].Visible = Convert.ToBoolean(arr[1]);
    GridView1.HeaderRow.Cells[2].Visible = Convert.ToBoolean(arr[2]);

    GridView1.HeaderRow.Cells[0].FindControl("chkCol0").Visible = false;
    GridView1.HeaderRow.Cells[1].FindControl("chkCol1").Visible = false;
    GridView1.HeaderRow.Cells[2].FindControl("chkCol2").Visible = false;

    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        GridViewRow row = GridView1.Rows[i];
        row.Cells[0].Visible = Convert.ToBoolean(arr[0]);
        row.Cells[1].Visible = Convert.ToBoolean(arr[1]);
        row.Cells[2].Visible = Convert.ToBoolean(arr[2]);
        row.BackColor = System.Drawing.Color.White;
        row.Attributes.Add("class", "textmode");
        if (i % 2 != 0)
        {
            row.Cells[0].Style.Add("background-color", "#C2D69B");
            row.Cells[1].Style.Add("background-color", "#C2D69B");
            row.Cells[2].Style.Add("background-color", "#C2D69B");
        }
    }
    GridView1.RenderControl(hw);
    string style = @"";
    Response.Write(style);
    Response.Output.Write(sw.ToString());
    Response.End();
}

protected void OnPaging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind();
}
Sapna Girish5y ago
check this link out

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html
Sachin Singh5y ago
the below code will export all columns ,please test this
protected void ExportToExcel(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //To Export all pages
        GridView1.AllowPaging = false;
        this.BindGrid();

        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = GridView1.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
            }
        }

        GridView1.RenderControl(hw);

        //style to format numbers to string
        string style = @"";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}
Amit Gupta5y ago
What's complication in this?

Loop through all the columns when exporting. Share your code snippet so we can make amendments on that