Store Locator: Excel Import

ImportShopWidget.cshtml

<!-- Widget: admin_header_before  --> 
<script type="text/javascript">

    $(function () {
        var importButton = $('<a href="#" class="k-button">Import</a>').click(function (e) {
            e.preventDefault();
            var window = $("#importexcel-window");
            if (!window.data("kendoWindow")) {
                window.kendoWindow({
                    modal: true,
                    width: "400px",
                    title: "@T("Admin.Common.ImportFromExcel")",
                    actions: ["Close"]
                });
            }
            window.data('kendoWindow').center().open();
        });

        $('a[href="/Admin/StoreLocatorAdmin/Create"]')
            .closest('div.options')
            .prepend(importButton);
    });


</script>
<div id="importexcel-window" style="display:none;">
    @using (Html.BeginRouteForm("Plugin.Misc.MyPlugin.Storelocator.Import", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
        @Html.AntiForgeryToken()
        <table style="text-align:left;">
            <tr>
                <td>
                    @T("Admin.Common.ExcelFile"):
                </td>
                <td>
                    <input type="file" id="importexcelfile" name="importexcelfile" />
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <input type="submit" class="k-button" value="@T("Admin.Common.ImportFromExcel")" />
                </td>
            </tr>
        </table>
    }
</div>
<script type="text/javascript">
    $(document).ready(function () {
        $("#importexcel").click(function (e) {

        });
    });
</script>

MyPlugin.cs

using System;
using System.Collections.Generic;
using System.Web.Routing;
using Nop.Core.Domain.Tasks;
using Nop.Core.Plugins;
using Nop.Services.Cms;
using Nop.Services.Tasks;

namespace Nop.Plugin.Misc.MyPlugin
{
    public class MyPlugin : BasePlugin, IWidgetPlugin
    {
        public IList<string> GetWidgetZones()
        {
            return new List<string> { "admin_header_before" };
        }

        public void GetConfigurationRoute(out string actionName, out string controllerName, out RouteValueDictionary routeValues)
        {
            throw new NotImplementedException();
        }

        public void GetDisplayWidgetRoute(string widgetZone, out string actionName, out string controllerName, out RouteValueDictionary routeValues)
        {
            controllerName = "StorelocatorImport";
            actionName = "ImportShopWidget";

            routeValues = new RouteValueDictionary()
            {
                {"Namespaces", "Nop.Plugin.Misc.Mpetfood.Controllers"},
                {"area", null},
                {"widgetZone", widgetZone}
            };
        }
    }
}

RouteProvider.cs

using Nop.Web.Framework.Mvc.Routes;
using System.Web.Mvc;
using System.Web.Routing;

namespace Nop.Plugin.Misc.MyPlugin
{
    public partial class RouteProvider : IRouteProvider
    {
        public void RegisterRoutes(RouteCollection routes)
        {
            routes.MapRoute("Plugin.Misc.MyPlugin.Storelocator.Import",
                 "Plugins/StorelocatorImport/Import",
                 new { controller = "StorelocatorImport", action = "Import" },
                 new[] { "Nop.Plugin.Misc.MyPlugin.Controllers" }
            );
        }
        public int Priority
        {
            get
            {
                return 1;
            }
        }
    }
}

ShopImportService.cs

using Nop.Core;
using Nop.Core.Data;
using Nop.Core.Domain.Common;
using Nop.Services.Common;
using Nop.Services.Logging;
using Nop.Services.Seo;
using OfficeOpenXml; // nuget: EPPlus
using SevenSpikes.Nop.Plugins.StoreLocator.Domain.Shops;
using SevenSpikes.Nop.Plugins.StoreLocator.Services;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace Nop.Plugin.Misc.MyPlugin.Services
{
    public class ShopImportService : IShopImportService
    {
        #region Fields
        private readonly ILogger _logger;
        private readonly IShopService _shopService;
        private readonly IGenericAttributeService _genericAttributeService;
        private readonly IUrlRecordService _urlRecordService;
        private readonly IRepository<GenericAttribute> _genericAttributeRepository;
        #endregion

        #region Ctor
        public ShopImportService(
            ILogger logger,
            IShopService shopService,
            IGenericAttributeService genericAttributeService,
            IUrlRecordService urlRecordService,
            IRepository<GenericAttribute> genericAttributeRepository
            )
        {
            this._logger = logger;
            this._shopService = shopService;
            this._genericAttributeService = genericAttributeService;
            this._urlRecordService = urlRecordService;
            this._genericAttributeRepository = genericAttributeRepository;
        }
        #endregion

        public void ImportShopsFromExcel(Stream stream)
        {
            using (var xlPackage = new ExcelPackage(stream))
            {
                var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    throw new NopException("No worksheet found");

                var shopKeys = (from ga in _genericAttributeRepository.Table
                                 where ga.KeyGroup == "Shop" && ga.Key == "Key"
                                 select new { Value = ga.Value, EntityId = ga.EntityId })
                                 .Distinct().ToDictionary(g => g.Value, g => g.EntityId);
                

                int iRow = 2;
                var tags = new List<string>();
                var previousKey = null as string;
                var shop = null as Shop;

                while (true)
                {
                    if (worksheet.Cells[iRow, 1].Value == null || string.IsNullOrEmpty(worksheet.Cells[iRow, 1].Value.ToString()))
                        break;

                    var productTag = worksheet.Cells[iRow, 1].Value.ToString().Replace(',', '.');
                    var key = worksheet.Cells[iRow, 3].Value.ToString();
                    var name = worksheet.Cells[iRow, 4].Value.ToString();
                    var city = worksheet.Cells[iRow, 9].Value.ToString();
                    var address = worksheet.Cells[iRow, 7].Value.ToString();
                    var zipcode = worksheet.Cells[iRow, 8].Value.ToString();
                    var phone = worksheet.Cells[iRow, 10].Value.ToString();

                    var addressLine = string.Format("{0}, {1} {2}", address, zipcode, city);
                    var fullDescription = string.Format("Phone: {0}", phone);

                    if (previousKey != null && previousKey != key) // New Shop, add/update prev shop
                    {
                        if (shop != null)
                        {
                            AddOrUpdateShop(shop, previousKey, tags);
                            tags.Clear();
                            shop = null;
                        }
                    }

                    tags.Add(productTag);

                    if (shopKeys.ContainsKey(key) && shop == null)
                    {
                        shop = _shopService.GetShopById(shopKeys[key]);
                        shop.Name = name;
                        shop.FullDescription = fullDescription;

                        if (shop.ShortDescription != addressLine)
                        {
                            _logger.Information(string.Format("Update Shop Address: {0}, {1} -> {2}", shop.Name, shop.ShortDescription, addressLine));
                            shop.Latitude = null;
                            shop.Longitude = null;
                            shop.IsVisible = false;
                            shop.ShortDescription = addressLine;
                        }
                        shopKeys.Remove(key);
                    }
                    else if (shop == null)
                    {
                        shop = new Shop
                        {
                            Name = name,
                            ShortDescription = addressLine,
                            FullDescription = fullDescription
                        };
                    }

                    previousKey = key;
                    iRow++;
                }
                
                AddOrUpdateShop(shop, previousKey, tags);

                foreach (var shopId in shopKeys.Values)
                {
                    var shopToDelete = _shopService.GetShopById(shopId);
                    if (shopToDelete != null)
                    {
                        _logger.Information(string.Format("Delete Shop: {0}", shopToDelete.Name));
                        _shopService.DeleteShop(shopToDelete);
                    }
                }

            }
        }

        private void AddOrUpdateShop(Shop shop, string key, List<string> tags)
        {
            shop.Tags = string.Join(",", tags);
            if (shop.Id == 0)
            {
                _shopService.InsertShop(shop);
                _genericAttributeService.SaveAttribute(shop, "Key", key);
                var seName = shop.ValidateSeName("", shop.Name, true);
                _urlRecordService.SaveSlug(shop, seName, 0);
                _logger.Information(string.Format("Insert Shop: {0}", shop.Name));
            }
            else
            {
                _shopService.UpdateShop(shop);
            }
        }
    }
}

StorelocatorImportController.cs

using Nop.Plugin.Misc.MyPlugin.Services;
using Nop.Services.Localization;
using Nop.Web.Framework.Controllers;
using System;
using System.Web.Mvc;

namespace Nop.Plugin.Misc.MyPlugin.Controllers
{
    public class StorelocatorImportController : BasePluginController
    {
        private readonly ILocalizationService _localizationService;
        private readonly IShopImportService _shopImportService;

        public StorelocatorImportController(
            IShopImportService shopImportService,
            ILocalizationService localizationService
            )
        {
            this._shopImportService = shopImportService;
            this._localizationService = localizationService;
        }

        public ActionResult ImportShopWidget()
        {

            return View("~/Plugins/Misc.MyPlugin/Views/ImportShopWidget.cshtml");
        }

        [HttpPost]
        public ActionResult Import()
        {
            try
            {
                var file = Request.Files["importexcelfile"];
                if (file != null && file.ContentLength > 0)
                {
                    _shopImportService.ImportShopsFromExcel(file.InputStream);
                }
                else
                {
                    ErrorNotification(_localizationService.GetResource("Admin.Common.UploadFile"));
                    return Redirect("~/Admin/StoreLocatorAdmin/List");
                }
                SuccessNotification(_localizationService.GetResource("Admin.Shop.Imported"));
                return Redirect("~/Admin/StoreLocatorAdmin/List");
            }
            catch (Exception exc)
            {
                ErrorNotification(exc);
                return Redirect("~/Admin/StoreLocatorAdmin/List");
            }
        }
    }
}