ADO.NET 3.5 Cookbook, 2nd Edition

3.30. Retrieving Data from a Microsoft Excel Workbook

Problem

You want to access data stored in a Microsoft Excel workbook.

Solution

Use the Microsoft Access Database Engine OLE DB provider to create, access, and modify data stored in an Excel workbook.

The solution creates an OleDbDataAdapter that loads the Excel workbook Category.xlsx, shown in Figure 3-33, into a DataTable and outputs the contents of the DataTable to the console.

Figure 3-33. Excel workbook Category.xlsx

The C# code in Program.cs in the project ReadExcelData is shown in Example 3-43.

Example 3-43. File: Program.cs for ReadExcelData solution

using System; using System.Data; using System.Data.OleDb; namespace ReadExcelData { class Program { static void Main(string[] args) { string oledbConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=..\..\..\Category.xlsx;" + "Extended Properties=\"Excel 12.0;HDR=YES\";"; string commandText = "SELECT CategoryID, CategoryName, " + "Description FROM [Sheet1$]"; Console.WriteLine("---CONNECTION---"); Console.WriteLine(oledbConnectString); OleDbConnection connection = new OleDbConnection(oledbConnectString); OleDbCommand command = new OleDbCommand(commandText, connection); connection.Open( ); OleDbDataReader dr = command.ExecuteReader( ); Console.WriteLine("\nID Name Description"); while (dr.Read( )) { Console.WriteLine("{0} {1} {2}", dr["CategoryID"], dr["CategoryName"].ToString( ...