How to import data from Excel to SQL Server

 if you want more related search please click here  
 please help to other  
 http://scriptquery.blogspot.in/  

 How to bind a data to table with Import Excell by using jquery  
 ---------------------------------------------------------------  
 simple  
 step1): //write input buttons in html.  
 -------  
 <input type="file" onchange="UploadFile();" />  
 <input type="button" text="ImportExcel" />  
 step2); //this is the function for uploadfile()  
 ------  
 function UploadFile() {  
   var formData = new FormData();  
   var file = document.getElementById("importfiles").files[0]; 
   formData.append("file", file);  
   formData.append("filename", file.name);  
   formData.append("filesize", file.size);  
   formData.append("fiketype", file.type);  
   var uploadServerSideScriptPath = "OpeningBalance.aspx";  
   var xhr = new XMLHttpRequest();  
   xhr.open("POST", uploadServerSideScriptPath);  
   xhr.send(formData);  
 }  
 step3); // write code in page load  
 -------  
     protected void Page_Load(object sender, EventArgs e)  
     {  
       HttpPostedFile file = null;  
       if (Request.Files.Count > 0)  
       {  
         file = Request.Files[0];  
         file.SaveAs(Server.MapPath("~\\ImportExcel/" + file.FileName));  
       }  
      }  
 step 4):  
 --------  
 $(document).ready(function () {  
   btncontrolevent();  
 });  
 function btncontrolevent(){  
   $('#BtnImportExcel').click(function () {  
       var request = {};  
       request.FileType = $('#importfiles').val();  
       $.ajax({  
         type: "post",  
         url: impportexcel.aspx/importexcel,  
         data: '{request: ' + JSON.stringify(request) + '}',  
         contentType: "application/json; charset=utf-8",  
         dataType: "json",  
         success: OnSuccess,  
         error: onErrorCall  
       });  
   });  
 }  
 #region Import Excel  
     [System.Web.Services.WebMethod]  
     public static GetOpeningBalanceRecordsResponse importexcel()  
     {  
       var response = new GetOpeningBalanceRecordsResponse();  
       string strExcelConn = "";  
       string Extension = request.FileType;  
       int i = Extension.LastIndexOf('.');  
       string rhs = i < 0 ? "" : Extension.Substring(i + 1);//giveng file Extension (lhs = i < 0 ? s : s.Substring(0, i)  
       //Connection String to Excel Workbook  
       if (rhs == "xls")  
       {  
         strExcelConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + HttpContext.Current.Server.MapPath("~/ImportExcel/" + request.FileType) + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";  
       }  
       else if (rhs == "xlsx")  
       {  
         strExcelConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + HttpContext.Current.Server.MapPath("~/ImportExcel/" + request.FileType) + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";  
       }  
       System.Data.OleDb.OleDbConnection connExcel = new System.Data.OleDb.OleDbConnection(strExcelConn);  
       System.Data.OleDb.OleDbCommand cmdExcel = new System.Data.OleDb.OleDbCommand();  
       cmdExcel.Connection = connExcel;  
       connExcel.Open();  
       System.Data.DataTable dtExcelSchema;  
       dtExcelSchema = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);  
       connExcel.Close();  
       connExcel.Open();  
       System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();  
       DataSet ds = new DataSet();  
       string sheetName = dtExcelSchema.Rows[0]["Table_Name"].ToString();  
       cmdExcel.CommandText = "SELECT * From [Sheet1$]";  
       da.SelectCommand = cmdExcel;  
       da.Fill(ds);  
       // DataTable dt = new DataTable();  
 var dt =new DataTable();        
 dt = ds.Tables[0];  
 iF(dt.Rows.count>0){  
 foreach(rows in dt.Rows ){  
       var BOMRecordsList = new OpeningBalRecordsDetails();  
       var BOMRecordsitems = new OpeningBalRecords();  
                 BOMRecordsitems = new OpeningBalRecords();  
                 BOMRecordsitems.ItemID = response.ResponseCode;  
                 BOMRecordsitems.ItemCode = rows.ItemArray[1].ToString();  
                 BOMRecordsitems.ItemName = rows.ItemArray[2].ToString();  
                 BOMRecordsitems.UOMCode = rows.ItemArray[3].ToString();  
                 BOMRecordsitems.RecepientDate = rows.ItemArray[4].ToString();  
                 BOMRecordsitems.RecepientReference = rows.ItemArray[5].ToString();  
                 BOMRecordsitems.OpeningQty = (rows.ItemArray[6].ToString() != CommonConstants.emptyString) ? Convert.ToInt32(rows.ItemArray[6].ToString()) : 0;  
                 BOMRecordsitems.Rate = (rows.ItemArray[7].ToString() != CommonConstants.emptyString) ? Convert.ToDecimal(rows.ItemArray[6].ToString()) : 0;  
                 BOMRecordsitems.OpValue = (rows.ItemArray[8].ToString() != CommonConstants.emptyString) ? Convert.ToDecimal(rows.ItemArray[7].ToString()) : 0;  
                 BOMRecordsitems.Remarks = rows.ItemArray[9].ToString();  
                 BOMRecordsList.Add(BOMRecordsitems);  
      }  
 }  
       connExcel.Close();  
       return response;  
     }  
     #endregion Import Excel  
 step 5:)  
 -----------------  
   public class OpeningBalRecordsDetails : List<OpeningBalRecords> { }  
   public class OpeningBalRecords  
   {  
     #region Properties  
     /// <summary>  
     /// Opening Balance ID  
     /// </summary>  
     /// <param name="OpBalID"></param>   
     /// <returns></returns>  
     public long OpBalID { get; set; }  
     /// <summary>  
     /// Item ID  
     /// </summary>  
     /// <param name="ItemID"></param>   
     /// <returns></returns>  
     public long ItemID { get; set; }  
     /// <summary>  
     /// Location ID  
     /// </summary>  
     /// <param name="LocationID"></param>   
     /// <returns></returns>  
     public long LocationID { get; set; }  
     /// <summary>  
     /// Recepient Date  
     /// </summary>  
     /// <param name="RecepientDate"></param>   
     /// <returns></returns>  
     public string RecepientDate { get; set; }  
     /// <summary>  
     /// Recepient Reference  
     /// </summary>  
     /// <param name="RecepientReference"></param>   
     /// <returns></returns>  
     public string RecepientReference { get; set; }  
     /// <summary>  
     /// Opening Qty  
     /// </summary>  
     /// <param name="OpeningQty"></param>   
     /// <returns></returns>  
     public int OpeningQty { get; set; }  
     /// <summary>  
     /// Rate  
     /// </summary>  
     /// <param name="Rate"></param>   
     /// <returns></returns>  
     public decimal Rate { get; set; }  
     /// <summary>  
     /// Opening Value  
     /// </summary>  
     /// <param name="OpValue"></param>   
     /// <returns></returns>  
     public decimal OpValue { get; set; }  
     /// <summary>  
     /// Remarks  
     /// </summary>  
     /// <param name="Remarks"></param>   
     /// <returns></returns>  
     public string Remarks { get; set; }  
     /// <summary>  
     /// ItemCode  
     /// </summary>  
     /// <param name="ItemCode"></param>   
     /// <returns></returns>  
     public string ItemCode { get; set; }  
     /// <summary>  
     /// ItemName  
     /// </summary>  
     /// <param name="ItemName"></param>   
     /// <returns></returns>  
     public string ItemName { get; set; }  
     /// <summary>  
     /// UOMCode  
     /// </summary>  
     /// <param name="UOMCode"></param>   
     /// <returns></returns>  
     public string UOMCode { get; set; }  
     /// <summary>  
     /// SlNO  
     /// </summary>  
     /// <param name="SlNO"></param>   
     /// <returns></returns>  
     public long SlNO { get; set; }  
     #endregion Properties  
   }  

 if you want more related search please click here  
 please help to other  
 http://scriptquery.blogspot.in/  

 How to Import Excell into datatable binding using jquery  
 How to Import Excell into datatable binding using jquery  
 How to Import Excell into datatable binding using jquery  
 How to Import Excell into datatable binding using jquery  

11:18 PM Share: