需要验证行在Excel aps.net MVC
问题描述:
我有批量导入excel到sql数据库,我需要验证每一行,我使用的是Microsoft.Office.Interop.Excel Services。我正在使用以下代码:需要验证行在Excel aps.net MVC
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");
}
}
}
如何验证所有行中的数据?
答
您可以为每种类型创建验证功能,然后您可以收集所有失败的行以避免instert列出并向用户提供信息。
+0
感谢您的支持,我不知道如何在导入文件时从excel中分离行,并在批量导入数据库时验证行分隔。 –
您可以使用数据行或dt.rows验证数据值[“columnname”]值可以与代码一起使用,也可以创建正则表达式验证,以确定您期望使用哪种类型的值。 –