how to copy the data from excel to sqlserver

Hi Narasiman,

Try this...



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





   


   

   

   
           
               
                                        Text="Upload Excel Files" 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" CellSpacing="2">
               
               
               
               
               
           
       
       
           
       
       
   

            Text="Insert to Db" />
       
       
   






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 Show_Excel_gridview_insert_to_db
{
    public partial class _Default : System.Web.UI.Page
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlCommand com;

        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 ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection Con = new OleDbConnection(ConnStr);
            Con.Open();
            OleDbCommand com = new OleDbCommand("SELECT * FROM [sheet1$]", Con);
            return com;

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

            OleDbDataAdapter oledbda = new OleDbDataAdapter();
            oledbda.SelectCommand = ExcelConnection();
            DataSet ds = new DataSet();
            oledbda.Fill(ds);
            //GridViewExcel.DataSource = ds.Tables[0].DefaultView;
            GridViewExcel.DataSource = ds;
            GridViewExcel.DataBind();
        }
        protected void ButtonUpload_Click(object sender, System.EventArgs e)
        {
            PanelUpload.Visible = true;
            PanelView.Visible = false;
            PanelImport.Visible = false;
        }

        protected void btn_insert_Click(object sender, EventArgs e)
        {
            foreach (GridViewRow g1 in GridViewExcel.Rows)
            {
                SqlConnection con = new SqlConnection(connStr);
                com = new SqlCommand("insert into stock(ItemCode,SerialNo,Status,CreatedDate,CreatedBy) values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text.ToString() + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "','" + g1.Cells[4].Text + "')", con);
                con.Open();
                com.ExecuteNonQuery();
                con.Close();

            }
            Label1.Text = "Records inserted successfully";
        }
    }
}




Thanks
If this post helps you mark it as answer