Sunday, February 5, 2012

Export excel data to sql server Data base in

Now a days we have an mandatory requirements like export data to excel in and excel to database etc.Here i will show how to export the data from excel to Database in the below example i have taken one excel sheet"testexcel.xls" with data.when ever work with excel data base we have to add one namespace i;e, System.Data.OleDb;.
DataTable dt = new DataTable(); 
OleDbConnection oledbcn=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ Server.MapPath("~/testexcel.xls").ToString() + ";
Extended Properties=Excel 8.0;");  
//sheet name 
string SheetName="orders";oledbcn.Open(); 
OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM [" + SheetName + "$]", oledbcn);  
OleDbDataAdapter oledbad = new OleDbDataAdapter();  
oledbda.SelectCommand = cmd;
//if we have a large amount of data we have to use for lop to isert the data   
StringBuilder Str =new StringBuilder();
foreach (DataRow dr in dt.Rows)
 Str.Append("INSERT INTO Orders VALUES(" + dr["ID"].ToString() + ",'" + 

dr["Name"].ToString() + "')");

No comments: