Tuesday, 6 January 2015

How to import data from excel in Asp.net



Call this function:-

private void ImportExcel(Label lbl)
    {
        string connectionString = "";
        string strFileType = Path.GetExtension(fu_excel_N.FileName).ToLower();
        string path = fu_excel_N.PostedFile.FileName;
        if (strFileType.Trim() == ".xlsx")
        {
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileToConvert + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }


        try
        {
            OleDbConnection connection = new OleDbConnection(connectionString);
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection);
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            adapter.Fill(ds);
            dt = ds.Tables[0];
            System.Web.HttpContext.Current.Session["ImportExcel"] = dt;
            connection.Close();

            int number_of_columns = dt.Columns.Count;
            string[] columnNames = new string[number_of_columns];
            for (int j = 0; j < number_of_columns; j++)
            {
                columnNames[j] = dt.Columns[j].ToString();

            }
           
        }

        catch (Exception ex)
        {
            lbl.Text = "Error loading file !!!, Make sure that the file is in '.xlsx' format";
            lbl.Visible = true;
            lbl.ForeColor = System.Drawing.Color.Red;
            return;
        }

    }

No comments: