Export Data From Excel File in ASP.Net

Export Data From Excel File in ASP.Net

The following function takes the following parameter :

    "excelFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// This function convert uploaded Ecxcel sheet into DataTable and then returns 
    /// </summary>
    /// <param name="fileToConvert"></param>
    /// <param name="fileForDs"></param>
    /// <param name="uniqueFileName"></param>
    /// <returns></returns>
    public System.Data.DataTable ReadFromExcelFile(string excelFileName)
    {
        OleDbConnection connection = null;

        //this datatable contains Active Sheet  records 
        System.Data.DataTable dtImprtExcel = new System.Data.DataTable();

        try
        {

            // Get Active Sheet Name
            string activeSheetName = getActiveSheetName(excelFileName);

            //If Active Sheet Name is not Null or Blank
            if (!string.IsNullOrEmpty(activeSheetName))
            {

                // <add key="ExcelConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Path;Extended Properties=Excel 8.0;"/>
                string connectionString = ConfigurationManager.AppSettings.Get("ExcelConnectionString").Replace("Path", excelFileName);
                OleDbConnection excelCon = new OleDbConnection(connectionString);
                excelCon.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + activeSheetName + "$]", excelCon);
                adapter.Fill(dtImprtExcel);
                excelCon.Close();

                //cleans up the temporary file that was stored
                File.Delete(excelFileName);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (connection != null)
            {
                if (connection.State == ConnectionState.Open)
                {
                    //close a open connection
                    connection.Close();
                }
            }
        }
        return dtImprtExcel;
    }

Get Active Sheet Name :

The following function takes the following parameter :
"strFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// Get Excel Sheet Name
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    private string getActiveSheetName(string strFileName)
    {
        string activeSheetName = string.Empty;
        try
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;

            // Start Excel and get Application object. 
            oXL = new Application();

            // Set some properties 
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            // Open the workbook. 
            oWB = oXL.Workbooks.Open(strFileName);

            // Get the active sheet 
            oSheet = (Worksheet)oWB.ActiveSheet;
            activeSheetName = oSheet.Name;

            // Save the sheet and close 
            oSheet = null;
            oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return activeSheetName;
    }

Get ActiveSheet Name of Uploaded Execl in ASP.Net

 The following function takes the following parameter :

    "strFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// Get Excel Sheet Name
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    private string getActiveSheetName(string strFileName)
    {
        string activeSheetName = string.Empty;
        try
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;

            // Start Excel and get Application object. 
            oXL = new Application();

            // Set some properties 
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            // Open the workbook. 
            oWB = oXL.Workbooks.Open(strFileName);

            // Get the active sheet 
            oSheet = (Worksheet)oWB.ActiveSheet;
            activeSheetName = oSheet.Name;

            // Save the sheet and close 
            oSheet = null;
            oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return activeSheetName;
    }

Download Excel in ASP.Net

The Following Function downloads the Excel .

It accepts the following paramete :
fileName : It contains the path with FileName . I.e : "D:\MyExcel.exe".

Code :

    /// <summary>
    /// Download Excel template with some existing data
    /// </summary>
    /// <param name="fileName"></param>
    private void DownloadExcel(string fileName)
    {
        try
        {
            FileInfo file = new FileInfo(fileName);
            if (file.Exists)
            {
                // Clear the content of the response
                Response.ClearContent();
                Response.Clear();

                // add the header that specifies the default filename for the Download/SaveAs dialog
                Response.AddHeader("Content-Disposition", "attachment; filename=LMSUsers.xls");

                // add the header that specifies the file size, so that the browser can show the download progress
                Response.AddHeader("Content-Length", file.Length.ToString());

                // specify that the response is a stream that cannot be read by the client and must be downloaded
                Response.ContentType = "application/vnd.ms-excel";

                // send the file stream to the client
                Response.WriteFile(file.FullName);
                Response.BufferOutput = true;
                Response.Flush();
                Response.Close();

                //Delete the file if Required
                file.Delete();
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.InnerException.Message);
        }
    }

Create Excel with Data in ASP.Net

Create Excel in ASP.Net with Dynamic data .

The following function takes the following parameter :
    "DataTable"  : which holds the data from Database.
    "strFileName" : It is specify the Path with File Name .i.e: "D:\MyExcel.xls"

 

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// Creates the excel file from the provided dataset.
    /// </summary>
    /// <param name="dtSource">The dt source.</param>
    /// <param name="strFileName">Name of the STR file.</param>
    private void GenerateExcel(System.Data.DataTable dtSource, string strFileName)
    {
        Application oXL;
        Workbook oWB;
        Worksheet oSheet;
        Range oRange;

        // Start Excel and get Application object. 
        oXL = new Application();

        // Set some properties 
        oXL.Visible = false;
        oXL.DisplayAlerts = false;

        // Get a new workbook. 
        oWB = oXL.Workbooks.Add(Missing.Value);

        // Get the active sheet 
        oSheet = (Worksheet)oWB.ActiveSheet;
        oSheet.Name = "LMS Users";
        int rowCount = 1;

        // Add Headrer to Excel
        for (int i = 1; i < dtSource.Columns.Count + 1; i++)
        {
            oSheet.Cells[1, i] = dtSource.Columns[i - 1].ColumnName;
            oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, i];
            oRange.Font.Bold = true;
        }

        // Add the Data Rows to the Excel
        if (dtSource.Rows.Count > 0)
        {
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount += 1;
                for (int i = 1; i < dtSource.Columns.Count + 1; i++)
                {
                    if (rowCount > 1)
                    {
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }
            }
        }
        
        // Resize the columns 
        oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                      oSheet.Cells[rowCount, dtSource.Columns.Count]);
        oRange.EntireColumn.AutoFit();

        // Save the sheet and close 
        oSheet = null;
        oRange = null;
        oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            XlSaveAsAccessMode.xlExclusive,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();

        // Clean up 
        // NOTE: When in release mode, this does the trick 
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
    }

How to get File and Function Details from Exception object

Its better to use ErrorLog in Exception Handling. In ErrorLog we need to keep track of the File and the Function details where this Exception occurs. So, instead of sending the Hardcoded Values (i.e : Function Name , File Name ) to ErrorLog, we can directly get this details from Exception object (i.e : Exception ex) as follows :

Code :

Use namespace :

using System.Diagnostics;
using System.Reflection;

Function where Error Occurred :

        /// <summary>
        /// Set AssetName for the AssetID
        /// </summary>
        /// <param name="programId"></param>
        /// <param name="languageId"></param>
        /// <param name="assetId"></param>
        /// <param name="assetName"></param>
        public void SetAssetName(Int32 progId, Int32 langId, int astId, string astName)
        {
            AssetClient assetClient = null;
            try
            {
                assetClient = new AssetClient(appBindingAssets);
                //return assetClient.SetAssetName(progmId, langId, astId, astName);
            }
            catch (Exception ex)
            {
                //call ErrorLog using the File Details
                LogError(ex);
            }
            finally
            {
                if (assetClient != null)
                {
                    assetClient.Close();
                }
            }
        }

ErrorLog Function accepts Exception object as Parameter :

        /// <summary>
        /// Logs the exception. 
        /// </summary>
        /// <param name="ex"></param>
        public static void LogError(Exception ex)
        {
            // Get the File Details from Exception
            StackTrace st = new StackTrace(ex);
 
            // Get the object of MethodBase from StackTrace
            MethodBase mb = st.GetFrame(st.FrameCount - 1).GetMethod();
 
            // Get FileName with NameSpace 
            // Ex: IGroup.Modules.SalesZone.Controller.SalesZoneController"
            string source = mb.DeclaringType.FullName;
 
            // Get Function Name "GetPhraseTranslationList"
            string methodName = mb.Name;
 
            //Implement ErrorLog Logics here using the File Details
            //AddLogError(source, ex, methodName);
        }

SQL JOINS

Let check here a overall picture of  JOIN

Bind same event to Multiple controls in JQuery

When multiple controls needs to fire a same event.

Instead of binding the event to each control separately , we can create an array of controls and loop through it to bind the single event as following :

Code :

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Single Event for Multiple Controls</title>
     <script src="http://code.jquery.com/jquery-1.7.2.min.js" type="text/javascript"></script>
        <script type="text/javascript">
            $(function() {
                var btns = $('#btnA,#btnC,#btnE');
                $.each(btns, function() {
                    $(this).click(function() {
                        alert(this.id);
                    });
                });
            });
        </script>
</head

<body>
    <input id="btnA" type="button" value="Button A" />
    <input id="btnB" type="button" value="Button B" />
    <input id="btnC" type="button" value="Button C" />
    <input id="btnD" type="button" value="Button D" />
    <input id="btnE" type="button" value="Button E" />
</body>
</html>

Optimize IIS Performance using "Enable Gzip compression"

Enable Gzip compression :

Enable Gzip compression is one of the Feature to Optimize IIS and enhances the performance of sites and applications.

This feature allows user to use bandwidth more effectively and efficiently by using common compression mechanisms such as Gzip and Deflate.

It provides options to Configure HTTP Compression for both static and dynamic sites.

It is supported by Windows 7, Windows Server 2008, Windows Server 2008 R2, Windows Vista

How it works ?

Lets discussion how exactly the application works with out Compression and with Compression

Process with out Compression :

When you request a file like http://www.KnowledgeParlour.com/Default.html, your browser communicates to a web server as follows :

1. Browser sends request to get Default.html to Server.

2. Server checks if Default.html is available or not .

3. Server if Found Default.html then sends back the response code and the file.

4. Browser received the file which is around let 500KB, which is very slow . Then loads the file .

Process with Compression :

If the browser gets a .zip file (i.e. Default.html.zip) instead of normal Default.html , then it can save bandwidth and download time. Then The browser can download the zipped file, extract it, and then show it to user. Which is bit faster.

So using "Enable Gzip Compression" we can archieve the above process as follows :

1. Browser sends request to get Default.html to Server and informs to get the Compressed version of the response file.

2. Server checks if Default.html is available or not .

3. Server if Found Default.html then it Zip the file sends back the response code and the zipped File.

4. Browser received the Zipped file which is around 50KB , which is bit Faster and enhance the performance of application . Then it unzip the file and loads it .

Reason behind it :

When browser received the file from Server if it is smaller then it takes less time to download and process. so using Gzip Compression server makes the file let 500KB to 50KB which enhance the Performance and makes the application runs faster

How to configure compression ?

1. Open IIS Manager and navigate to the level you want to manage.

2. In Features View, double-click Compression.

3. Choose one or both of the following:

   a. Enable dynamic content compression to configure IIS to compress dynamic content.

   b. Enable static content compression to configure IIS to compress static content.

4. Click Apply in the Actions pane.

web.config Changes :

Once its applied , the following code has been updated in web.config file

<system.webServer> 
        <urlCompression doDynamicCompression="true" />
</system.webServer>

Take Backup with Data of single Table in SQL Server

Create Backup table Instantly :

The Following query automatically creates a Table with name "Table_Backup" and Insert all the rows of "Table_Old" into "Table_Backup"

Example :

    SELECT *  INTO  Table_Backup FROM Table_Old

External Style Sheets

Introduction :

External style sheets have many powerful that make them ubiquitous in professional Web sites:

• It keeps your website design and content separate.
• It's much easier to reuse your CSS code if you have it in a separate file. Instead of typing the same CSS code on every web page you have, simply have many pages refer to a single CSS file with the "link" tag.
• You can make drastic changes to your web pages with just a few changes in a single CSS file.
• It allows a single style sheet to control the rendering of multiple documents.
• This results in a time-savings for the author, a savings of space for the web server, and less download time for the user.
• This method can be used in both HTML and XML.
• In Application When using CSS it is preferable to keep the CSS separate from your HTML.
• Placing CSS in a separate file allows the web designer to completely differentiate between content (HTML) and design(CSS).

An External Style Sheet is a file containing only CSS syntax and should carry a MIME type of "text/css."

It is saved with a ".css" filename extensions that file is then referenced in your HTML using the "link" instead of "style".

By using the Link Tag to load a basic external style sheet (CSS), it's possible to control the look n feel of multiply WebPages by making changes to One style sheet.

This means that it is easy to change font, bgcolor, background, etc on ALL pages - just by changing one external style sheet (CSS).

Those CSS files define page attributes for every page to which they are linked.

The style information is not explicitly tied directly to the document's elements, so Selector syntax is used to specify what styles attach to which portions of the document tree.

The full range of CSS syntax is allowed in this method.

Example with codes :

The 'link' is always added to the Head Section i.e anywhere between the "head" and the "/head"

HTML Code:

    link rel="stylesheet" type="text/css" href="style.css"

Just create a text (ASCII) file named (test.css) that contains the code shown below.

Put the style.css file in the same folder / directory as the file .

Let us create an external CSS file. Open up notepad.exe, or any other plain text editor and type the following CSS code.

CSS Code:

    body
    {
        background-color: #FFFFF0;
        font-family: Arial, Verdana, sans-serif;
        font-size: 18px;
        color: #00008B;
    }

    a
    {
        font-family: Arial, Verdana, sans-serif;
        font-size: 18px;
        color: Blue;
        text-decoration: underline;
    }

    a:hover
    {
        font-family: Arial, Verdana, sans-serif;
        font-size: 18px;
        color: Red;
        background-color: Green;
    }

    h1 
    { 
        font-family: Arial, Verdana, sans-serif; 
        font-size: 32px; 
        color: blue; 
    }

    table
    {
        font-family: Arial, Verdana, sans-serif;
        font-size: 18px;
        color: #00008B;
        margin-top: 0px;
        margin-right: 0px;
        margin-bottom: 0px;
        margin-left: 0px;
        padding-top: 0px;
        padding-right: 0px;
        padding-bottom: 0px;
        padding-left: 0px;
    }

Now save the file as a CSS (test.css) file.

Now create a new HTML file and fill it with the following code.

HTML Code:

    link rel="stylesheet" type="text/css" href="test.css"

Then save this file as "Sample.html" (without the quotes) in the same directory as your CSS file.

Now open your HTML file in your web browser and it should look something like this..

Display:

A Blue Header

Image link in Blue Colour. then on Mouse hover it will be red colur with green background because we changed it with CSS!

Advantages :

External style sheets have many powerful that make them ubiquitous in professional Web sites:

• It keeps your website design and content separate.
• It's much easier to reuse your CSS code if you have it in a separate file. Instead of typing the same CSS code on every web page you have, simply have many pages refer to a single CSS file with the "link" tag.
• You can make drastic changes to your web pages with just a few changes in a single CSS file.
• It allows a single style sheet to control the rendering of multiple documents.This results in a time-savings for the author, a savings of space for the web server, and less download time for the user.
• This method can be used in both HTML and XML.