I have this Model Class
public COUNTRIES()
{
public int COUNTRY_ID { get; set; }
public string COUNTRY_CODE { get; set; }
public string COUNTRY_NAME { get; set; }
}
I want to perform batch upload of data from Excel with all the fields above and save into the database. I want the code to use the field
COUNTRY_CODE as the
UniqueIdentifier.
It will iterate through the Excel Sheet to check if COUNTRY_CODE exists in the database.
1. The application will save the one that are not in existence using COUNTRY_CODE
2. It will show the user the list of COUNTRY_CODE that exists in the database.
3. it will ask if the user wants to proceed: those to update and those to skip.
4. The user will be able to select the ones he wants to update from the last.
5. The application will save the selected ones and discard the unselected one.
Please how do I go about it.
The code I have written will only upload and save, but will not treat duplicates.
[HttpPost]
public ActionResult ImportCountriesExcel(HttpPostedFileBase FileUpload)
{
string data = "";
var bodsList = new List<COUNTRIES>();
if (FileUpload != null)
{
HttpPostedFileBase file = Request.Files["FileUpload"];
if (true)
{
try
{
string message = "";
using (var package = new ExcelPackage(FileUpload.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
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 COUNTRIES();
bod.COUNTRY_CODE = Convert.ToString(workSheet.Cells[rowIterator, 1].Value).Trim();
bod.COUNTRY_NAME = Convert.ToString(workSheet.Cells[rowIterator, 2].Value).Trim();
bodsList.Add(bod);
}
}
foreach (var item in bodsList)
{
_countriesService.AddCountry(item);
}
return RedirectToAction("Index");
}
catch (Exception e)
{
notif.notif_message = "Data import error, Kindly verify the data.";
notif.notif_type = NotificationType.ERROR;
}
TempData["notif"] = notif;
return RedirectToAction("Index");
}
else
{
notif.notif_type = NotificationType.ERROR;
data += "<ul>";
data += "<li>Only Excel file format is allowed</li>";
data += "</ul>";
notif.notif_message = data;
TempData["notif"] = notif;
return RedirectToAction("Index");
}
}
else
{
notif.notif_type = NotificationType.ERROR;
data += "<ul>";
if (FileUpload == null) data += "<li>Please choose Excel file</li>";
data += "</ul>";
notif.notif_message = data;
TempData["notif"] = notif;
return RedirectToAction("Index");
}
}
What I have tried:
[HttpPost]
public ActionResult ImportCountriesExcel(HttpPostedFileBase FileUpload)
{
string data = "";
var bodsList = new List<COUNTRIES>();
if (FileUpload != null)
{
HttpPostedFileBase file = Request.Files["FileUpload"];
if (true)
{
try
{
string message = "";
using (var package = new ExcelPackage(FileUpload.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
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 COUNTRIES();
bod.COUNTRY_CODE = Convert.ToString(workSheet.Cells[rowIterator, 1].Value).Trim();
bod.COUNTRY_NAME = Convert.ToString(workSheet.Cells[rowIterator, 2].Value).Trim();
bodsList.Add(bod);
}
}
foreach (var item in bodsList)
{
_countriesService.AddCountry(item);
}
return RedirectToAction("Index");
}
catch (Exception e)
{
notif.notif_message = "Data import error, Kindly verify the data.";
notif.notif_type = NotificationType.ERROR;
}
TempData["notif"] = notif;
return RedirectToAction("Index");
}
else
{
notif.notif_type = NotificationType.ERROR;
data += "<ul>";
data += "<li>Only Excel file format is allowed</li>";
data += "</ul>";
notif.notif_message = data;
TempData["notif"] = notif;
return RedirectToAction("Index");
}
}
else
{
notif.notif_type = NotificationType.ERROR;
data += "<ul>";
if (FileUpload == null) data += "<li>Please choose Excel file</li>";
data += "</ul>";
notif.notif_message = data;
TempData["notif"] = notif;
return RedirectToAction("Index");
}
}