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();
    }