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.

Calling a code behind function from JQuery

function TestFunction(ddlType){
    
    $.ajax({
        type: "POST",
        contentType: "application/json; charset=utf-8",
        url: "../Script Services/AutoComplete.asmx/SampleWebMethod",
        datatype: "JSON",
        parameters: { param1: ddlType },
        data: "{intTypeID: " + intTypeID + "}",
        success: function (response) {
            TestFunctionOnSuccess(ddlType, response.d);
        }
    });}

[WebMethod(EnableSession = true)]
[System.Web.Script.Services.ScriptMethod]
        public SampleObject[] SampleWebMethod(int? intTypeID)
        {
            DataTable dt = new DataTable();
            dt = ProjectBusinessLayer.Type.TypeSelect(intTypeID.Value);

            List<SampleObject> Details = new List<SampleObject>();
            foreach (DataRow dtrow in dt.Rows)
            {
                string TestID = dtrow["TestID"].ToString();
                string TestValue = dtrow["TestValue"].ToString();
                Details.Add(new SampleObject(TestID, TestValue));
            }
            return Details.ToArray();
        }

function TestFunctionOnSuccess(ddlType, lstTestDetails) {

// Do the functionality you need

}

Replace Header and Footer in an existing word document using Open xml sdk

i have a scenario where i have to replace header and footer of an existing document. i have found an article in msdn 

http://msdn.microsoft.com/en-us/library/office/cc546917.aspx i have used this code with modifications to replace both header and footer.

private void ReplaceHeaderAndFooter(WordprocessingDocument docx, byte[] byteHeaderArray, byte[] byteFooterArray)
        {
            docx.MainDocumentPart.DeleteParts(docx.MainDocumentPart.HeaderParts);
            docx.MainDocumentPart.DeleteParts(docx.MainDocumentPart.FooterParts);

            DocumentFormat.OpenXml.Packaging.HeaderPart headerPart = docx.MainDocumentPart.AddNewPart<HeaderPart>();
            DocumentFormat.OpenXml.Packaging.FooterPart footerPart = docx.MainDocumentPart.AddNewPart<FooterPart>();

            string strHeaderPartID = docx.MainDocumentPart.GetIdOfPart(headerPart);
            string strFooterPartID = docx.MainDocumentPart.GetIdOfPart(footerPart);

            using (MemoryStream stream = new MemoryStream())
            {
                stream.Write(byteHeaderArray, 0, byteHeaderArray.Length);
                using (WordprocessingDocument wdDocSource =
        WordprocessingDocument.Open(stream, true))
                {
                    DocumentFormat.OpenXml.Packaging.HeaderPart firstHeader = wdDocSource.MainDocumentPart.HeaderParts.FirstOrDefault();

                    if (firstHeader != null)
                    {
                        headerPart.FeedData(firstHeader.GetStream());
                    }
                }

            }

            using (MemoryStream stream = new MemoryStream())
            {
                stream.Write(byteFooterArray, 0, byteFooterArray.Length);
                using (WordprocessingDocument wdDocSource = WordprocessingDocument.Open(stream, true))
                {
                    DocumentFormat.OpenXml.Packaging.FooterPart firstFooter = wdDocSource.MainDocumentPart.FooterParts.FirstOrDefault();

                    if (firstFooter != null)
                    {
                        footerPart.FeedData(firstFooter.GetStream());
                    }
                }

            }

            IEnumerable<DocumentFormat.OpenXml.Wordprocessing.SectionProperties> sections = docx.MainDocumentPart.Document.Body.Elements<DocumentFormat.OpenXml.Wordprocessing.SectionProperties>();

            foreach (var section in sections)
            {
                section.RemoveAllChildren<DocumentFormat.OpenXml.Wordprocessing.HeaderFooterReferenceType>();

                section.PrependChild<DocumentFormat.OpenXml.Wordprocessing.HeaderReference>(new DocumentFormat.OpenXml.Wordprocessing.HeaderReference() { Type = DocumentFormat.OpenXml.Wordprocessing.HeaderFooterValues.Default, Id = strHeaderPartID });
                section.PrependChild<DocumentFormat.OpenXml.Wordprocessing.FooterReference>(new DocumentFormat.OpenXml.Wordprocessing.FooterReference() { Type = DocumentFormat.OpenXml.Wordprocessing.HeaderFooterValues.Default, Id = strFooterPartID });
            }
        }

If you use merge fields in the document header or footer you will get trouble in replacing the header or footer. In that case you have to replace the below

DocumentFormat.OpenXml.Packaging.FooterPart firstFooter = wdDocSource.MainDocumentPart.FooterParts.FirstOrDefault();

With

DocumentFormat.OpenXml.Packaging.FooterPart firstFooter = wdDocSource.MainDocumentPart.FooterParts.ToList()[1];