CODEWITHSUNDEEP
c#.netasp.net-mvcasp.net-core
Amir Khan
Amir Khan

Reputation: 81

CSV files is showing absurd character instead of chinese or korean

I have created a generic method which downloads a csv file for any input. I am facing issue with the chinese and korean character as they are coming differently in the csv file.enter image description here

private IActionResult ReturnCsvFile<T>(IEnumerable<T> users)
        {
            var csvString = new StringBuilder();
            Type typeParameterType = typeof(T);

            var propInfos = typeParameterType.GetProperties(
            BindingFlags.Instance | BindingFlags.Public).OrderBy(x => x.MetadataToken)
                .Select(p => p.GetCustomAttribute<JsonPropertyAttribute>());

            csvString.AppendLine(string.Join(",", propInfos.Select(x => x.PropertyName)));
            var counter = 1;
            foreach (var item in users)
            {
                PropertyInfo[] props = item.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public).OrderBy(x => x.MetadataToken).ToArray();
                props[0].SetValue(item, Convert.ChangeType(counter++, props[0].PropertyType, CultureInfo.InvariantCulture), null);
                string commaSeperatedValues = string.Join(",", props.Select(prop => "\"" + Convert.ToString(prop.GetValue(item), CultureInfo.InvariantCulture) + "\""));
                csvString.AppendLine(commaSeperatedValues);
            }
            var test = new UTF8Encoding(false).GetBytes(csvString.ToString());
            return File(test, "text/csv", "UsersList.csv");
        }

Upvotes: 1

Views: 815

Answers (1)

Poul Bak
Poul Bak

Reputation: 10930

The problem is:

Excel can't open Unicode files without a BOM (Byte Order Mark).

It will open them as Ansi files, which will show unicode characters as garbage.

Since you're using Encoding.GetBytes() there will never be a BOM.

To get a BOM, you must use StreamWriter.

You can fix that by replacing the last 2 lines by this:

using (MemoryStream ms = new())
{
    using (StreamWriter sw = new(ms, new UTF8Encoding(true)))
    {
        sw.Write(csvString);
    }
}
return File(ms, "text/csv", "UsersList.csv");

Now there should be a BOM and Excel should be able to to open it correctly.

Upvotes: 1

Related Questions