unable to export excel file from gidview or dataset
<p>I try many thing with the code but the same code I before used not work in my current project. I can retrieve data from the data base if i change visible=true then  data also display in the grid view. I try all the function as mentioned bellow but still I am unable to get answer</p>

<p>following is bunch of aspx page code</p>

<pre><code>&lt;asp:GridView ID="gvExport" runat="server" Visible="false" 
    AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" 
    GridLines="None"&gt;
    &lt;RowStyle BackColor="#F7F6F3" ForeColor="#333333" /&gt;
    &lt;Columns&gt;
        &lt;asp:BoundField DataField="artID" HeaderText="ID" /&gt;
        &lt;asp:BoundField DataField="artName" HeaderText="Name" /&gt;
        &lt;asp:BoundField DataField="artType" HeaderText="Category" /&gt;
        &lt;asp:BoundField DataField="artWork" HeaderText="Work Type" /&gt;
        &lt;asp:BoundField DataField="artCont1" HeaderText="Contact Person (I)" /&gt;
        &lt;asp:BoundField DataField="artMob1" HeaderText="Mobile" /&gt;
        &lt;asp:BoundField DataField="artCont2" HeaderText="Contact person (II)" /&gt;
        &lt;asp:BoundField DataField="artMob2" HeaderText="Mobile" /&gt;
        &lt;asp:BoundField DataField="artPhone" HeaderText="Office Phone" /&gt;
        &lt;asp:BoundField DataField="artEmail" HeaderText="Email ID" /&gt;
        &lt;asp:BoundField DataField="artStreet" HeaderText="Street" /&gt;
        &lt;asp:BoundField DataField="artCity" HeaderText="City" /&gt;
        &lt;asp:BoundField DataField="artState" HeaderText="State" /&gt;
        &lt;asp:BoundField DataField="artCountry" HeaderText="Country" /&gt;
    &lt;/Columns&gt;
    &lt;FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /&gt;
    &lt;PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /&gt;
    &lt;SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /&gt;
    &lt;HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /&gt;
    &lt;EditRowStyle BackColor="#999999" /&gt;
    &lt;AlternatingRowStyle BackColor="White" ForeColor="#284775" /&gt;
&lt;/asp:GridView&gt;
</code></pre>

<p>and page behind code as followed</p>

<pre><code>dataBase dConnect = new dataBase();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
static string exceprionString = "", artType = "", searchCity = "", searchType = "", searchCont = "", searchSugType = "", searchSugCIty = "", fileName = "";
static int i = 0, j = 0, artID = 0;
static List&lt;string&gt; listArt = new List&lt;string&gt;();
/*Export User Artist File As MS Excel Format*/
protected void btExport_Click(object sender, EventArgs e)
{
    if (((string)Session["userRole"]).Equals("ADMIN") || ((string)Session["userRole"]).Equals("MANAGER"))
    {
        //try
        //{
            exportFile("artist");
        //}
        //catch (Exception ex)
        //{
        //    throw ex;
        //}
    }
    else
        SMS("User Doed Not Rights To Export File");
}
/*Export Suggested Artist File As MS Excel Format*/
protected void btSugExport_Click(object sender, EventArgs e)
{
    if (((string)Session["userRole"]).Equals("ADMIN") || ((string)Session["userRole"]).Equals("MANAGER"))
    {
        try
        {
            exportFile("sugArtist");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    else
        SMS("User Doed Not Rights To Export File");
}
/*********File Export From Grid View*/
private void exportFile(string expportType)
{
    if (((string)Session["userRole"]).Equals("ADMIN") || ((string)Session["userRole"]).Equals("MANAGER"))
    {
        //try
        //{
            ds.Clear();
            if (expportType.Equals("artist"))
            {
                if (((string)Session["userID"]).Equals("admin"))
                    ds = dConnect.artistInfo(0, ((string)Session["userID"]), "", "", "", "", "", "", "", "", "", "", "", "", "", "admin");
                else if (((string)Session["userRole"]).Equals("MANAGER"))
                    ds = dConnect.artistInfo(0, ((string)Session["userID"]), "", "", "", "", "", "", "", "", "", "", "", "", "", "manager");
                else if (((string)Session["userRole"]).Equals("MANAGER"))
                    ds = dConnect.artistInfo(0, ((string)Session["userID"]), "", "", "", "", "", "", "", "", "", "", "", "", "", "select");
                fileName = (string)Session["userID"];
            }
            else if (expportType.Equals("sugArtist"))
            {
                ds = dConnect.artistInfo(0, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "sugExport");           
                fileName = "Sugested Artist";
            }
            exceprionString = "";
            exceprionString = dConnect.exceptionMessage();

        //}
        //catch (Exception ex)
        //{
        //    throw ex;
        //}
        //try
        //{
            if (ds.Tables[0].Rows.Count &gt; 0 &amp;&amp; exceprionString.Equals(""))
            {
                if (expportType.Equals("artist"))
                {
                    gvExport.DataSource = ds.Tables[0];
                    gvExport.DataBind();

                    //PrepareGridViewForExport(gvExport);
                    //ExportInExcel(gvExport);

                    ExcelFileExpor(gvExport);

                    //ExportExcelFile(gvExport);

                    //ExcelExportByDataSet(ds);
                }
                else if (expportType.Equals("sugArtist"))
                {
                    gvSugArtistExport.DataSource = ds.Tables[0];
                    gvSugArtistExport.DataBind();

                    //PrepareGridViewForExport(gvSugArtistExport);
                    //ExportInExcel(gvSugArtistExport);

                    ExcelFileExpor(gvSugArtistExport);

                    //ExportExcelFile(gvSugArtistExport);

                    //ExcelExportByDataSet(ds);
                }
            }
            else
                SMS("File Not Export Due To Data Not Found");
        //}
        //catch (Exception ex)
        //{
        //    //throw ex;
        //    //SMS("File Unable To Export");
        //    SMS(ex.ToString());
        //}
    }
    else
        SMS("user Does Not Rights To Export File");
}
/*Export Excel Sheet*/
/***********Clear Contro Of Grid View Befor Export To Excel*/
public override void VerifyRenderingInServerForm(Control control)
{
}
/*Remove Control Before Export The File*/
private void PrepareGridViewForExport(Control gv)
{
    LinkButton lb = new LinkButton();
    Literal l = new Literal();
    string name = String.Empty;
    for (int i = 0; i &lt; gv.Controls.Count; i++)
    {
        if (gv.Controls[i].GetType() == typeof(LinkButton))
        {
            l.Text = (gv.Controls[i] as LinkButton).Text;
            gv.Controls.Remove(gv.Controls[i]);
            gv.Controls.AddAt(i, l);
        }
        else if (gv.Controls[i].GetType() == typeof(DropDownList))
        {
            l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
            gv.Controls.Remove(gv.Controls[i]);
            gv.Controls.AddAt(i, l);
        }
        else if (gv.Controls[i].GetType() == typeof(CheckBox))
        {
            l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
            gv.Controls.Remove(gv.Controls[i]);
            gv.Controls.AddAt(i, l);
        }
        else if (gv.Controls[i].GetType() == typeof(HiddenField))
        {
            l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
            gv.Controls.Remove(gv.Controls[i]);
            gv.Controls.AddAt(i, l);
        }
        if (gv.Controls[i].HasControls())
        {
            PrepareGridViewForExport(gv.Controls[i]);
        }
    }
}

/*Export Data From Grid View To Excel **********/
private void ExportInExcel(GridView grid)
{
    HtmlForm form = new HtmlForm();
    string attachment = "attachment; filename=" + fileName + ".xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";
    StringWriter stw = new StringWriter();
    HtmlTextWriter htextw = new HtmlTextWriter(stw);
    form.Controls.Add(grid);
    this.Controls.Add(form);
    form.RenderControl(htextw);
    Response.Write(stw.ToString());
    //Response.Output.Write(stw.ToString());//
    //Response.Flush();//
    //Response.End();

    HttpContext.Current.ApplicationInstance.CompleteRequest();
}
/**Export Excel FIle **/
private void ExcelFileExpor(GridView gv)
{
    StringWriter tw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(tw);
    gv.RenderControl(hw);

    Response.ContentType = "application/vnd.ms-excel"; 
    Response.AppendHeader("Content-Disposition", "attachment; filename="+ fileName +".xls");
    this.EnableViewState = false;
    Response.Write(tw.ToString());
    Response.End();
}
/*Export With Drow Command Like paint The Header text Etc */
private void ExportExcelFile(GridView GridView1)
{
    try
    {
        Response.Clear();
        Response.Buffer = true;

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

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

        //Change the Header Row back to white color
        GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

        //Apply style to Individual Cells
        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");
        GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");

        for (int i = 0; i &lt; GridView1.Rows.Count; i++)
        {
            GridViewRow row = GridView1.Rows[i];

            //Change Color back to white
            row.BackColor = System.Drawing.Color.White;

            //Apply text style to each Row
            row.Attributes.Add("class", "textmode");

            //Apply style to Individual Cells of Alternating Row
            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");
                row.Cells[3].Style.Add("background-color", "#C2D69B");
            }
        }
        GridView1.RenderControl(hw);

        //style to format numbers to string
        string style = @"&lt;style&gt; .textmode { mso-number-format:\@; } &lt;/style&gt;";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        //Response.End();
        HttpContext.Current.ApplicationInstance.CompleteRequest();
    }
    catch (Exception ex)
    {
        SMS(ex.ToString());
    }
    //Response.End();
}
/*Export Excel By DataTable*/
private void ExcelExportByDataSet(DataSet ddData)
{
    if (ddData.Tables[0].Rows.Count &gt; 0)
    {
        DataTable dt = new DataTable();
        dt = (DataTable)ddData.Tables[0];

        string attachment = "attachment; filename=" + fileName + ".xls";
        //string attachment = "attachment; filename="+ fileName +".xls";
        //string attachment = "attachment; filename=" + fileName + ".csv";

        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        //Response.ContentType = "application/excel";
        Response.ContentType = "application/vnd.ms-excel";
        //Response.ContentType = "text/csv";

        string tab = "";
        foreach (DataColumn dc in dt.Columns)
        {
            Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
        Response.Write("\n");

        int i;
        foreach (DataRow dr in dt.Rows)
        {
            tab = "";
            for (i = 0; i &lt; dt.Columns.Count; i++)
            {
                Response.Write(tab + dr[i].ToString());
                tab = "\t";
            }
            Response.Write("\n");
        }
        dt.Clear();

        Response.End();
        SMS("File Successfully Export");
    }
    else
        SMS("Unalbe Export File");

}
</code></pre>
