OleDB Jet - Float issues in reading excel data

Question

When I read a sheet into a DataTable using the OleDbDataReader, floating point numbers loose their precision.

I tried forcing OleDb to read the excel data as string, but although the data is now contained in a DataRow with each Column defined as System.String it looses precision (18.125 -> 18.124962832).

Any idea how to avoid this behaviour?

Answer 1

The output from the code below shows you how to get the underlying number and the formatted text with SpreadsheetGear for .NET:

Here is the output 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 the free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 2

I'm pretty sure that Jet tries to assign a datatype to each column based on what it sees in the first five rows. If something after those first five rows doesn't fall into that data type it will either convert it or return nothing at all.

Do the first five rows of your spreadsheet have a lower precision than the items that are begin truncated?

Take a look at this post.