Well ,the problem is the way of Excel to deal with BOM . You might found out to use a StreamWriter :
StreamWriter defaults to using an instance of UTF8Encoding unless specified otherwise. This instance of UTF8Encoding is constructed without a byte order mark (BOM), so its GetPreamble method returns an empty byte array. The default UTF-8 encoding for this constructor throws an exception on invalid bytes. This behavior is different from the behavior provided by the encoding object in the Encoding.UTF8 property. To specify a BOM and determine whether an exception is thrown on invalid bytes, use a constructor that accepts an encoding object as a parameter, such as StreamWriter(String, Boolean, Encoding) or StreamWriter.
So I just create a custom implementation of IActionResult :
public class Utf8ForExcelCsvResult : IActionResult
{
public string Content{get;set;}
public string ContentType{get;set;}
public string FileName {get;set;}
public async Task ExecuteResultAsync(ActionContext context)
{
var Response =context.HttpContext.Response;
Response.Headers["Content-Type"] = this.ContentType;
Response.Headers["Content-Disposition"]=$"attachment; filename={this.FileName}; filename*=UTF-8''{this.FileName}";
// for ASP.NET Core above, sync methods are forbidden:
using var sw = new StreamWriter(Response.Body, System.Text.Encoding.UTF8);
try
{
await sw.WriteAsync(Content);
await sw.FlushAsync();
}
finally
{
await sw.DisposeAsync();
}
}
}
When you need open such a csv file using Excel , simply return a Utf8ForExcelCsvResult .
[HttpGet]
[Route("/progress/data.csv")]
[Produces("text/csv")]
public IActionResult MyFileDownload()
// public Utf8ForExcelCsvResult MyFileDownload()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("æø;2;3;");
sb.AppendLine("გამარჯობა");
sb.AppendLine("ဟယ်လို");
sb.AppendLine("ສະບາຍດີ");
sb.AppendLine("cześć");
sb.AppendLine("こんにちは");
sb.AppendLine("你好");
Console.WriteLine(sb.ToString());
return new Utf8ForExcelCsvResult(){
Content=sb.ToString(),
ContentType="text/csv",
FileName="hello.csv",
};
}
We can use Powershell to inspect the HEX representation of csv file by Format-hex -path .\hello.csv :
00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
00000000 EF BB BF C3 A6 C3 B8 3B 32 3B 33 3B 0D 0A E1 83 æø;2;3;..á
00000010 92 E1 83 90 E1 83 9B E1 83 90 E1 83 A0 E1 83 AF ááá á¯
00000020 E1 83 9D E1 83 91 E1 83 90 0D 0A E1 80 9F E1 80 ááá..áá
00000030 9A E1 80 BA E1 80 9C E1 80 AD E1 80 AF 0D 0A E0 áºááá¯..à
00000040 BA AA E0 BA B0 E0 BA 9A E0 BA B2 E0 BA 8D E0 BA ºªàº°àºàº²àºàº
00000050 94 E0 BA B5 0D 0A 63 7A 65 C5 9B C4 87 0D 0A E3 ີ..czeÅ..ã
00000060 81 93 E3 82 93 E3 81 AB E3 81 A1 E3 81 AF 0D 0A ãã«ã¡ã¯..
00000070 E4 BD A0 E5 A5 BD 0D 0A ä½ å¥½..
Here the first three bytes EF BB BF are the Byte order marks
