How to use the correct Decimal separator and Thousands separator when exporting to excel in c#?

Question

I have a class(found on the internet, i have made some few modifications) that can export data to a .csv file. This works fine.

But when i open the file, numbers containing decimal values are displayed wrongly.

For example: 173,8543526 will be displayed something like: 17.385.435.26 which is due to the decimal separator not being correct when exporting to csv.

The export class can be seen below:

public class CsvExport
{
    List<string> fields = new List<string>();

    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();

    Dictionary<string, object> currentRow { get { return rows[rows.Count - 1]; } }

    public object this[string field]
    {
        set
        {
            // Keep track of the field names, because the dictionary loses the ordering
            if (!fields.Contains(field)) fields.Add(field);
            currentRow[field] = value;
        }
    }

    public void AddRow()
    {
        rows.Add(new Dictionary<string, object>());
    }

    public void RemoveLastRow()
    {
        rows.RemoveAt(rows.Count - 1);
    }

    string MakeValueCsvFriendly(object value)
    {
        if (value == null) return "";
        if (value is INullable && ((INullable)value).IsNull) return "";
        if (value is DateTime)
        {
            if (((DateTime)value).TimeOfDay.TotalSeconds == 0)
                return ((DateTime)value).ToString("yyyy-MM-dd");
            return ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
        }
        string output = value.ToString();
        if (output.Contains(",") || output.Contains("\""))
            output = '"' + output.Replace("\"", "\"\"") + '"';
        return output;
    }

    public string Export(bool omitHeaders)
    {
        StringBuilder sb = new StringBuilder();

        // The header
        if (!omitHeaders)
        {
            foreach (string field in fields)
                sb.Append(field).Append(";");
            sb.AppendLine();
        }

        // The rows
        foreach (Dictionary<string, object> row in rows)
        {
            foreach (string field in fields)
            {
                if (row.ContainsKey(field))
                {
                    sb.Append(MakeValueCsvFriendly(row[field])).Append(";");
                }
                else
                {
                    sb.Append(";");
                }

            }
            sb.AppendLine();   

        }

        return sb.ToString();
    }

    public string ExportToFileDialog(string defaultFileName)
    {
        string filename = String.Empty;
        var dlg = new Microsoft.Win32.SaveFileDialog
                      {
                          FileName = !String.IsNullOrEmpty(defaultFileName) ? defaultFileName : String.Empty,
                          DefaultExt = ".csv",
                          Filter = "CSV (Comma delimited)|*.csv"
                      };

        // Show save file dialog box
        var result = dlg.ShowDialog();

        // Process save file dialog box results
        if (result == true)
        {
            // Save document
            filename = dlg.FileName;
        }

        return filename;
    }

    public void ExportToFile(string path, bool append = false, bool omitHeaders = false)
    {
        try
        {
            if (append)
                File.AppendAllText(path, Export(omitHeaders), Encoding.UTF8);
            else
                File.WriteAllText(path, Export(omitHeaders), Encoding.UTF8);
        }
        catch (Exception exc) 
        {

        }
    }

So my question is, how can I in this class define that "," should be decimal separator and "." should be thousands separator?

Answer 1

For this you will probably need to force the formatting to use different thousands separators and decimal places.

You can use a custom number formatter with the NumberFormatInfo type. First, place a class variable in the class:

NumberFormatInfo customNumFormat;

Then define a constructor as follows:

public CsvExport()
{
    customNumFormat = (NumberFormatInfo)CultureInfo.InvariantCulture.NumberFormat.Clone();
    customNumFormat.NumberGroupSeparator = ".";
    customNumFormat.NumberDecimalSeparator = ",";
}

This will define a number formatter with the separators you require. This allows you to control the separators and decimal place marker to use whatever character you wish.

To use this, you will need to format the numbers yourself, so add in another if statement in your MakeValueCsvFriendly method as follows:

if (value is decimal || value is double || value is int)
  return string.Format(customNumFormat, "{0:N}", value);

If you have other number types in your dictionary you will need to add these types to the if statement accordingly.

Hope this helps.