Problem in special characters ehen exporting to excel in Asp.Net(C#) RSS

• • Reply
  

  Nick Bansal

  Member

  182 Points

  165 Posts

  Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Jul 27, 2007 12:03 AM|LINK

   

  I am using gridview to display the records and exporting the records to excel . All works fine except when user enters some special characters(ascii codes like à , è, ä) in certain fields, these special characters garbled when exporting to excel. Please let me know how to handle these special characters while exporting. To ecxport to excel i am using the code like

  Response.ContentType = "application/vnd.ms-excel"
  • Moved by limno on Jul 27, 2007 12:18 AM
• • Reply
  

  addie

  Participant

  1562 Points

  291 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Jul 27, 2007 08:25 AM|LINK

  If you have something like below in your code:
   

  dgExport.DataSource = ds;
  dgExport.DataBind();
  System.Web.HttpContext.Current.Response.Clear();
  System.Web.HttpContext.Current.Response.Buffer = true;
  System.Web.HttpContext.Current.Response.Charset = "";
  System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=report.aspx" );

   
   

  try changing it to:
   

  dgExport.DataMember = dsResults.Tables[0].TableName;
  dgExport.DataBind();
  System.Web.HttpContext.Current.Response.Clear();
  System.Web.HttpContext.Current.Response.Buffer = true;
  //add line below
  System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8 ;
  //
  System.Web.HttpContext.Current.Response.Charset = "";
  System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=report.xls");

   
  

  • Edited by Nick Bansal on Jul 29, 2007 01:26 PM
  • Edited by Nick Bansal on Jul 29, 2007 01:27 PM
• • Reply
  

  Nick Bansal

  Member

  182 Points

  165 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Jul 27, 2007 04:28 PM|LINK

  I appreciate your reply. I  tried your code but still the issue not resolved. The special characters are shown as before. Please advise more....
  

• • Reply
  

  addie

  Participant

  1562 Points

  291 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Jul 30, 2007 04:57 AM|LINK

  will you please try changing the encoding to UTF32 and let me know if that works (in case it does nt if possible please paste the code you are using)

   

  System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8 ;

   

• • Reply
  

  Nick Bansal

  Member

  182 Points

  165 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Jul 31, 2007 08:01 PM|LINK

  When i am using System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8 ; i am getting the error as

  "The file is not in a recognizable format. " when opning the excel.

  What i am doing on the page is that i have the gridview associated with the datasource. On server button click (for excel) i am just using  

  Response.ContentType = "application/vnd.ms-excel";

  to export the gridview results to the excel. 

   

• • Reply
  

  addie

  Participant

  1562 Points

  291 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Aug 01, 2007 04:39 AM|LINK

  Is it possible you paste the part of the code you are using to export to excel?
  (btw: in my last post i suggested you to try the UTF32 encoding)

• • Reply
  

  Nick Bansal

  Member

  182 Points

  165 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Aug 02, 2007 07:06 PM|LINK

  On page_load i am using the below code to export the data to excel......

  There is gridview on the page and i am assigning the datasource of that gridview to the session from the previous page. 

  SqlDataSource1.SelectCommand = Session["search"].ToString();

  System.Web.HttpContext.Current.Response.Clear();

  System.Web.HttpContext.Current.Response.Buffer = true;

  System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF32;

  //System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

  System.Web.HttpContext.Current.Response.Charset = "";

  System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=report.xls");

  //Response.ContentType = "application/vnd.ms-excel";

   In my previous reply i mentioned the error that i am getting when i am using encoding as UTF32.

   

   

• • Reply
  

  addie

  Participant

  1562 Points

  291 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Aug 03, 2007 04:57 AM|LINK

  Ok I worked with the Customers table of the Northwind database in Sql Server, the reason being the companyName and city fields have a lot of special characters of the type mentioned by you. The below code seems to be working fine in exporting the special characters. Give it a try and let me know:
   

  protected void Page_Load(object sender, EventArgs e)
  {
  SqlDataSource1.SelectCommand="select companyName, city from [customers]";
  Response.Clear();
  Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
  Response.Charset = "";
  
  
  Response.ContentType = "application/vnd.xls";
  System.IO.StringWriter stringWrite = new System.IO.StringWriter();
  System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
  GridView1.RenderControl(htmlWrite);
  Response.Write(stringWrite.ToString());
  Response.End();
  }
  public override void VerifyRenderingInServerForm(Control control)
  {
  //you need to add this as it is here
  }

   

• • Reply
  

  Nick Bansal

  Member

  182 Points

  165 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Aug 06, 2007 03:31 PM|LINK

  I tried the code you pasted in your reply like you suggested as below and i am getting the error as "WebDev.WebServer.exe has encountered a problem and needs to close.  We are sorry for the inconvenience." whenever i click on the Export Excel button . Please advise.....

  protected void Page_Load(object sender, EventArgs e)

  {

  if (Request.QueryString["Type"] == "excel")

  {

  //SqlDataSource1.SelectCommand = Session["search"].ToString();

  SqlDataSource1.SelectCommand += " and email in (" + Session["excelresult"].ToString() + ")";

  Response.Clear();

  Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

  Response.Charset = "";

  Response.ContentType = "application/vnd.xls";

  System.IO.StringWriter stringWrite = new System.IO.StringWriter();

  System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

  GridView1.RenderControl(htmlWrite);

  Response.Write(stringWrite.ToString());

  Response.End();

  } }

  public override void VerifyRenderingInServerForm(Control control)

  {

  //you need to add this as it is here

  SqlDataSource1.SelectCommand = Session["search"].ToString();

  Response.Clear();

  Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

  Response.Charset = "";

  Response.ContentType = "application/vnd.xls";

  System.IO.StringWriter stringWrite = new System.IO.StringWriter();

  System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

  GridView1.RenderControl(htmlWrite);

  Response.Write(stringWrite.ToString());

  Response.End();

  }

• • Reply
  

  Nick Bansal

  Member

  182 Points

  165 Posts

  Re: Problem in special characters ehen exporting to excel in Asp.Net(C#)

  Aug 06, 2007 03:31 PM|LINK

  I tried the code you pasted in your reply like you suggested as below and i am getting the error as "WebDev.WebServer.exe has encountered a problem and needs to close.  We are sorry for the inconvenience." whenever i click on the Export Excel button . Please advise.....

  protected void Page_Load(object sender, EventArgs e)

  {

  if (Request.QueryString["Type"] == "excel")

  {

  //SqlDataSource1.SelectCommand = Session["search"].ToString();

  SqlDataSource1.SelectCommand += " and email in (" + Session["excelresult"].ToString() + ")";

  Response.Clear();

  Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

  Response.Charset = "";

  Response.ContentType = "application/vnd.xls";

  System.IO.StringWriter stringWrite = new System.IO.StringWriter();

  System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

  GridView1.RenderControl(htmlWrite);

  Response.Write(stringWrite.ToString());

  Response.End();

  } }

  public override void VerifyRenderingInServerForm(Control control)

  {

  //you need to add this as it is here

  SqlDataSource1.SelectCommand = Session["search"].ToString();

  Response.Clear();

  Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

  Response.Charset = "";

  Response.ContentType = "application/vnd.xls";

  System.IO.StringWriter stringWrite = new System.IO.StringWriter();

  System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

  GridView1.RenderControl(htmlWrite);

  Response.Write(stringWrite.ToString());

  Response.End();

  }