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

No comments:

Post a Comment

Gadget

This content is not yet available over encrypted connections.