MVC Globalization Culture Decimal Number Format using OPEN XML

Question

Good day, I have a MVC application that uploads an excel file using Open XML. The file has deciml values that I pass back to teh view, however when I deply the applciation to teh server it returns the values with proceeding decimal places, for example 292,28 is returned as 292.27999999999997 and 85,7703 is returned as 85.770300000000006. I have tried adding Number Format in code but it doesnt seem to work, please see my code below

Here I map my excel values to my object.

 private static CleanSupplierClaim MappExcelOpenXMLtoGenericList(IList<string> rowData, IList<string> columnNames)
    {


        var cleanSupplierClaims = new CleanSupplierClaim()
        {
            Action = rowData[columnNames.IndexFor("Action")],
            Line_Number = rowData[columnNames.IndexFor("Lineno")],
            Total_Claim = rowData[columnNames.IndexFor("TotalClaim")].ToDecimalNullable(), //Convert.ToDecimal(Math.Round(Convert.ToDouble(rowData[columnNames.IndexFor("TotalClaim")].ToDoubleNullable()),2)),
            Currency = rowData[columnNames.IndexFor("Currency")],
            ClaimReference = rowData[columnNames.IndexFor("ClaimReference")]
        };

        return cleanSupplierClaims;
    }

Here I get the cell value and attempt to format the decimal type that I get from the file

private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    var culture = new CultureInfo("en-us");
    culture.NumberFormat.CurrencyDecimalDigits = 3;

    if (cell == null) return null;
    string value = cell.InnerText;

    //Process values particularly for those data types.
    if (cell.DataType != null)
    {
        switch (cell.DataType.Value)
        {
            //Obtain values from shared string table.
            case CellValues.SharedString:
                var sstPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                value = sstPart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText.Trim();
                break;

            //Optional boolean conversion.
            case CellValues.Boolean:
                var booleanToBit = ConfigurationManager.AppSettings["BooleanToBit"];
                if (booleanToBit != "Y")
                {
                    value = value == "0" ? "FALSE" : "TRUE";
                }
                break;






                //specifire = "F";
            case CellValues.Number:
                return Convert.ToDecimal(cell.CellValue.Text).ToString(culture);
            default:
                if (cell.CellValue != null)
                    return cell.CellValue.Text;
                return string.Empty;

        }
    }
    return value;
}

I have set the decimal field as decimal places of 3 in my sql database, but its still returning incorrect decimal value, well incorrect formatting.

Any ideas as to how I can fix this, kind regards

Answer 1

Hope this help:

 string stringNumber = "0.1230000";

 decimal decimalNumber = decimal.Parse(stringNumber, CultureInfo.InvariantCulture);//or another culture

 decimal roundedNuber = decimal.Round(decimalNumber, 3);