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:
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('.', ',');