Windows Forms: Read Excel file in C#

You can easily read data from excel file in c# using Microsoft.Office.Interop.Excel. Make sure your computer has Microsoft Office software installed.

Dragging DataGridView, Button 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.

Right-clicking on References, then select Add Reference... You need to add a reference to the Microsoft.Office.Interop.Excel.dll.

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

C:\Windows\assembly\GAC_MSIL\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.

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)
        {
            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);
        }
    }
}

First, 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.

And don't forget to include the namespace below to your form.

using Excel = Microsoft.Office.Interop.Excel;

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. This is not a good solution when you want to read excel file in c#.