Import excel file using c#.net

Question

I need to import an excel and have to do some process on it. Those excel data should be retrieved using C#. While i was googled, got confused. Could anybody provide the c# code to achieve it?

Thanks in advance.

Answer 1

Using OleDB:

using System.Data.OleDb;

private void readExcel(string pExcelPath, string pSheetName)
        {
            DataTable sheet1 = new DataTable();
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = pExcelPath;
            if (excelFirstRowIsHeader == true)
                csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
            else
                csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=NO");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string sqlQuery = @"SELECT * FROM [" + pSheetName + "]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(sqlQuery, connection))
                {
                    adapter.Fill(sheet1);
                    excelData_dataGridView.DataSource = sheet1;
                }
                connection.Close();
            }
        }

This code supports Excel-files created with Excel 2007/2010. The boolean excelFirstRowIsHeader is used to specifiy if your excel contains a header row (if the first row in your excel sheet is used a header)

You can also use Interop-Assembly to read an excel file, but therefor MS Excel must be installed.