Read all cells - in all sheets - of an excel file

[shokoohkanani0]

Hi,
As my post's title indicates, I wanna read all cells of all sheets/pages in my excel file and just show the text content of each cell.
I have written my code but have 2 problems:

-

Dim ExcelConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Excel.xls;Extended Properties=Excel 8.0;")
ExcelConnection.Open()
Dim MyCommands As New OleDbCommand("SELECT * FROM [Sheet1$]", ExcelConnection)
Dim MyReader As OleDbDataReader = MyCommands.ExecuteReader
While MyReader.Read()
    'How should I read cells here?
End While
MyReader.Close()
ExcelConnection.Close()
 

-

1. How can I read cells inside the While?
2. My select command only selects Sheet1, how can I select ALL sheets?
Thank you guys/gals, as I am not expert in database.

[Curtis Rutland]

OK, I found a way for you to get the "schema" of the spreadsheed. Basically, this method takes your connection string, and returns an array of strings that is the list of your tables (sheets).

It's in C#, but you should be able to translate it.

static string[] GetSchemaTable(string connectionString)
{
    using (OleDbConnection connection = new
               OleDbConnection(connectionString))
    {
        connection.Open();
        DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        List<string> tableList = new List<string>();
        foreach (DataRow dr in schemaTable.Rows)
        {
            if (dr[3] as string == "TABLE")
                tableList.Add(dr[2] as string);
        }
        return tableList.ToArray();
    }
}

Now that you have all the table names, you could either do a UNION statement (bad idea if the sheets don't have exactly the same headers) or you could just do a loop, selecting all from each table.

As to the reading the cells, take a look at these articles:

• How To Use A Database In Your Program
• How To Use A Database In Your Program Part II

Just remember that anything that is done with the SqlDataReader can be done with the OleDbReader.

[Frinavale]

VB Code of iA's example

 Function GetSchemaTable(ByVal connectionString As String) As String()
        Dim connection As New OleDb.OleDbConnection(connectionString)
 
        connection.Open()
        Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
        Dim tableList As New List(Of String)
        For Each dr As DataRow In schemaTable.Rows
            If (String.Compare(CType(dr(3), String), "TABLE", True) = 0) Then
 
                tableList.Add(CType(dr(2), String))
            End If
        Next
        Return tableList.ToArray()
    End Function