How can I use named range in Excel with OleDB?

Question

I'm trying to extract data from a specific named range in Excel with ASP .NET/C#. Here is an exemple of what I'm trying to extract.

What I want is "B", "C", "D" by using the name "RANGE_NAMED". Is it possible to do this with OleDB ?

Best regards,

Alex.

Conative

Answer 1

You could try this code

using(OleDbConnection c = new OleDbConnection(con))
{
    c.Open();
    string selectString = "SELECT * FROM [RANGE_NAMED]";
    using(OleDbCommand cmd1 = new OleDbCommand(selectString))
    {
          cmd1.Connection = c;
        var result = cmd1.ExecuteReader();
        while(result.Read())
        {
              Console.WriteLine(result[0].ToString());
        }
    }
}

Squishy

As I understand the query is only picking up the text data from the range. Can this be used to perform HasFormula on the cells in the range? – Itchy

If you refer to this then I think no because this is not used via Interop but just OleDb – Squishy

I have seen that link, and just wondering whether there's such properties within OleDb. If in anyway we could serialized the range taken into the above result and deserialize it into an excel range in C# while reading via the reader. I am just thinking out loud - if you can point out any relevant info, that's great. – Itchy

I can’t get this to work. It skips the while loop. I only have one value in my named range, does that effect it? – Tersina

Answer 2

Ok, It's was obvious and I don't know why it didn't work the first time...

SELECT * FROM RANGE_NAMED

And I get B, C, D.

Conative