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#.