public ActionResult FileUpload(HttpPostedFileBase file) { var model = new List<DisplayFromFile>(); // Verify that the user selected a file //if (file != null && file.ContentLength > 0) //{ // // extract only the filename // var fileName = Path.GetFileName(file.FileName); // // store the file inside ~/App_Data/uploads folder // var path = Path.Combine(Server.MapPath("~/App_Data"), fileName); // file.SaveAs(path); //} //// redirect back to the index action to show the form once again //return RedirectToAction("Index"); //FileUpload fu = file; //if (fu.HasFile) //{ // StreamReader reader = new StreamReader(fu.FileContent); // do // { // string textLine = reader.ReadLine(); // // do your coding // //Loop trough txt file and add lines to ListBox1 // } while (reader.Peek() != -1); // reader.Close(); //} //return View(); string result = new StreamReader(file.InputStream).ReadToEnd(); string[] multiline = result.Split(new string[] { Environment.NewLine }, StringSplitOptions.None); foreach(var item in multiline) { string[] split1 = item.Split(';'); var modelitem = new DisplayFromFile(); if (split1.Length == 3) { var mat_opsplit = split1[0].Split('_'); if (mat_opsplit[3].Any(c => char.IsDigit(c))) { modelitem.MaterialNumber = mat_opsplit[3]; modelitem.OptionName = ""; for (int i = 4; i < mat_opsplit.Length; i++) modelitem.OptionName = modelitem.OptionName + mat_opsplit[i]; } else { modelitem.MaterialNumber = ""; modelitem.OptionName = ""; for (int i = 3; i < mat_opsplit.Length; i++) modelitem.OptionName = modelitem.OptionName + mat_opsplit[i]; } modelitem.OptionValue = split1[1]; modelitem.Description = split1[2]; } //else //{ // modelitem.MaterialNumber = 0; // modelitem.OptionValue=split1[] //} model.Add(modelitem); } //foreach (var item in db.FromSAP.ToList()) // db.FromSAP.Remove(item); db.Database.ExecuteSqlCommand("TRUNCATE TABLE [FromSAP]"); foreach (var item1 in model) { var fromsap = new FromSAP { MaterialNumber = item1.MaterialNumber, OptionName = item1.OptionName, OptionValue = item1.OptionValue, Description = item1.Description }; db.FromSAP.Add(fromsap); } db.SaveChanges(); return View("DisplayFromFile",model); }
public ActionResult Upload(HttpPostedFileBase upload) { if (ModelState.IsValid) { if (upload != null && upload.ContentLength > 0) { // ExcelDataReader works with the binary Excel file, so it needs a FileStream // to get started. This is how we avoid dependencies on ACE or Interop: Stream stream = upload.InputStream; // We return the interface, so that IExcelDataReader reader = null; if (upload.FileName.EndsWith(".xls")) { reader = ExcelReaderFactory.CreateBinaryReader(stream); // upload.SaveAs(Server.MapPath("/App_Data/Excel Files/" + upload.FileName)); } else if (upload.FileName.EndsWith(".xlsx")) { reader = ExcelReaderFactory.CreateOpenXmlReader(stream); //string filepath = Server.MapPath("/App_Data/Excel Files/" + upload.FileName); //upload.SaveAs(filepath); //ImportExcel(filepath); } else { ModelState.AddModelError("File", "This file format is not supported"); return View(); } DataSet result = reader.AsDataSet(); reader.Close(); DataTable dt = result.Tables[0]; bool flag = true; //Delete existing contents of SAP file from database if(db.FromSAP.ToList().Count()!=0) db.Database.ExecuteSqlCommand("TRUNCATE TABLE [FromSAP]"); //Saving values of SAP file to database foreach(DataRow dr in dt.Rows) { string filter1 = "R5G_MX_SYS_1"; string filter2 = "R5G_MX_SYS_2"; string filter3 = "R5G_MX_SYS_3"; string filter4 = "R5G_MX_SYS_4"; string filter5 = "R5G_MX_SYS_5"; string filter6 = "R5G_MX_SYS_6"; string filter7 = "R5G_MX_SYS_7"; string filter8 = "R5G_MX_SYS_8"; string filter9 = "R5G_MX_SYS_9"; string filter0 = "R5G_MX_SYS_0"; string matno = dr["Column1"].ToString(); string optionname = dr["Column3"].ToString(); string filter = dr["Column4"].ToString(); string optionval = dr["Column5"].ToString(); string description = dr["Column6"].ToString(); if(String.IsNullOrEmpty(matno)||String.IsNullOrEmpty(optionname)||String.IsNullOrEmpty(filter)||String.IsNullOrEmpty(optionval)||string.IsNullOrEmpty(description)||optionval=="999") { flag = false; } else if (filter.StartsWith(filter1) || filter.StartsWith(filter2) || filter.StartsWith(filter3) || filter.StartsWith(filter4) || filter.StartsWith(filter5) || filter.StartsWith(filter6) || filter.StartsWith(filter7) || filter.StartsWith(filter8) || filter.StartsWith(filter9) || filter.StartsWith(filter0)) { var fromsap = new FromSAP{ MaterialNumber= matno, Description= description, OptionName = optionname, OptionValue = optionval }; db.FromSAP.Add(fromsap); } } db.SaveChanges(); return View("ImportSAP"); } else { ModelState.AddModelError("File", "Please Upload Your file"); } } return View("Upload"); }