Blogs >> Education & Books >>
Export data from excel to database using c#
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
/* This code requires there should be same name of column in sql table as well as in excel file and name of sheet of excel file should be abc */
}
protected void Button1_Click(object sender, EventArgs e)
{
ExportExcelTOSql();
}
public void ExportExcelTOSql()
{
OdbcConnection connection;
SqlBulkCopy bulkCopy;
string strExcelPath = Server.MapPath("abc.xls");
string ConnectionString = @"server=DEL-SKUMAR\SPLENDIDCRM;database=TestDatabase;Integrated Security=SSPI;";
string conn = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + strExcelPath;
using (connection = new OdbcConnection(conn))
{
//To place in sql
OdbcCommand command = new OdbcCommand("Select * FROM [abc$]", connection);
connection.Open();
using (OdbcDataReader dr = command.ExecuteReader())
{
using (bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = "EmployeeNotSentReport";
bulkCopy.WriteToServer(dr);
}
dr.Close();
}
//To place in dataset and manipulate at the time of insertion
//here abc is name of sheet in excel or you can place sheet1 the default name of sheet if not changed in excel file.
OdbcDataAdapter placeindataset = new OdbcDataAdapter("Select * FROM [abc$]", connection);
DataSet ds = new DataSet();
placeindataset.Fill(ds);
}
bulkCopy.Close();
connection.Close();
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
/* This code requires there should be same name of column in sql table as well as in excel file and name of sheet of excel file should be abc */
}
protected void Button1_Click(object sender, EventArgs e)
{
ExportExcelTOSql();
}
public void ExportExcelTOSql()
{
OdbcConnection connection;
SqlBulkCopy bulkCopy;
string strExcelPath = Server.MapPath("abc.xls");
string ConnectionString = @"server=DEL-SKUMAR\SPLENDIDCRM;database=TestDatabase;Integrated Security=SSPI;";
string conn = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + strExcelPath;
using (connection = new OdbcConnection(conn))
{
//To place in sql
OdbcCommand command = new OdbcCommand("Select * FROM [abc$]", connection);
connection.Open();
using (OdbcDataReader dr = command.ExecuteReader())
{
using (bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = "EmployeeNotSentReport";
bulkCopy.WriteToServer(dr);
}
dr.Close();
}
//To place in dataset and manipulate at the time of insertion
//here abc is name of sheet in excel or you can place sheet1 the default name of sheet if not changed in excel file.
OdbcDataAdapter placeindataset = new OdbcDataAdapter("Select * FROM [abc$]", connection);
DataSet ds = new DataSet();
placeindataset.Fill(ds);
}
bulkCopy.Close();
connection.Close();
}
}
|
|
