Need Validation Row in Excel aps.net MVC

asp.net c# sqlbulkcopy sql-server

Question

Using Microsoft.Office.Interop.Excel Services, I need to verify each row in a mass import of Excel data into a SQL database. I'm using code below:

public ActionResult ImportDataAgens()
{
  return View();
}

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult ImportDataAgens(HttpPostedFileBase excelfileRekn)
{
  if (excelfileRekn == null || excelfileRekn.ContentLength == 0)
  {
    ViewBag.Error = "Please Select File <br>";
    return View("ImportDataAgens");
  }
  else
  {
    if (excelfileRekn.FileName.EndsWith("xls") || excelfileRekn.FileName.EndsWith("xlsx"))
    {
      string newFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff");
      string filename = Path.GetFileName(excelfileRekn.FileName);
      string DocFileNames = newFileName + "-" + filename;
      string path = System.IO.Path.Combine(Server.MapPath("~/UploadFile/DataAgen/"), DocFileNames);
      if (System.IO.File.Exists(path)) 
        System.IO.File.Delete(path);
      try
      {
        excelfileRekn.SaveAs(path);
        ViewData["Feedback"] = "Upload Complete";
      }
      catch (Exception ex)
      {
        ViewData["Feedback"] = ex.Message;
      }

      //read data from file excel 

      Excel.Application application = new Excel.Application();
      Excel.Workbook workbook = application.Workbooks.Open(path);
      Excel.Worksheet worksheet = workbook.ActiveSheet;
      Excel.Range range = worksheet.UsedRange;
      List<DMInformasiDataAgen> listTempRekn = new List<DMInformasiDataAgen>();
      for (int row = 3; row <= range.Rows.Count; row++)
      {
        DMInformasiDataAgen rk = new DMInformasiDataAgen();
        rk.NamaAgen = ((Excel.Range)range.Cells[row, 2]).Text;
        rk.NomorIdentifikasiAgen = ((Excel.Range)range.Cells[row, 3]).Text;
        rk.NomordanTanggalPerjanjian = ((Excel.Range)range.Cells[row, 4]).Text;
        rk.CreateBy = valueA;
        rk.UpdateDate = DateTime.Today;
        listTempRekn.Add(rk);
        db.DMInformasiDataAgen.Add(rk);
        db.SaveChanges();
        ViewBag.Result = "Successfully Imported";
      }
      return RedirectToAction("Index", "DataAgens");
    }
    else
    {
      ViewBag.Error = "This file format is not supported";
      return View("ImportDataAgens");
    }
  }
}

How can I verify the information from every Row?

1
0
10/13/2018 3:10:55 PM

Popular Answer

To avoid inserting to a list and providing information to the user, you may write validation functions for each type and then gather all of the unsuccessful rows.

0
7/14/2017 8:24:12 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow