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?
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.