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.

 

 

 

Save PDF file to local disk using MemoryStream

I found difficulty in saving a PDF file  to my local hard disk using a MemoryStream. Tried some ways and resulted with some errors like "Access is denied.","Cannot write as stream is in use.","There was an error in opening the document.As the document is already open or is in use."

I am sharing you the simple one line code which will over cross all these errors and you can save the file.

using (var stream = new MemoryStream())
{
    //Generate PDF bytes here using stream
    bytes = stream.ToArray();
    var fullpath = Path.Combine(@"C:\DestinationFolder", "PDFName");
    System.IO.File.WriteAllBytes(fullpath, bytes);
}

Reporting Services Merge Cells in a Row

With the “Expressions” approach outlined in this article ( http://itjungles.com/reporting-services/reporting-services-merge-cells). You should be able to easily and consistently create SSRS 2005 reports with vertical rows looking like they are merged.

All credits to the founders who found/discovered this work around and sharing it with the rest of us.