Need Validation Row in Excel aps.net MVC

asp.net c# sqlbulkcopy sql-server

Question

I have bulk import excel to sql database, and I need to validate each row, and I'm using Microsoft.Office.Interop.Excel Services. Below I'm Using code :

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 validate data from all the Rows?

Popular Answer

You can create validation function for each types then you can collect all of failed row for avoid instert to list and giving information to user.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why