Search code examples
c#excelepplus

I get different numbers in excel on different PCs using EPPlus generated file


I have this code in my C# application to generate Excel files using EPPlus, everything works nicely, i send the application to client and he says that it shows wrong numbers. In the Excel files he sent me back, the numbers are really different. Can't figure out why. I suspect Excel is the culprit, interpreting numbers differently because of region settings or something similar. How can i fix this? For example, 20 becomes 2000 on another PC:

Decimal.TryParse(dataGridView1.Rows[e.RowIndex].Cells[15].Value.ToString().Replace(".", ","), out number); //cell value 20.00
ws.Cells["I33"].Value = number;
ws.Cells["I33"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells["I33"].Style.Numberformat.Format = "0.00"; //becomes 20,00

Results: results


Solution

  • Here is small example:

    string decimalStr1 = "123,34";
    string decimalStr2 = "123.34";
    
    decimal number1, number2 = 0m;
    
    decimal.TryParse(decimalStr1.Replace(",", "."), NumberStyles.AllowDecimalPoint, 
                     CultureInfo.InvariantCulture, out number1);
    decimal.TryParse(decimalStr2.Replace(",", "."), NumberStyles.AllowDecimalPoint, 
                     CultureInfo.InvariantCulture, out number2);
    
    // number1 = number2 = 123.34 
    

    Important is to use '.' as a separator and CultureInfo.InvariantCulture. Ant then after parse (you are sure that value is correct) you can change separator back to ',':

    ws.Cells["I33"].Value = number1.ToString().Replace('.', ',');