ADO.NET 3.5 Cookbook, 2nd Edition

1.13. Connecting to a Microsoft Excel Workbook

Problem

You want to connect to a Microsoft Excel workbook.

Solution

Use the OLE DB .NET data provider.

The solution creates and opens a connection to a Microsoft Excel workbook using the OLE DB .NET data provider. Information about the connection is displayed.

The Excel 2007 workbook in this solution is shown in Figure 1-13.

Figure 1-13. Excel workbook Category.xlsx

The Excel 2003 workbook used in this solution is identical.

The C# code in Program.cs in the project ConnectExcel is shown in Example 1-14.

Example 1-14. File: Program.cs for ConnectExcel solution

using System;
using System.Data; using System.Data.OleDb; namespace ConnectExcel { class Program { static void Main(string[] args) { // Define connection strings for both default // Excel .xlsx format and Excel 97-2003 .xls format string[] oledbConnectString = new string[] { "Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=..\..\..\Category.xlsx;" + "Extended Properties=\"Excel 12.0;HDR=YES\";", "Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=..\..\..\Category.xls;" + "Extended Properties=\"Excel 8.0;HDR=YES\";" }; foreach (string connectString in oledbConnectString) { // Define and open the connection OleDbConnection connection = new OleDbConnection(connectString); connection.Open( ); // Output some connection properties to the console Console.WriteLine("---CONNECTION---"); Console.WriteLine("Connection.String ...