Click here to Skip to main content
13,664,548 members
Rate this:
 
Please Sign up or sign in to vote.
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 18-Dec-17 3:11am
Updated 18-Dec-17 6:45am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

[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);
            }
        }
  Permalink  
Comments
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180810.1 | Last Updated 18 Dec 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100