ASP.NET - How to import Excel data and store to the sql server database table

Asked By aman on 22-Nov-11 02:22 AM
hi all,
i have to know how we can import data from excel file with Header included and save to the sql server database table.

i have installed office 2007
.net framework 4.0
sql server 2008

please give nice example..

thanks 
Reena Jain replied to aman on 22-Nov-11 02:25 AM
try this and let me know its working for me perfectly

using System.Data;
using System.Data.OleDb;
   
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("select * from MyObject", con);
DataTable dt = new DataTable();
da.Fill(dt);
   
/* Read the DataTable Item and Insert it One by One into Database using Insert Query */
  
SqlConnection con = new SqlConnection("ConnectionStr");
con.Open();
foreach(DataRow r in dt.Rows)
{
  /* Inset Logic */
  SqlCommand comm = new SqlCommand("insert into table1(id,col1) values(@id,@col1)", con);
  comm.Parameters.AddWithValue("@id", r[0].ToString());
  comm.Parameters.AddWithValue("@col1", r.Cells[1].ToString());
  comm.ExecuteNonQuery();
  con.Close();
  }
}
dipa ahuja replied to aman on 22-Nov-11 02:38 AM
private void button2_Click(object sender, EventArgs e)
{
  string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
  ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
 
  string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
  SqlConstr += "Integrated Security=True;User Instance=True";
 
  using (OleDbConnection con = new OleDbConnection(ExcelConstr))
  {
    con.Open();
    OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
    OleDbDataReader dr = com.ExecuteReader();
    using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
    {
      sqlcon.Open();
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
      {
        bulkCopy.DestinationTableName = "Table1";
        bulkCopy.ColumnMappings.Add("srno", "srno");
        bulkCopy.ColumnMappings.Add("amount", "amount");
        bulkCopy.WriteToServer(dr);
      }
    }
    dr.Close();
    dr.Dispose();
  }
  MessageBox.Show("successfully imported!");
  //display the imported data in the datagrid
  SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
  DataTable dt = new DataTable();
 
  da.Fill(dt);
 
  dataGridViewX1.DataSource = dt;
}
 
kalpana aparnathi replied to aman on 22-Nov-11 03:33 AM
Try this Example:

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.SqlClient;

using System.Data.OleDb;

 

public partial class ExcelDataImport : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}


protected void btnUpload_Click(object sender, EventArgs e)

{



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

{

OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet

using (DbDataReader dr = command.ExecuteReader())

{

// SQL Server Connection String

string sqlConnectionString =DataAccess_Perf.GetConnectionString() ;

// Bulk Copy to SQL Server

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))

{

bulkCopy.DestinationTableName =
"LIB_SQUIRREL_user.ExcelTest";

bulkCopy.WriteToServer(dr);

}

}

}

}