Csharp/CSharp Tutorial/ADO.Net/Excel

Connecting to a Microsoft Excel Workbook

<source lang="csharp">using System; using System.Data; using System.Data.OleDb;

   class Program
   {
       static void Main(string[] args)
       {
           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)
           {
               OleDbConnection connection =new OleDbConnection(connectString);
               connection.Open(  );
               Console.WriteLine("Connection.String = {0}\n",connectString);
               Console.WriteLine("Connection.State = {0}",connection.State);
               Console.WriteLine("Connection.Provider = {0}",connection.Provider);
               Console.WriteLine("Connection.ServerVersion = {0}",connection.ServerVersion);
               connection.Close(  );
               
           }
       }
   }</source>

Retrieving Data from a Microsoft Excel Workbook

<source lang="csharp">using System; using System.Data; using System.Data.OleDb;

   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$]";
           OleDbConnection connection = new OleDbConnection(oledbConnectString);
           OleDbCommand command = new OleDbCommand(commandText, connection);
           connection.Open();
           OleDbDataReader dr = command.ExecuteReader( );
           while (dr.Read()){
               Console.WriteLine(dr["CategoryID"]);
               Console.WriteLine(dr["CategoryName"]);
               Console.WriteLine(dr["Description"]);
           }
           connection.Close( );
       }
   }</source>