Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Store Locator: Excel Import
<!-- 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>
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}
};
}
}
}
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;
}
}
}
}
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);
}
}
}
}
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");
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.