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 );
}