TechiesWeb

Most of the application which handles with bulk amount of data needs an import facility.Recently i also came across the same situation where i had to read data from an excel file and insert ito to the backend – Database table.
It s quite simple.Lets look at the solution.
Lets start.. Initially I am uploading the excel file which user submitted to somewhere in the server

string tempExcelFileUploadPath=”";
try {
tempExcelFileUploadPath = “Excelstore/TempFiles/”;
tempExcelFileUploadPath = Server.MapPath(tempExcelFileUploadPath);
if (!Directory.Exists(tempExcelFileUploadPath))� // If directory not exist, create a new one
{
Directory.CreateDirectory(tempExcelFileUploadPath);
}
tempFilePath = tempExcelFileUploadPath + “\tempExcelFile.xls”;
excelUploader.PostedFile.SaveAs(tempFilePath);
Response.Write(“<br><font face=’Verdana’ > Excel file Uploaded …</font>”);
}
catch (Exception er)
{

Response.Write(“<br><font face=’Verdana’ > Error in excel file upload ! </font>”); Response.Write(“<br>” + er.ToString());
}

Now we have uploaded the excel file to server.The file is located in the folder Excelstore/TempFiles with the name as tempExcelFile.xls. Now we wanto read the data from this file .for this we are using an Oledb connection (Import the

System.Data.OleDb to our program)

Ex : using System.Data.OleDb; OleDbConnection con = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + tempFilePath + “;Extended Properties=Excel 8.0″);
// Create an object of OleDBConnection class
con.Open();// Open the connection
try { //Create Dataset and fill with information from the Excel Spreadsheet for easier reference
DataSet myDataSet = new DataSet();
SqlCommand objCmd = new SqlCommand();
OleDbDataAdapter myCommand = new OleDbDataAdapter(“SELECT * from [Sheet1$]“, con);

myCommand.Fill(myDataSet);
con.Close();
DataTable dTblManu;
dTblManu = new DataTable();
DataTable dt = myDataSet.Tables[0];
Response.Write(” Reading data from Excel file :……….”);
foreach (DataRow dr in dt.Rows)
{
if (dr[0].ToString() != “”)
{
name=d r[0].ToString();
age=d r[1].ToString(); // Now the variable ‘ name’ is holding the value of the first record’s name column

// Code to insert this data to DB (Build an inser query here)
// Ex : string sqlInsert=”insert into students (name,age) values

(‘”+name+”‘,”+age+”)”; //execute the query
}

}

catch (Exception ex1)

{

//Write excpetion handling code heree

}

Thats all you need. Try this. If you  face any difficulty in following this,
Please let me know it. Hapy yo HELP.