OleDB Jet - floating point problems while reading Excel data

Question

When I read a worksheet in a DataTable using OleDbDataReader, floating point numbers lose their accuracy.

I tried to get OleDb to read excel data as a string, but although the data is now contained in the DataRow with every column defined as System.String, it loses accuracy (18.125 → 18.124962832).

Any idea how to avoid this behavior?

Answer 1

I just checked your details and this method posted a work here.
that is, the value of the cell maintained the accuracy of 18.124962832 when entering into the DataSet.

Answer 2

I'm sure Jet is trying to assign a data type to each column based on what it sees in the first five rows. If something after the first five lines does not fall into this data type, it either converts or returns nothing.

Do the first five rows of your table have less precision than elements that start with truncation?

Take a look at this post.

Answer 3

The result from the code below shows how to get the base number and formatted text using SpreadsheetGear for .NET :

Here is the result from the code:

x=18.124962832 y=18.124962832 formattedText=18.125 

Here is the code:

 namespace Program { class Program { static void Main(string[] args) { // Create a new workbook and get a reference to Sheet1!A1. var workbook = SpreadsheetGear.Factory.GetWorkbook(); var sheet1 = workbook.Worksheets[0]; var a1 = workbook.Worksheets[0].Cells["A1"]; // Put the number in the cell. double x = 18.124962832; a1.Value = x; a1.NumberFormat = "0.000"; double y = (double)a1.Value; string formattedText = a1.Text; System.Console.WriteLine("x={0} y={1} formattedText={2}", x, y, formattedText); } } } 

You can see live SpreadsheetGear samples here and download a free trial.

Disclaimer: I have SpreadsheetGear LLC