Click here to Skip to main content
14,698,178 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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)//FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            try
            {
                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 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;
            //alert message for invalid file format
            data += "<ul>";
            data += "<li>Only Excel file format is allowed</li>";
            data += "</ul>";
            notif.notif_message = data;
            TempData["notif"] = notif;
            return RedirectToAction("Index");
            //return Json(data, JsonRequestBehavior.AllowGet);
        }
    }
    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");
        //return Json(data, JsonRequestBehavior.AllowGet);
    }
}


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)//FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            try
            {
                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 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;
            //alert message for invalid file format
            data += "<ul>";
            data += "<li>Only Excel file format is allowed</li>";
            data += "</ul>";
            notif.notif_message = data;
            TempData["notif"] = notif;
            return RedirectToAction("Index");
            //return Json(data, JsonRequestBehavior.AllowGet);
        }
    }
    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");
        //return Json(data, JsonRequestBehavior.AllowGet);
    }
}
Posted
Updated 18-Dec-17 7:45am

1 solution

[HttpPost]
 
        public ActionResult ImportCountriesExcel(HttpPostedFileBase FileUpload)
        {
            string data = "";
            var bodsList = new List<COUNTRIES>();
            if (FileUpload != null)
            { 
                HttpPostedFileBase file = Request.Files["FileUpload"];
                if (true)//FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    try
                    {
                        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 COUNTRIES();
                                bod.COUNTRY_CODE = Convert.ToString(workSheet.Cells[rowIterator, 1].Value).Trim();
                                bod.COUNTRY_NAME = Convert.ToString(workSheet.Cells[rowIterator, 2].Value).Trim();
string chk = Convert.ToString(workSheet.Cells[rowIterator, 1].Value).Trim();
bool isexists = bodsList.Any( x =>x.COUNTRY_CODE == chk )
if(!isexists)
 bodsList.Add(bod);
                                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;
                    //alert message for invalid file format  
                    data += "<ul>";
                    data += "<li>Only Excel file format is allowed</li>";
                    data += "</ul>";
                    notif.notif_message = data;
                    TempData["notif"] = notif;
                    return RedirectToAction("Index");
                    //return Json(data, JsonRequestBehavior.AllowGet);
                }
            }
            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");
                //return Json(data, JsonRequestBehavior.AllowGet);
            }
        }
   
Comments
Member 2294293 18-Dec-17 14:07pm
   
How do I give the user the choice to choose from the list of data that already exist, either to update or disregard

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900