JAYRAM

JAYRAM

  • NA
  • 272
  • 214.4k

upload excel file in sqltable

Jan 5 2015 6:26 AM
i attached excel file above ...plz see

error Reading Merged Cell From Excel Using Microsoft.Office



i want to upload excel file into sql using csharp code


save excel values in sql table like below format

SnoCompanyNameCompanyidsalesqtysalesvaluesdate
1Dell23414500000jan'15
2Dell2341512000Feb'15

3sony
4sony
5
6
7
8


plz write logic of  csharp code ....
i want save o/p like above format


i writen csharp code its working  ...but i have problem with select  sales qty ,salesvalue with datefield  in excel  ....

plz write logic to copy data in datatable and insert in sql  

my csharp code
string FileName = FlUploadcsv.FileName;
        string path = string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));


        FlUploadcsv.PostedFile.SaveAs(path);


        OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");


       // ExcelConn(OleDbcon);


        Query = string.Format("Select [SNo],[CompanyName],[companyid],[salesqty] ,[salesvalues]FROM [{0}]", "Sheet1$");


OleDbCommand Ecom = new OleDbCommand(Query, OleDbcon);
        // OleDbCommand Ecom2 = new OleDbCommand(Query2, Econ);
        OleDbcon.Open();


        DataSet ds = new DataSet();
        OleDbDataAdapter oda = new OleDbDataAdapter(Query, OleDbcon);
        OleDbcon.Close();
        oda.Fill(ds);
        DataTable Exceldt = ds.Tables[0];
        //connection();
        //creating object of SqlBulkCopy  
        SqlBulkCopy objbulk = new SqlBulkCopy(conn);
        //assigning Destination table name  
        objbulk.DestinationTableName = "compinfo";
        //Mapping Table column  
        objbulk.ColumnMappings.Add("sNo", "sNo");
        objbulk.ColumnMappings.Add("CompanyName", "CompanyName");
     
        objbulk1.ColumnMappings.Add("companyidi", "companyid");
        objbulk1.ColumnMappings.Add("SupplierId", "SupplierId");
        //objbulk.ColumnMappings.Add("salesqty", "salesqty");
        //objbulk.ColumnMappings.Add("salesvalue", "salesvalue");
        //inserting Datatable Records to DataBase  
        conn.Open();
        objbulk.WriteToServer(Exceldt);
       


        conn.Close();



Attachment: compinfo.rar

Answers (4)