user23048345 user23048345 - 2 months ago 14
Vb.net Question

ASP.NET CSV Excel issue with strange characters

I'm exporting a table of data to CSV format, e.g.:

"COL1","COL2","COL3"
"1","some text", "£232.00"
"2","some more text", "£111.00"
"3","other text", "£2.00"


The code to export is fairly simple using an ashx handler:

context.Response.Clear()
context.Response.ContentType = "text/csv"
context.Response.AddHeader("Content-disposition", "attachment;filename=data.csv")
context.Response.AddHeader("Cache-Control", "must-revalidate")
context.Response.AddHeader("Pragma", "must-revalidate")
context.Response.Write(data)
context.Response.Flush()
context.Response.End()


My issue is when Excel tries to open the exported file the character
Â
appears before all
£
signs, e.g.
£232.00
when the value should be
£232.00
.

Answer

You need to set the encoding i.e. the Response.ContentEncoding property.

After finally looking this up in Reflector, the default ContentEncoding is likely to be Encoding.Default or Encoding.UTF8, if a system.web/globalization config section exists, unless it is overridden by a responseEncoding property in that config section.

As to whether setting the ContentEncoding property at all performs differently to not doing so:

  • If you set it to the same object as its current setting, it explicitly catches that and does nothing;
  • However, if you change the setting, it calls HttpWriter.UpdateResponseEncoding, which calls HttpWriter.FlushCharBuffer before switching to the new encoding if there's anything buffered to write already.
  • If you do not set ContentEncoding it is set to the default I mentioned above by first reference to HttpWriter.UpdateResponseEncoding, which is definitely called before any content is written because HttpWriter._responseEncodingUpdated is checked in the writing methods and only updated to True at the end of HttpWriter.UpdateResponseEncoding.

As such, I believe the OP needed to change the encoding to Unicode and I assume Phil Hale's comment means he had a system.web/globalization config section that needed overriding.

Notes:

  • The existing code calling Response.Clear only affects content, not headers, so, contrary to my previous suggestion, I don't believe it is relevant, other than removing any chance of existing content to be present to be flushed before the ContentEncoding change.
  • This is based upon the Reflector 6 output from System.Web from .NET Framework 2.0.