Read large data from Excel in C#

There are a lot methods to read data from excel sheet from c#. Here am mentioning some of the methods i have used. I have got a scenario where i want to read data from a excel sheet which consists of more than 2 lakhs records more than 20 columns. I have tried a lot but all of them are taking more time in reading(around 8 mins). So i have found a solution using interop which i can read large data in less than a minute.

Using Excel Interop

 

The below method effectively reads the excel data in a faster way if you want to read large data from the excel.

 

Add namespace "using Excel = Microsoft.Office.Interop.Excel;"

 

            Excel.Application objApplication = new Excel.Application();
            Excel.Workbook objWorkbook = objApplication.Workbooks.Open(filePath);
            Excel.Worksheet objWorksheet = objWorkbook.Worksheets[sheetName];

            DataTable oDatatable = new DataTable();
            try
            {
                object[,] sheetEntries;
                sheetEntries = objWorksheet.get_Range(startRange + ":" + endRange, Type.Missing).Value2;

                oDatatable = ConvertArrayToDataTable(sheetEntries);

                return oDatatable;
            }
            catch (Exception ex)
            {
               
            }
            finally
            {
                objWorkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
                objWorkbook = null;
                objApplication.Quit();
                objApplication = null;
                oDatatable.Dispose();
            } 

 

Pass the sheet name, start and end range of the excel sheet data. Here ConvertArrayToDatTable is a method which converts array into a datatble. you can find many ways to do that.

 
 
Using Oledb Connection

using (OleDbConnection oConnection = new OleDbConnection())
{
       oConnection.ConnectionString = connectionString;
       using (OleDbCommand comm = new OleDbCommand())
       {
         comm.CommandText = "Select * from [" + sheetName + "$" + startRange + ":" + endRange + "]";
         comm.Connection = oConnection;
         using (OleDbDataAdapter oOleDbDataAdapter = new OleDbDataAdapter())
         {
             oOleDbDataAdapter.SelectCommand = comm;
             oOleDbDataAdapter.Fill(oDataTable);
             return oDataTable;
        }
     }
}

 

Pass the sheet name, start range and end range of the excel sheet cell range from where you want to read.