Which NumberStyles to use in order to have the right number conversion?

29 Views Asked by At

I'm reading value from an excel sheet. The correct value of the cell is -0.09 but excel reads it as "-9.3299999999999994E-2" I created a method handling regional setting (comma and period in number format)to convert value correctly and to handle Excel misunderstanding .

Every thing works fine till the last instruction where I do the conversion via the NumberStyles the result of the last instruction is -933 instead of -0.09

What is the change I need to do in order to have the right answer ?

static void Main(string[] args)
    {
        String data = "-9.3299999999999994E-2";
        double number = 0.0;

        string s = Regex.Replace(data, @"\s", "");

        //Handle scientif Notation Coming from Excel e.g: 1.234567E-06
        if (s.ToUpper().Contains("E"))
        {
            try
            {
                number = double.Parse(s.Trim(), CultureInfo.InvariantCulture);
                s = number.ToString();
            }
            catch (FormatException e)
            {
                throw new FormatException();
            }
        }
        var cultureInfo = CultureInfo.InvariantCulture;
        // if the first regex matches, the number string is in us culture
        if (Regex.IsMatch(s, @"^(:?[\d,]+\.)*\d+$"))
        {
            cultureInfo = new CultureInfo("en-US");
        }
        // if the second regex matches, the number string is in de culture
        else if (Regex.IsMatch(s, @"^(:?[\d.]+,)*\d+$"))
        {
            cultureInfo = new CultureInfo("de-DE");
        }
        NumberStyles styles = NumberStyles.Number ;

        bool isDouble = double.TryParse(s.Trim(),styles, cultureInfo, out number);
        if (!isDouble) //if conversion fails            
            throw new FormatException();

        System.Console.WriteLine ( number );
}
0

There are 0 best solutions below