ASP.NET MVC - Import Item with Parent Category from Excel

Question

I have this Excel sheet as shown below

 

 

 The table is shown below

 

 

 

 

I want to import from the Excel and save into the database using ASP.NET MVC.

From the Excel, when it is Level 1(Root Category), then ParentID will be 0.

1. ParentID1 is the Level
2. ParentID of the Root Category(Level 1) will be set to 0
3. For the ones that are not Root
4. The Category can be to any Level 

Please how do I import this data from the Excel and save into the database.

 

I started a code but don't know how to complete it, because of the complexity. See my Controller below.

 

 

 

public ActionResult ImportCountriesExcel(HttpPostedFileBase FileUpload)

{

string data = "";

var bodsList = new List<CategoryLevel>();

if (FileUpload != null)

{

HttpPostedFileBase file = Request.Files["FileUpload"];

if (true)

{

string message = "";

using (var package = new ExcelPackage(FileUpload.InputStream))

{

var currentSheet = package.Workbook.Worksheets;

// if(currentSheet.Count)

var workSheet = currentSheet.First();

var noOfCol = workSheet.Dimension.End.Column;

var noOfRow = workSheet.Dimension.End.Row;

for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)

{

var bod = new CategoryLevel();

bod.CategoryName = Convert.ToString(workSheet.Cells[rowIterator, 1].Value).Trim();

bod.ParentID = Convert.ToSInt(workSheet.Cells[rowIterator, 2].Value);

bod.ParentID1 = Convert.ToSInt(workSheet.Cells[rowIterator, 3].Value);

bod.ParentID2 = Convert.ToSInt(workSheet.Cells[rowIterator, 4].Value);

bodsList.Add(bod);

}

}

foreach (var item in bodsList)

{

_categorylevelService.AddCategoryLevel(item);

}

return RedirectToAction("Index");

}

else

{

return RedirectToAction("Index");

}

}

else

{

return RedirectToAction("Index");

}

}