UTF-8 with BOM doesn't work easily with csv

[teamolhuang]

Excel Type

• XLSX
• XLSM
• CSV
• OTHER

Upload Excel File

example).csv

MiniExcel Version

1.34.2

Description

When writing an UTF8 csv and then opening with Excel, it needs to be UTF8 with BOM, otherwise the UTF-8 characters will be messed up.
I've tried methods like,

    private static readonly Encoding _utf8WithBom = new UTF8Encoding(true); // note the `true` here
    private static readonly CsvConfiguration _csvConfiguration = new()
    {
        StreamWriterFunc = stream => new(stream, _utf8WithBom);
    };

doesn't work. Or,

  stream.WriteByte(0xEF);
  stream.WriteByte(0xBB);
  stream.WriteByte(0xBF);
  // ... proceeds to SaveAs()

doesn't work.

Solution

    private static readonly char _utf8bom = '\ufeff';

    private static readonly CsvConfiguration _csvConfiguration = new()
    {
        StreamWriterFunc = stream =>
        {
            StreamWriter sw = new(stream, _utf8WithBom);
            
            sw.Write(_utf8bom);
            return sw;
        }
    };

Since new UTF8Encoding(true) seems the official way in .NET of saying this encoding should come with BOM, would it be supported in the future?