How To Insert Data To DataTable by Reading The Excel Sheet

using System;
using System.Data;
using System.Collections.Specialized;
using SubsonicTool;
using System.Configuration;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Byte;
using System.Data.OleDb;
using System.Text;
using System.IO;


partial class ExcelImport : System.Web.UI.Page
{
#region "Private Members"


#endregion

protected void Button1_Click(object sender, System.EventArgs e)
{
try {

string filepath;
if ((FileUpload1.PostedFile.ContentLength == 0)) { //rejecting a zero length file
Label1.Text = "Cannot upload zero length file";
return;
}
FileUpload1.PostedFile.SaveAs("C:\\Book1.xls");

filepath = ("C:\\Book1.xls"); //Getting the excel file


**************************
//connection of the excel sheet goes here initalic
StringBuilder sbConn = new StringBuilder();

sbConn.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
sbConn.Append(filepath);

sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));

**************************
//connection of the data abse part goes here initalic

OleDbConnection cnExcel = new OleDbConnection(sbConn.ToString);
cnExcel.Open();


OleDbDataAdapter xlDataAda = new OleDbDataAdapter(" Select * From [Sheet1$]", cnExcel);

DataTable xlDataTbl = new DataTable();
xlDataAda.Fill(xlDataTbl);





cnExcel.Close();


SqlCommand sqlCommand = new SqlCommand();

string ConnString = "";
string sql = "";
ConnString = ConfigurationManager.ConnectionStrings("/*connection Name*/").ConnectionString;
SqlConnection SqlObj = new SqlConnection(ConnString);

SqlObj.Open();
SqlCommand cmd = SqlObj.CreateCommand;


cmd.CommandText = "SELECT * FROM /*tablename*/";
cmd.ExecuteNonQuery();

SqlDataAdapter sqldataAdp = new SqlDataAdapter(cmd);
SqlCommandBuilder sqlComBul = new SqlCommandBuilder(sqldataAdp);
DataSet sqlDataSet = new DataSet();
sqldataAdp.Fill(sqlDataSet, "/*tablename*/");


foreach (DataRow xlrow in xlDataTbl.Rows) {
DataRow dr = sqlDataSet.Tables("/*tablename*/").NewRow;

string username;
string password;
string intial;
string Donintial;

foreach (DataColumn col1 in xlDataTbl.Columns) {


if (col1.ColumnName.ToString == "/*column name 1*/") {
username = xlrow(col1.ColumnName);
}

else if (col1.ColumnName.ToString == "/*column name 2*/") {
password = Crypto.Lock(xlrow(col1.ColumnName));
}
else if (col1.ColumnName.ToString == "/*column name 3*/") {
intial = (xlrow(col1.ColumnName));
}
else if (col1.ColumnName.ToString == "/*column name 4*/") {
Donintial = (xlrow(col1.ColumnName));
}






}

//your sp or data insert part goes here


}

Label1.Text = "weldone";


sqldataAdp.Dispose();
SqlObj.Close();

File.Delete("C:\\Book1.xls");
}


catch (Exception ex) {
if (ex.Message.Contains("Column '/*column name 1*/' does not belong to table .")) {

Label1.Text = "You Have To Modify The Excel File /*column name 1*/Column As :-UserName And Password:-Password ";
}

else if (ex.Message.Contains("Cannot insert duplicate key in object ")) {
Label1.Text = "You Are try to enterted values to the db";
}
else {

Label1.Text = ex.Message.ToString;
}

}


}

}