ExcelDataReader.AsDataSet() converts single fraction double value into multiple fractions

Question

I'm facing a problem when reading the excel-sheet data using ExcelDataReader in c#. I am reading data from excel-sheet(.xlsm) One of the cell has a list of values to choose.

Eg.

5.1

5.2

5.1a

When I choose the value either 5.2 or 5.1a and read, I get the same exact value in the dataset

But when I choose 5.1 and read, I get 5.0999999999999996 in the dataset

Here is the code which I used to read the data in c#,

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);

DataSet findingsData = excelReader.AsDataSet();

Note : For a workaround, I put a space after the value 5.1 in the cell. Then it read the value exactly same as expected(5.1 instead of 5.0999999999999996).

But I'm wondering, when it read the value 5.2 exactly same without applying any space, why doesn't work for 5.1?

Any suggestions are welcome to resolve this issue...

Thanks,

Karthik

Answer 1

Take a look at this question: Why can't decimal numbers be represented exactly in binary?

My maths isn't quite up to figuring it out precisely (comments welcome) but I suspect that 5.1 doesn't convert to the C# double precisely, but 5.2 does.

The reason it works when you add the space is that Excel will assume that the field is text, the same way 5.1a is, but when it receives something that looks like a number it assumes it is a number. (You can see this behaviour in a default blank spreadsheet as it will be right aligned if it is a number and left aligned when you add a space or any other text).

I expect that if you explicitly format all the cells as text in your source spreadsheet then the value will be read as you expect