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

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.

ParentID1 is the Level
ParentID of the Root Category(Level 1) will be set to 0
For the ones that are not Root
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();

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");

}

sir

put condition inthe for loop

I try some code

using (var pac = new OfficeOpenXml.ExcelPackage(file.InputStream))

{

var levellist = new List();

var cursheet = pac.Workbook.Worksheets;

var worksheet = cursheet.First();

var Noofcol = worksheet.Dimension.End.Column;

var Noofrow = worksheet.Dimension.End.Row;

int catid = 1;

for (int i = 2; i < Noofrow; i++)

{

CatagoryLevel cat = new CatagoryLevel();

string catname1 = Convert.ToString(worksheet.Cells[i, 1].Value).Trim();

string catname2 = Convert.ToString(worksheet.Cells[i, 2].Value).Trim();

string catname3 = Convert.ToString(worksheet.Cells[i, 3].Value).Trim();

string catname4 = Convert.ToString(worksheet.Cells[i, 4].Value).Trim();

if (catname1 != null && catname1 != "")

{

cat.CatagotyId = catid;

cat.ParentId = 0;

cat.ParentId1 = 1;

cat.ParentId2 = catid;

cat.CatagotyName = catname1;

}

else if (catname2 != null && catname2 != "")

{

}

// ...................

// ..................

catid += 1;

levellist.Add(cat);

}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo8y ago
sir
put condition inthe for loop

I try some code

using (var pac = new OfficeOpenXml.ExcelPackage(file.InputStream))
{
var levellist = new List();
var cursheet = pac.Workbook.Worksheets;
var worksheet = cursheet.First();
var Noofcol = worksheet.Dimension.End.Column;
var Noofrow = worksheet.Dimension.End.Row;
int catid = 1;
for (int i = 2; i < Noofrow; i++)
{
CatagoryLevel cat = new CatagoryLevel();
string catname1 = Convert.ToString(worksheet.Cells[i, 1].Value).Trim();
string catname2 = Convert.ToString(worksheet.Cells[i, 2].Value).Trim();
string catname3 = Convert.ToString(worksheet.Cells[i, 3].Value).Trim();
string catname4 = Convert.ToString(worksheet.Cells[i, 4].Value).Trim();
if (catname1 != null && catname1 != "")
{
cat.CatagotyId = catid;
cat.ParentId = 0;
cat.ParentId1 = 1;
cat.ParentId2 = catid;
cat.CatagotyName = catname1;
}
else if (catname2 != null && catname2 != "")
{

}
// ...................
// ..................
catid += 1;
levellist.Add(cat);
}
}