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");
}
