Error In Importing MS Excel In Custom Module

Topics: Writing modules
Nov 24, 2014 at 10:11 AM
Edited Nov 24, 2014 at 10:13 AM
Hi all ,
I 'm facing problem when Importing data from Excel in custom module,
the problem is that when I run in local device it works fine , but after uploading the error page says : input string is not in correct format and so on ...
I convert the first column in each row to int64 , and that is where the code get error , I 'm sure the data in that field is convertable to int64 because it has numerical value .
below is my code :
        private List<CustomerPartRecord> UploadFile(HttpPostedFileBase usersFile) {
            Guid att = Guid.NewGuid();
            string attstr = att.ToString().Substring(0, 10);
            string filename = string.Empty;
            List<CustomerPartRecord> list=new List<CustomerPartRecord>();
            if (usersFile != null && usersFile.ContentLength != 0) {

                filename = usersFile.FileName;

                filename = System.IO.Path.GetFileName(usersFile.FileName);

                filename = attstr + filename;
                usersFile.SaveAs(Server.MapPath(@"~\Modules\RS.Customers\Contents\SampleCSV\") + filename);
                list= importdatafromexcel(Server.MapPath(@"~\Modules\RS.Customers\Contents\SampleCSV\" ) + filename , usersFile);
            }

            return list;
        }


        public List<CustomerPartRecord> importdatafromexcel(string excelfilepath, HttpPostedFileBase usersFile)
     {
  
   
         string myexceldataquery = "select * from [sheet1$]";
   
        //create our connection strings
         string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
       

         if (usersFile.ContentType == "application/vnd.ms-excel")
         {
            
                sexcelconnectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelfilepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

         }

   
        var oledbconn = new OleDbConnection(sexcelconnectionstring);
        var oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
        oledbconn.Open();
        OleDbDataReader dr = oledbcmd.ExecuteReader();
       
       
        int lastMax = _customer.GetMaxAccountingCode(2, 1);
        List<CustomerPartRecord> list=new List<CustomerPartRecord>();
        while (dr.Read()) {
            //if (Convert.ToInt64(dr[0].ToString()) > lastMax) {
                list.Add(new CustomerPartRecord
                {
                    AccountingCode = dr[0].ToString(),
                    AccountingName = dr[1].ToString(),
                    Identification = dr[2].ToString(),
                    Titel = dr[3].ToString(),
                    FirstName = dr[4].ToString(),
                    LastName = dr[5].ToString(),
                    Mobile = dr[6].ToString(),
                    HomePhone = dr[7].ToString(),

                    Phone1 = dr[8].ToString(),
                    Phone2 = dr[9].ToString(),
                    Phone3 = dr[10].ToString(),
                    Phone4 = dr[11].ToString(),
                    Advisor = dr[12].ToString(),
                    BirthDay = dr[13].ToString(),
                    Address = dr[14].ToString(),
                    CustomerType = dr[15].ToString(),
                    Job = dr[16].ToString(),

                    CreateDate = _clock.UtcNow.ToString(),
                    OwnerId = 1,
                    Insertion = 2,
                    Description = string.Empty,
                    Education = string.Empty
                });
            //}

        }
     
           oledbconn.Close();

           System.IO.File.Delete(excelfilepath);
            return list;
        }


        [HttpPost, ActionName("CustomerImport")]
        public ActionResult CustomerImportPost(HttpPostedFileBase usersFile) {
             const int ownerId = 1;

            int lastMax = _customer.GetMaxAccountingCode(2, 1);
            string fileType = usersFile.ContentType;

      
            if (fileType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || fileType == "application/vnd.ms-excel")
            {
                List<CustomerPartRecord> allResults = UploadFile(usersFile);
                allResults.ForEach(l => _customer.CreateCustomer(l));
              
                    
            }

            return RedirectToAction("Customers", ControllerContext.RouteData.Values);
        }
and now I have no idea about the error that happen !!!
anyone have any idea , Please help me !!!