Grid View To Excel

Question

Hi I am trying to convert the contents of a grid View to an excel file and I am doing it using this code

 string attachment = "attachment; filename= " + FileName; 
 Response.ClearContent();
 Response.AddHeader("content-disposition", attachment);
 Response.ContentType = "application/excel";
 StringWriter sw = new StringWriter();
 HtmlTextWriter htw = new HtmlTextWriter(sw);
 gv.RenderControl(htw);           
 Response.Write(sw.ToString());
 Response.End();

The problem is I am getting some sort of html in an excel style format , theres java script in the page links etc what I want is to turn the results of my query into a comma seperated file Is that do-able for free or do I have to run the query myself get the data and write out a csv stream Thanks

Answer 1

I'm not sure if i fully understand your question but the code you posted should create a valid excel file. As far as creating a csv file is concerned, if you want to create the csv file from your gridview try this.

string attachment = "attachment; filename=test.csv";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);

StringWriter sw = new StringWriter();
StringBuilder sb = sw.GetStringBuilder();

foreach (GridViewRow row in GridView1.Rows)
{
    string[] values = new string[row.Cells.Count];            
    for (int i = 0; i < row.Cells.Count; i++)
    {                
        values[i] = row.Cells[i].Text;
    }            
    sb.AppendLine(String.Join(",", values));
}

HtmlTextWriter htw = new HtmlTextWriter(sw);        
Response.Write(sw.ToString());
Response.End();

Answer 2

I use a method which leverages the .RenderControl() method of the GridView, itself. Optionally, you can hide a column or two.

  public static void ExportGridToExcel(System.Web.UI.Page p_Page, System.Web.UI.WebControls.GridView p_GridView, int[] p_ColumnsToHide)
  {

     if (p_ColumnsToHide != null)
     {
        foreach (int i in p_ColumnsToHide)
        {
           p_GridView.Columns[i].Visible = false;
        }//foreach
     }//if

     p_Page.Response.Clear();
     p_Page.Response.Buffer = true;
     p_Page.Response.AddHeader("content-disposition", "attachment; filename=output.xls");
     p_Page.Response.ContentType = "application/vnd.ms-excel";
     p_Page.Response.Charset = string.Empty;
     p_Page.EnableViewState = false;

     System.IO.StringWriter sw = new System.IO.StringWriter();
     System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(sw);

     p_GridView.RenderControl(oHtmlTextWriter);
     p_Page.Response.Write(sw.ToString());
     p_Page.Response.End();

  }

If this doesn't work out of the box, then there are a few other options, one of which you mentioned... writing out a csv stream manually (I prefer tab delimited myself) and then lying to the browser about what kind of file you're sending so it will open it in Exhell. Exhell 2007 unfortunately gives the user a warning on this approach now so depending on the application, this may or may not work for you.

Another approach (albeit heavy) is to put another gridview on the page and send that to this method. The advantage is that you can easily customize the Exhell output separately from what the user sees on-screen (i.e. yank javascript, add columns, etc...). The disadvantage? Two gridviews, of course.

Good luck.