Whether you are working on Windows or on Web or on Console application, at one stage we need to process data and excel file is iwdely used for it, so in this article, I am going to provide you C# code to open and read excel file (.xls) line by line using Console application and OLEDB, you can use the same code C# code to fill ASP.NET GridView or MVC application table.

So, let's get started with it.

Step 1: Create a new Console applicaiton in your Visual Studio, by navigating to File->New->Project-> Select "Windows Classic dekstop" from left-pane & "Console-App" from right-pane -> Provide a name to your application "CSharpReadExcelFile" -> Click "OK"

c-sharp-read-excel-min.png

Step 2: Now we have our Console application and we need to add C# code using OLEDB to read excel file, for that we would need connection string with the source URL of excel file.

In the given example as I am using .XLS excel file, here is my connection string

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Sample1.xls; Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";

For Excel 97-2003 Format we can use “Microsoft Jet OLEDB Driver 4.0”, while for the Connection String for Excel 2007 Format (.XLSX), we can use “Microsoft Ace OLEDB Driver 12.0” and it's connection string would be as below

 string connString = "Provider= Microsoft.ACE.OLEDB.12.0;" + "Data Source=Sample1.xlsx" + ";Extended Properties='Excel 8.0;HDR=Yes'";

In the above Connection string's you may see extended properties HDR=Yes & HDR =No

Use HDR=YES if first excel row contains headers, alternatively,use HDR=NO when your excel's first row is not headers and it's data.

Now, we have connection string , we need to create connection using OLEDB and open it

             // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
           
            // Open connection
            oledbConn.Open();

Read the excel file using OLEDB connection and fill it in dataset

  //here sheet name is Sample-spreadsheet-file, usually it is Sheet1, Sheet2 etc..
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sample-spreadsheet-file$]", oledbConn);

                // Create new OleDbDataAdapter
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds, "Employees");

in the above code Sample-spreadsheet-file is the name of Sheet.

Note: With the help of sheet name, you can refer to Excel data, you need to use '$' with sheet name, e.g. Select * from [Sheet1$]

Now loop through each row of excel sheet and print it in Console app

 //loop through each row
                foreach(var m in ds.Tables[0].DefaultView)
                {
                    Console.WriteLine(((System.Data.DataRowView)m).Row.ItemArray[0] +" "+((System.Data.DataRowView)m).Row.ItemArray[1] +" "+((System.Data.DataRowView)m).Row.ItemArray[2]);

                }

If you are using ASP.NET, you can bind it with Grid View using below code instead of printing it

            // Bind the data to the GridView
            GridView1.DataSource = ds.Tables[0].DefaultView;
            GridView1.DataBind();

Now, we have discussed each step, suppose this is out Excel file

/sample-xls-file-read-csharp-min.png

and ese the code below in your Console application, build and execute it.

using System;
using System.Data;
using System.Data.OleDb;


namespace CSharpReadExcelFile
{
    class Program
    {
        static void Main(string[] args)
        {
            //this is the connection string which has OLDB 4.0 Connection and Source URL of file
            //use HDR=YES if first excel row contains headers, HDR=NO means your excel's first row is not headers and it's data.
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Sample1.xls; Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";
          

            // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                // Open connection
                oledbConn.Open();

                // Create OleDbCommand object and select data from worksheet Sample-spreadsheet-file
                //here sheet name is Sample-spreadsheet-file, usually it is Sheet1, Sheet2 etc..
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sample-spreadsheet-file$]", oledbConn);

                // Create new OleDbDataAdapter
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds, "Employees");

                //loop through each row
                foreach(var m in ds.Tables[0].DefaultView)
                {
                    Console.WriteLine(((System.Data.DataRowView)m).Row.ItemArray[0] +" "+((System.Data.DataRowView)m).Row.ItemArray[1] +" "+((System.Data.DataRowView)m).Row.ItemArray[2]);

                }
           
            }
            catch (Exception e)
            {
                Console.WriteLine("Error :" + e.Message);
            }
            finally
            {
                // Close connection
                oledbConn.Close();
            }
        }
    }
}

Output of the above code will be as below

csharp-read-excel-file-xls-min.png

Now, if you are working on 64 bit operating system, you may get this error "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.".

Resolving this error: If your application is Desktop based, compile your EXE with x86 CPU (Menu Tools, Options, select Projects And Solutions, check the show advanced build configurations. Now in the Build Menu you will be able to go to the Config Manager and set output to x86.)

If your application is web based, then Enable '32-Bit Applications' in application pool.

On IIS, change the "Enable 32-bit Applications" setting to True, in the Advanced Settings for the Application Pool.

Disadvantage of using OLEDB for Excel

With OLEDB, you cannot format data that you inserted/updated in EXCEL sheet but Interop can do it efficiently. You cannot perform any mathematical operation or working on graphs using OLEDB, but it is really a good way to insert/update data in EXCEL where no Excel application is installed.

That's it, feel free to provide your feedback in the below comment's section.