Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
by David Hayden ( Sarasota ASP.NET C# Developer )
I received some really interesting feedback and questions regarding the following tutorial where I discussed reading and writing to an Excel Workbook using ADO.NET:
One of the questions was on how to find the worksheets and columns in those worksheets that existed in the Excel Workbook. Great question, because the ADO.NET 2.0 DbConnection Class has this wonderful method called GetSchema that can help you find such information. I wrote an overview of GetSchema here:
Finding Worksheets in Excel Using ADO.NET and GetSchema
Let's take an example Excel Workbook that contains four worksheets (Planets, Countries, States, Cities).
We can write the following code that read the names of the worksheets in the Excel Workbook, called Book1.xls, as follows:
DataTable worksheets;
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";
using (OleDbConnection connection =
new OleDbConnection(connectionString))
{
connection.Open();
worksheets = connection.GetSchema("Tables");
}
Setting a breakpoint and looking at the worksheets datatable using the DataSet Visualizer provides a list of the Excel worksheets:
I haven't looked into why Excel appends a $ character at the end of the worksheet name as displayed in the workbook, but it does :)
Finding Excel Worksheet Columns using ADO.NET and GetSchema
Using the same workbook mentioned above, we can find the names of the columns in the Cities$ Excel Worksheet using the following code:
DataTable columns;
string[] restrictions = {null, null, "Cities$", null};
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";
using (OleDbConnection connection =
new OleDbConnection(connectionString))
{
connection.Open();
columns = connection.GetSchema("Columns", restrictions);
}
Viewing the columns datatable using the DataSet Visualizer gives us the following:
I truncated the image above as it has too much information to display on my blog. However, as you can see, you can retrieve the names of the columns for each Excel worksheet without knowing them ahead of time. This can be mighty useful, for example, if you want to create a generic routine that extracts Excel Workbook information to a CSV file.
Conclusion
Using ADO.NET and the DbConnection's GetSchema Method you can read schema information from Excel Spreadheets.
Source: David Hayden ( Sarasota ASP.NET C# Developer )
Filed: ADO.NET 2.0 Tutorials