none
Reading Excel File content with Microsoft.ACE.OLEDB.12.0 gets a different cell decimal value depending on if file is open or closed RRS feed

  • Question

  • I’m reading a worksheet content into a DataTable with this connection string:

    new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + "; Extended Properties=\"Excel 12.0;HDR=" + paramHDR + ";IMEX=1;\""))

    Then, I’m looping through the DataRows and converting the object at each position to string.

    Everything works fine, but I’m having an issue reading a particular cell:

    I expect an integer value at that specific column, and sometimes my app reads “210.5” and sometimes it reads “211”.

    In the Excel file, when you select this particular cell, in the formula bar you can see it has a decimal part, “210.5”, but since the cell format has zero decimal places, the cell itself shows 211 in the worksheet.

    I also realised my app reads “210.5” when it runs while the Excel file is open at the same time the app is running, and it reads “211” if it is closed.

    I found it curious, maybe anyone can give me some hint on the issue.

    Just in case it’s useful, paramHDR=YES and my first 17 lines for that specific column are empty, but subsequent 1000 have an integer value (but this specific cell) which actually displays an integer, but its value has a decimal value.

    I'm working on framework 2.0 and with Visual Studio 2008.

    Thank you in advance.


    Mary

    • Moved by Fred BaoModerator Tuesday, December 9, 2014 9:49 AM Move to a more appropriate forum
    Friday, December 5, 2014 9:36 AM

All replies

  • Hello Mary,

    According to your description, I create a test demo with VS2013, .NET 2 and Excel 2013 with below demo:

    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\BMX\Project\2014\12\ADO.NETs\ADO.NET\bin\Debug\Order.xlsx; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;\'");
    
                try
    
                {
    
                    conn.Open();
    
                    OleDbCommand cmd = new OleDbCommand("select * from [Order$]", conn);
    
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    
                    DataSet ds = new DataSet();
    
                    da.Fill(ds);
    
                }
    
                catch (Exception)
    
                {
    
                }
    

    I am not sure if we are on the same page since I do not know what Excel you are using, however, the test shows that it could return “210.5” even I close the Excel, this is the test Excel, to reproduce this issue, I set the column has no decimal value although its value is “210.5”

    Since we cannot reproduce it, could you please provide a demo excel file? Or please have a try to set this column to General type so that it shows 210.5 in cell directly.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 8, 2014 5:59 AM
    Moderator
  • Thank you very much for your quick answer. I forgot to mention I’m using Excel 2010. Unfortunately, I shouldn’t modify the Excel file since it’s coming as an input from a third app.

    Anyway, I can tell you cell format is Number and decimal places are zero, that’s why it’s showing an integer although formula bar shows decimals when this particular cell is selected.  I hope this can help you reproduce this issue.


    Mary

    Tuesday, December 9, 2014 8:08 AM
  • Hello Mary,

    Thanks for clarification, I would try to test with Excel 2010, it may take some time and I would post back as soon as I get any result.

    Update:

    As your description, i also have tried to test with the Excel 2010 with cell format to be Number and decimal places are zero, the readed value is always 210.5:

    So i think it should be ok even with excel 2010. if it is possible, coudl you please share an demo excel file.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, December 10, 2014 9:57 AM
    Moderator
  • Hello Fred,

    I noticed your code and mine are slightly different, I tried using yours and I still have the same problem: I read an integer when file is closed and decimal when open. Here you are my code in order you can check if that's the root of the issue. (That's the result when excel file is closed)

    And just below, a screenshot of the Excel file I tried with:

    Screenshot of Excel file

    I hope this helps :)

    Thank you in advance.Used code in order to read the Excel file


    Mary

    Monday, December 15, 2014 10:48 AM
  • Hello Mary,

    With your new clarification, I created a test with the mixed data, and reproduced this issue successfully.

    After searching for a while, it seems that the Excel would have an inside service that would expose the real value, however, I am not sure since I am not an Excel expert. You could confirm it on Excel forum:

    https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

    Since the OleDb way could not process this scenario, you could use the Microsoft.Office.Interop.Excel to read excel files.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 16, 2014 8:14 AM
    Moderator
    • Edited by mary4wpf Tuesday, December 16, 2014 10:14 AM
    Tuesday, December 16, 2014 10:13 AM