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

The problem here is in the interpretation, not the output.

There is nothing wrong with writing a decimal to CSV as 173,8543526 as long as your thousands separator is set correctly (along with your field separator).

In Excel (2010), if you click on Data > From Text and follow the Text Import Wizard to Step 3, you can click on Advanced to choose the decimal and thousands separator for each column.

Note that CSV isn't a formal standard (while there are some de facto rules), so you can choose whatever separators you like, as long as you can interpret them correctly.

Answer 2

I tried to add logic to MakeValueCsvFriendly:

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");
    }
    else if(value is int || value is double || value is float || value is decimal || value is long || value is uint || value is ulong || value is ushort)
    {
        // Output numbers
        // Specific culture: da-DK
        // Specific format: N (thousand separator + 2 decimals)
        // 123456789098.7654321 => 123.456.789.098,77
        return value.ToString("N", new CultureInfo("da-DK"));
    }
    else
    {
        string output = value.ToString();
        if (output.Contains(",") || output.Contains("\""))
            output = '"' + output.Replace("\"", "\"\"") + '"';
        return output;
    }
}

See more Numeric formats here:

1. Standard: http://msdn.microsoft.com/en-us/library/dwhawy9k(v=vs.110).aspx
2. Custom: http://msdn.microsoft.com/en-us/library/0c899ak8(v=vs.110).aspx

Answer 3

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.

Answer 4

Instead of string output = value.ToString();, use:

string output;
if (value is Decimal)
    output = value.ToString("N");
else
    output = value.ToString();