import excel data into sql

 Import Excel using c#.net  
 --------------------------  
 step 1):  
 ---------  
 <connectionStrings>  
  <add name ="Excel03ConString"  
     connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};  
              Extended Properties='Excel 8.0;HDR={1}'"/>  
  <add name ="Excel07ConString"  
     connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};  
              Extended Properties='Excel 8.0;HDR={1}'"/>  
 </connectionStrings>  
 step 2):  
 ---------  
 <asp:FileUpload ID="FileUpload1" runat="server" />  
 <asp:Button ID="btnUpload" runat="server" Text="Upload"  
       OnClick="btnUpload_Click" />  
 <br />  
 <asp:Label ID="Label1" runat="server" Text="Has Header ?" />  
 <asp:RadioButtonList ID="rbHDR" runat="server">  
   <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >  
   </asp:ListItem>  
   <asp:ListItem Text = "No" Value = "No"></asp:ListItem>  
 </asp:RadioButtonList>  
 <asp:GridView ID="GridView1" runat="server"  
 OnPageIndexChanging = "PageIndexChanging" AllowPaging = "true">  
 </asp:GridView>  
 step 3):  
 -----------  
 inapp setting put it this  
 <appSettings>  
  <add key ="FolderPath" value ="Files/"/>  
 </appSettings >  
 step 4):  
 --------  
 protected void btnUpload_Click(object sender, EventArgs e)  
 {  
   if (FileUpload1.HasFile)  
   {  
     string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);  
     string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);  
     string FolderPath = ConfigurationManager.AppSettings["FolderPath"];  
     string FilePath = Server.MapPath(FolderPath + FileName);  
     FileUpload1.SaveAs(FilePath);  
     Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);  
   }  
 }  
 step 5):  
 --------  
 private void Import_To_Grid(string FilePath, string Extension, string isHDR)  
 {  
   string conStr="";  
   switch (Extension)  
   {  
     case ".xls": //Excel 97-03  
       conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]  
            .ConnectionString;  
       break;  
     case ".xlsx": //Excel 07  
       conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]  
            .ConnectionString;  
       break;  
   }  
   conStr = String.Format(conStr, FilePath, isHDR);  
   OleDbConnection connExcel = new OleDbConnection(conStr);  
   OleDbCommand cmdExcel = new OleDbCommand();  
   OleDbDataAdapter oda = new OleDbDataAdapter();  
   DataTable dt = new DataTable();  
   cmdExcel.Connection = connExcel;  
   //Get the name of First Sheet  
   connExcel.Open();  
   DataTable dtExcelSchema;  
   dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
   string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();  
   connExcel.Close();  
   //Read Data from First Sheet  
   connExcel.Open();  
   cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";  
   oda.SelectCommand = cmdExcel;  
   oda.Fill(dt);  
   connExcel.Close();  
   //Bind Data to GridView  
   GridView1.Caption = Path.GetFileName(FilePath);  
   GridView1.DataSource = dt;  
   GridView1.DataBind();  
 }  
 last step  
 -------------:)  
 protected void PageIndexChanging(object sender, GridViewPageEventArgs e)  
 {  
   string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;  
   string FileName = GridView1.Caption;  
   string Extension = Path.GetExtension(FileName);  
   string FilePath = Server.MapPath(FolderPath + FileName);  
   Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);   
   GridView1.PageIndex = e.NewPageIndex;  
   GridView1.DataBind();   
 }  
 happy coding   
 for more coding pages visit http://scriptquery.blogspot.in/   

3:46 AM Share: