How to get Excel file to the dataset

Hi Rajkumar,


Try this...


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Excel_file_to_the_dataset1._Default" %>





   


   

   

   
           
               
                                        Text="Upload Excel Spreadsheet" OnClick="ButtonUpload_Click" />
               
               
                                        Text="View Excel Data"  OnClick="ButtonView_Click" />
               
               
           
       
       
           
           

            Please select an Excel file to import:

                            Text="Upload File" OnClick="ButtonUploadFile_Click" />

           
       
       
           
                            BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
               
               
               
               
               
               
           
       
       
           
       
       
   

   

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace Excel_file_to_the_dataset1
{
    public partial class _Default : System.Web.UI.Page
    {

        protected void ButtonUploadFile_Click(object sender, EventArgs e)
        {
            if (FileUploadExcel.HasFile)
            {

                try
                {

                    FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"));
                    LabelUpload.Text = "Upload File Name: " +
                        FileUploadExcel.PostedFile.FileName + "
" +
                        "Type: " + FileUploadExcel.PostedFile.ContentType +
                        " File Size: " + FileUploadExcel.PostedFile.ContentLength +
                        " kb
";
                }
                catch (System.NullReferenceException ex)
                {
                    LabelUpload.Text = "Error: " + ex.Message;
                }
            }
            else
            {
                LabelUpload.Text = "Please select a file to upload.";
            }
        }
        protected OleDbCommand ExcelConnection()
        {

            string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";

            OleDbConnection objXConn = new OleDbConnection(xConnStr);
            objXConn.Open();
            OleDbCommand objCommand = new OleDbCommand("SELECT * FROM [sheet1$]", objXConn);
            return objCommand;

        }
        protected void ButtonUpload_Click(object sender, System.EventArgs e)
        {
            PanelUpload.Visible = true;
            PanelView.Visible = false;
            PanelImport.Visible = false;
        }
        protected void ButtonView_Click(object sender, System.EventArgs e)
        {
            PanelUpload.Visible = false;
            PanelView.Visible = true;
            PanelImport.Visible = false;


            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();


            objDataAdapter.SelectCommand = ExcelConnection();


            DataSet objDataSet = new DataSet();

            objDataAdapter.Fill(objDataSet);


            GridViewExcel.DataSource = objDataSet.Tables[0].DefaultView;
            GridViewExcel.DataBind();
        }

    }

}


Thanks
If this post helps you mark it as answer

Hi Rajkumar,


Try this...

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>





   
   



   

Insert     Data	View Data

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;

public partial class _Default : System.Web.UI.Page
{
    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
   
    protected void insertdata_Click(object sender, EventArgs e)
    {
       OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("test.xls") + ";Extended Properties=Excel 8.0");
       OleDbCommand com = new OleDbCommand("select * from [Sheet1$]", conn);
       conn.Open();
       OleDbDataReader reader = com.ExecuteReader();
       string Name = "";
       string Address = "";
       string salary = "";
     while (reader.Read())
     {
         Name = valid(reader, 0);
         Address = valid(reader, 1);
         salary = valid(reader, 2);
         insertdataintosql(Name, Address, salary);
     }
         conn.Close();
         l1.Text = "Records Inserted successfully";
      
    }
    protected string valid(OleDbDataReader reader, int str1)
  
    {
        object val = reader[str1];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }


    public void insertdataintosql(string Name, string Address, string salary)
    {
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand com = new SqlCommand();
        com.Connection = conn;
        com.CommandText = "insert into employee(Name,Address,salary)values(@Name,@Address,@salary)";
        com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Name;
        com.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Address;
        com.Parameters.Add("@salary", SqlDbType.Int).Value = Convert.ToInt32(salary);
        com.CommandType = CommandType.Text;
        conn.Open();
        com.ExecuteNonQuery();
        conn.Close();
    }

  protected void  viewdata_Click(object sender, EventArgs e)
  {
       l1.Text ="";
      SqlConnection conn = new SqlConnection(connStr);
      SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * from employee", conn);
      DataSet ds = new DataSet();
      sqlda.Fill(ds,"employee");
      g1.DataSource = ds;
      g1.DataMember = "employee";
      g1.DataBind();
      
}
 
}




Thanks

Hi Rajkumar,

Refer the below link it will solve your problem

http://www.codeproject.com/KB/office/ExcelToDataset.aspx

Thanks,
Parsant