How to export pound symbol from a C# Web App to Excel correctly? (£ is produced instead of £)

Question

We're exporting some data from a website app into an Excel spreadsheet, however, when a GBP symbol is used, instead of outputting "£9.99" it produces "£9.99".

Here's the code a colleague has written to produce the spreadsheet [tableOut is a StringBuilder that contains an HTML table]:

string filename = "EngageReplies.xls";
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader( "content-disposition", "attachment;filename=" + filename );
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
this.EnableViewState = false;

Response.Write( tableOut );
Response.End();

Any ideas how we can get the desired output?

p.s. Not sure if I should separate this into a separate question, but when the spreadsheet is generated, a warning message is triggered:

The file you are trying to open, 'EngageReplies.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

I've used Firefox and IE to Open the file with Office 2007. Clicking Yes opens the file okay, but I would prefer it if my users weren't subjected to it.

Answer 1

I wouldn't expect to see a Charset/ContentEncoding header when transferring a binary file, such as an XLS file. I'd also test the output be saving it to the lcoal disk somewhere and verifying that the file creation is doing "the right thing".

Answer 2

The warning is because you are saving HTML data and not an Excel Spreadsheet data in a file with a XLS extention. You could try changing the file extention to '.htm' but I'm not sure how you would get that to open in Excel rather than a web browser.

You proably want to look at this question for more http://stackoverflow.com/questions/150339/generating-an-excel-file-in-asp-net

Answer 3

Aha!

First I tried removing the Charset and CharacterEncoding, but still got the wrong output, so then I set them to the following and it has worked correctly:

Response.Charset = "";
Response.ContentEncoding = System.Text.Encoding.Default;

Thanks for the inspiration Rowland!

Answer 4

I agree with Martin Brown this is a HTML table not an excel spreadsheet that is why you get that message. However I can see why the developer saved it as a .xls it means an end user gets the table opened by Excel (which handles the html conversion with a warning) rather than the html opening in their web browser. Makes the user experience a bit better I suppose.

The alternative would be to properly create the table using office interop (would need to be installed on server and lots of configuration) or finding a library that can create an excel file properly.