How to Read Excel file in C#

How to Read an Excel File using Microsoft.Office.Interop.Excel in C#

First, Make sure your computer has Microsoft Office software installed.

Next, Create a new Windows Forms application project, then drag and drop the DataGridView, Button controls from your Visual Studio Toolbox to your form designer.

Creating a class with properties corresponding to the columns in your excel file.

In this example, I use the customer table from the northwind database.

public class Customer
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Region { get; set; }
    public string PostalCode { get; set; }
    public string Country { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }
}

Rebuild your project, then add a bindingsource to the DataGridView.

You need to add a reference to Microsoft.Office.Interop.Excel in your project by right-clicking on your project in Solution Explorer > Add > Reference > COM > Microsoft Excel xx.x Object Library (xx.x depends on your installed version).

If you have installed Microsoft Office. You can easily find libraries in the directory .

C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\

Add the necessary using directive to your C# file

using Excel = Microsoft.Office.Interop.Excel;

Adding a click event handler to the Open button allows you to open excel file, then read data from excel file and load data into the DataGridView.

// c# read excel file
private void btnOpen_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" })
    {
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            //Initialize Excel application and open the workbook
            Excel.Application ExcelApp = new Excel.Application();
            Excel.Workbook workbook = ExcelApp.Workbooks.Open(ofd.FileName);
            Excel._Worksheet worksheet = workbook.Sheets[1];
            Excel.Range range = worksheet.UsedRange;
            int rowCount = range.Rows.Count;
            int colCount = range.Columns.Count;
            List<Customer> list = new List<Customer>();
            for (int i = 1; i <= rowCount; i++)
            {
                Customer customer = new Customer();
                for (int j = 1; j <= colCount; j++)
                {
                    if (range.Cells[i, j] != null && range.Cells[i, j].Value2 != null)
                    {
                        switch (j)
                        {
                            case 1:
                                customer.CustomerID = range.Cells[i, j].Value2.ToString();
                                break;
                            case 2:
                                customer.CompanyName = range.Cells[i, j].Value2.ToString();
                                break;
                            case 3:
                                customer.ContactName = range.Cells[i, j].Value2.ToString();
                                break;
                            case 4:
                                customer.ContactTitle = range.Cells[i, j].Value2.ToString();
                                break;
                            case 5:
                                customer.Address = range.Cells[i, j].Value2.ToString();
                                break;
                            case 6:
                                customer.City = range.Cells[i, j].Value2.ToString();
                                break;
                            case 7:
                                customer.Region = range.Cells[i, j].Value2.ToString();
                                break;
                            case 8:
                                customer.PostalCode = range.Cells[i, j].Value2.ToString();
                                break;
                            case 9:
                                customer.Country = range.Cells[i, j].Value2.ToString();
                                break;
                            case 10:
                                customer.Phone = range.Cells[i, j].Value2.ToString();
                                break;
                            case 11:
                                customer.Fax = range.Cells[i, j].Value2.ToString();
                                break;
                        }
                    }
                }
                list.Add(customer);
            }
            customerBindingSource.DataSource = list;

            GC.Collect();
            GC.WaitForPendingFinalizers();

            //release com objects to fully kill excel process from running in the background
            Marshal.ReleaseComObject(range);
            Marshal.ReleaseComObject(worksheet);

            //close and release
            workbook.Close();
            Marshal.ReleaseComObject(workbook);

            //quit and release
            ExcelApp.Quit();
            Marshal.ReleaseComObject(ExcelApp);
        }
    }
}

You need to create a COM object for everything that is referenced, then you can read from the sheet, keeping in mind that indexing in excel is not 0 based. So you just need to read the cells and print them back just like in the file.

Remember, using Microsoft.Office.Interop.Excel requires Excel to be installed on the machine where the application runs. Additionally, using interop can sometimes lead to memory leaks, so make sure to release COM objects properly.

Throught this post, you will learn how to read excel file in c# windows application. When you run the demo, you will see that the process of reading excel file is very slow. It's not a good solution when you want to read excel file in c#.