OLEDB not reading formula cells correctly.

Question

Hi,

I am having some issues when I am trying to read Excel file using OleDB, I am having issues with the way it is read. I have IMEX=1 and HDR=Yes; Eg: Sheet1 in Col A has some values, say the first 15 rows are blank in this, and there are dropdown values from row 16(the dropdown can have only 'X');  Sheet2  Col A is linked to Col A of Sheet 1. Now if Sheet1 has a value in say 16th row, when I read it using OleDB, I am getting '0' for the 15 rows and row 16 is coming as blank for Sheet2. Not sure how I can fix this, I need to read values from only Sheet2. This is a very critical issue for me, could you please help me with this. Thanks.

---

Rpaul

Answer 1

Hi RPaul18,

Welcome to MSDN Forum.

Could you please show some code snippet for us to repro the isseu? This is, so we can help you more effectively. : )

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Allen,

Thanks for replying.

This is the code I have for reading the data:

string additionaldata = ";Extended Properties='Excel 12.0 Macro;HDR=No;IMEX=1'";

connString =

"Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=" + filePath + additionaldata;This works in all the scenarios except for the scenario that I mentioned. Thanks.

---

Rpaul

Answer 3

Only the connection string couldn't help to repro the issue, please show more code. Or, if possible, upload a demo and post the url here.

---

Go go Doraemon!

Answer 4

Hi RPaul18,

Could you please post the code which read the excel, this is, so we can help you more effectively. As @Dorado999 said, only connection string couldn't let us repro the issue. : )

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 5

Hello, The following example (using VS2008, VB.NET) shows first getting a value in Sheet2 A1:A1 where this is the SUM of Sheet1 A1:A5 then gets a value of A1 on Sheet1.

Sheet2 A1 Formula

=SUM(Sheet1!A1:A5)

Imports System.Data.OleDb
Public Class Form1
    Private Builder As New OleDbConnectionStringBuilder With _
    { _
        .Provider = "Microsoft.ACE.OLEDB.12.0", _
        .DataSource = IO.Path.Combine(Application.StartupPath, "Book1.xlsx") _
    }
    Private Sub Button1_Click( _
    ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
        Using cn As New OleDbConnection With _
            {.ConnectionString = Builder.ConnectionString}
            Using cmd As New OleDbCommand With _
                {.Connection = cn, .CommandText = <SQL>SELECT F1 FROM [Sheet2$A1:A1]</SQL>.Value}
                cn.Open()
                Dim Reader As OleDbDataReader = cmd.ExecuteReader
                If Reader.HasRows Then
                    Reader.Read()
                    Console.WriteLine(Reader.GetDouble(0))
                End If
                Reader.Close()
                cmd.CommandText = "SELECT F1 FROM [Sheet1$A1:A1]"
                Reader = cmd.ExecuteReader
                If Reader.HasRows Then
                    Reader.Read()
                    Console.WriteLine(Reader.GetDouble(0))
                End If
            End Using
        End Using
    End Sub
    Private Sub Form1_Load( _
    ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
        Builder.Add("Extended Properties", "Excel 12.0; HDR=No;IMEX=1")
    End Sub
End Class

---

KSG

Answer 6

Hi,

Thanks very much for the help so far, the problem I have is more of a generic one I believe. I am reading a lot of columns - Say A10:AZ from more than 1 sheet, this is how I mention the range since I do not know the end of the rows present that I need to read in each sheets, I do not have any problem in reading the values and executing it.

But the issue is - say if I have 25 rows of data that I need to read for the mentioned range, and say column Z in sheet 2 is linked to a formula that is in sheet1(eg: =Sheet1!F10) and so on for the rest of the rows. Now the value in Sheet1 column F has a drop down which can accept only the value 'X'; and sheet2 Column Z is just made to read this value in sheet 1. Now the issue is - when there are no values in the first 15 rows in sheet 1 then when I read sheet 2 and get that value in a dataset, I am seeing only '0' for column Z in sheet 2 and when there is a value in Sheet 1 in row 20, then this comes as blank when I read sheet2. This is the problem I have. Any help is much appreciated.

---

Rpaul

Answer 7

Hi,

Thanks very much for the help so far, the problem I have is more of a generic one I believe. I am reading a lot of columns - Say A10:AZ from more than 1 sheet, this is how I mention the range since I do not know the end of the rows present that I need to read in each sheets, I do not have any problem in reading the values and executing it.

But the issue is - say if I have 25 rows of data that I need to read for the mentioned range, and say column Z in sheet 2 is linked to a formula that is in sheet1(eg: =Sheet1!F10) and so on for the rest of the rows. Now the value in Sheet1 column F has a drop down which can accept only the value 'X'; and sheet2 Column Z is just made to read this value in sheet 1. Now the issue is - when there are no values in the first 15 rows in sheet 1 then when I read sheet 2 and get that value in a dataset, I am seeing only '0' for column Z in sheet 2 and when there is a value in Sheet 1 in row 20, then this comes as blank when I read sheet2. This is the problem I have. Any help is much appreciated.

---

Rpaul

I would say this is complicated rather than generic. Reading back thru your last reply sounds like if you are intending to read from multiple sheets at a time and don't know the last row in some cases that this process needs to be written down prior to writing any code and review the process. Break down into smaller sections things like instead of reading from multiple sheets read one sheet than another and populate DataTable objects as needed. When you don't know how many rows to read you can test by reading a sheet i.e. SELECT F1 FROM Sheet1$ (where in this case HDR=No and F1 indicates A column), when in a DataTable query the Row Count which will indicate how many rows there are to read. You might learn that using OleDb methods are not the best and may test working with Excel Automation instead but than again nothing says this will be any easier but simply a better route to take. In Excel Office automation there are methods say for finding the last row in a column unlike using the idea I presented for OleDb. Also you need to be aware of properly working with and disposing of any objects used with Excel automation. If for any reason your code crashes Excel is left in memory, if you fail to dispose of objects on your own, same thing. The benefits are you are working with native methods. There are tons of examples out there for Excel automation.

Resource for VB.NET, Excel (I assisted with #24 see also)

http://siddharthrout.wordpress.com/vb-net-and-excel/

VB.NET Sample project for learning the basics of Excel automation http://code.msdn.microsoft.com/Basics-of-using-Excel-4453945d

Hope these suggestions are of some use along with code example.

---

KSG

Answer 8

Hi,

I am sorry if my previous posts were misleading, I will re-phrase my question. All I am doing is - Reading multiple sheets, in the process I notice, say I am reading Sheet 1 & 2, in which Sheet 2 Column A is linked to Sheet 1 Column A; using Oledb, if I read sheet 2, then for Column A I am seeing all zero values if there are no values in Sheet 1 Column A, but if there are values it comes correctly. Why is it reading as Zero and not blank for sheet 2? What should I do to get blanks and not Zero values.

---

Rpaul