Read Excel File Using Microsoft Interop Library

In this article, we will learn how to read an excel file using Microsoft’s Interop excel library. There are lots of free/Paid excel libraries available in Nuget manager. You can check my other article of same excel series with other excel library. We will make a new windows application using C# to implement this. Let’s start.

Step 1

Open Visual Studio. Select New Project, then Windows Form Application.

Step 2

Name it as you want. My application's name is ReadExcelFileApp.

Step 3

First of all, add a Reference of Excel library. Right click on the Reference in the solution explorer.

Assemblies, Extensions, then Microsoft.Office.Interop.Excel.

If you are using Visual Studio 2010, then you can add Reference using two ways:

Right click on References then select Add Reference. After that a dialog appears,

1. Click on Browse button, C drive, Microsoft Office, Office12, then open EXCEL.EXE.
2. Select .NET tab then choose Microsoft.Office.Interop.Excel.

Step 4

After adding Reference add namespace using Excel = Microsoft.Office.Interop.Excel; and other namespaces as shown in the figure.

Step 5

Add two buttons Choose and Read File and Close.

Step 6

Add a DataGridView to see the result (excel data).

Step 7

Create a method ReadExcel that returns a datatable using the following logic.

public DataTable ReadExcel(string fileName, string fileExt) {
    string conn = string.Empty;
    DataTable dtexcel = new DataTable();
    if (fileExt.CompareTo(".xls") == 0) conn = @ "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
    else conn = @ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007
    using(OleDbConnection con = new OleDbConnection(conn)) {
        try {
            OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
            oleAdpt.Fill(dtexcel); //fill excel data into dataTable
        } catch {}
    }
    return dtexcel;
}

Step 8

Let's discuss something about ReadExcel() method.

Firstly, we will decide whether the file has extension .xls or .xlsx because there is a difference between the connection strings of both files.

If the file has extension .xls, then the connection string will be the following:

provider=Microsoft.Jet.OLEDB.4.0;Data Source='fileName';Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';

Otherwise

Provider=Microsoft.ACE.OLEDB.12.0;Data Source='FileName';Extended Properties='Excel 12.0;HDR=NO';"

Here HDR is the header field, depends upon you, whether you want to add it or not,

IMEX=1 is used to retrieve the mixed data from the columns.

Now by using the OleDbConnection define an OleDbDataAdapter.

OleDbDataAdapter oleAdpt =newOleDbDataAdapter("select * from [Sheet1$]", con);

Here Sheet1 is the sheet number that you want to select, you can select any sheet e.g. Sheet2, Sheet3, etc. If you want to choose some specific columns, then you can. For example, if you want to read just 2 columns say Name and Salary from the excel file then your query be like the following:

Select Name,Salary from [Sheet1$]

If there are no headers in complex excel files then you can select columns like F1, F20 etc. In that case, the query is like the following:

Select F11,F41,F70 from [Sheet1$]

Step 9

Add the following logic in button click events.

private void btnChooseFile_Click(object sender, EventArgs e) {
    string filePath = string.Empty;
    string fileExt = string.Empty;
    OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
    if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file chosen by the user
    {
        filePath = file.FileName; //get the path of the file
        fileExt = Path.GetExtension(filePath); //get the file extension
        if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0) {
            try {
                DataTable dtExcel = new DataTable();
                dtExcel = ReadExcel(filePath, fileExt); //read excel file
                dataGridView1.Visible = true;
                dataGridView1.DataSource = dtExcel;
            } catch (Exception ex) {
                MessageBox.Show(ex.Message.ToString());
            }
        } else {
            MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
        }
    }
}
private void btnClose_Click(object sender, EventArgs e) {
    this.Close(); //to close the window(Form1)
}

Step 10

After choosing the file Result will be like the following whether you upload .xls or .xlsx file.