How to import pictures from an Excel Spreadsheet

So yesterday, I submitted a post on how to use the import from Excel button on the product maintenance control panel to import a spreadsheet of products.  I mentioned how to first create a bogus product if your catalog is empty and export the product to excel so that you start off with a base template to copy and paste your product list to.  I also mentioned how I tweaked the code because it didn't like my date string for creation date and it didn't like null values in my weight, length, width, height columns.

I didn't import the pictures yesterday.  I am not sure what would happen if I re-imported the same excel spreadsheet with the Picture1 column filled in.  Instead of finding out the hard way, I decided to go a harder route and create an Import Pictures from Excel button.  Actually, it wasn't too hard.  I started at 9:00AM this morning and I am writing this at 11:45AM so, it should take you about an hour if you follow my steps.

So, I opened up Visual Studio 2010 and looked for occurrences of importing excel.  If you search for the term "ImportExcel" in the entire solution, you will find that you will have to modify the following files:

Nop.Web\App_Data\defaultResources.admin.nopres.xml
Nop.Web\Administration\Views\Product\List.cshtml
Nop.Web\Administration\Controllers\ProductController.cs
Nop.Services\ExportImport\ImportManager.cs
Nop.Services\ExportImport\IImportManager.cs

List.cshtml:


Everywhere I found ImportExcel, I made a copy.  I replaced in my copy importexcel with importpicturesexcel.

  <div class="options">
            <a href="@Url.Action("Create")" class="t-button">@T("Admin.Common.AddNew")</a> <a href="@Url.Action("DownloadCatalogAsPdf")" class="t-button">@T("Admin.Catalog.Products.List.DownloadPDF")</a>
            <a href="@Url.Action("ExportXml")" class="t-button">@T("Admin.Common.ExportToXml")</a>
            <a href="@Url.Action("ExportExcel")" class="t-button">@T("Admin.Common.ExportToExcel")</a>
            <button type="submit" id="importexcel" name="importexcel" value="importexcel" class="t-button">@T("Admin.Common.ImportFromExcel")</button>
            <button type="submit" id="importpicturesexcel" name="importpicturesexcel" value="importpicturesexcel" class="t-button">@T("Admin.Common.ImportPicturesFromExcel")</button>
        </div>

So, in List.cshtml, I added another button and copied the code for the pop up form at the bottom and made an importpictureexcel equivalent.

When I ran to test it, instead of having my button read Import Pictures From Excel, the label was Admin.Common.ImportPicturesFromExcel.  I wrongly assumed that adding a record to the defaultResources.admin.nopres.xml file would be picked up in nopCommerce.  The way to set the label is to go to the backend administration, Configuration, Languages, and edit the English language.  I added a record called Admin.Common.ImportPicturesFromExcel and gave it the label, "Import Pictures From Excel".  While I was there, I also updated the Label for Admin.Common.ImportFromExcel to "Import Products From Excel" to differentiate from my new button.

ProductController.cs

In ProductController.cs, I copied ImportExcel and renamed the copy ImportPicturesExcel.  The only real difference is the line:

_importManager.ImportPicturesFromXls(filePath);

   [HttpPost]
        public ActionResult ImportPicturesExcel(FormCollection form)
        {
            if (!_permissionService.Authorize(StandardPermissionProvider.ManageCatalog))
                return AccessDeniedView();

            try
            {
                var file = Request.Files["importpicturesexcelfile"];
                if (file != null && file.ContentLength > 0)
                {
                    var fileBytes = new byte[file.ContentLength];
                    file.InputStream.Read(fileBytes, 0, file.ContentLength);
                    //do stuff with the bytes
                    string extension = "xls";
                    if (file.FileName.EndsWith("xlsx"))
                        extension = "xlsx";

                    string fileName = string.Format("products_{0}_{1}.{2}", DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss"), CommonHelper.GenerateRandomDigitCode(4), extension);
                    string filePath = string.Format("{0}content\\files\\ExportImport\\{1}", Request.PhysicalApplicationPath, fileName);

                    System.IO.File.WriteAllBytes(filePath, fileBytes);
                    // LJD 11/09/2011
                    //_importManager.ImportProductsFromXls(filePath);
                    _importManager.ImportPicturesFromXls(filePath);
                }
                else
                {
                    ErrorNotification("Please upload a file");
                    return RedirectToAction("List");
                }
                SuccessNotification(_localizationService.GetResource("Admin.Catalog.Products.Imported"));
                return RedirectToAction("List");
            }
            catch (Exception exc)
            {
                ErrorNotification(exc);
                return RedirectToAction("List");
            }

        }


ImportManager.cs

In the ImportManger.cs class found in nopServices\ExportImport\ , I copied the ImportProductsFromXls code and renamed my copy ImportPicturesFromXls.  The Pictures Import code is a scaled down version of ImportProductsFromXls because I am only dealing with 2 columns in the spreadsheet:  Sku, and Picture

/// <summary>
        /// Import pictures from XLS file
        /// </summary>
        /// <param name="filePath">Excel file path</param>
        public virtual void ImportPicturesFromXls(string filePath)
        {
            using (var excelHelper = new ExcelHelper(filePath))
            {
                excelHelper.Hdr = "YES";
                excelHelper.Imex = "1";

                DataTable dt = excelHelper.ReadTable("Products");
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr.ItemArray.All(i => string.IsNullOrEmpty(i.ToString())))
                        continue;
                    string sku = dr["SKU"].ToString();
                    string picture = dr["Picture"].ToString();

                    var productVariant = _productService.GetProductVariantBySku(sku);
                    if (productVariant != null)
                    {
                        var product = productVariant.Product;
                        if (String.IsNullOrEmpty(picture))
                            continue;

                        productVariant.Product.ProductPictures.Add(new ProductPicture()
                        {
                            Picture = _pictureService.InsertPicture(File.ReadAllBytes(picture), "image/jpeg", _pictureService.GetPictureSeName(productVariant.Name), true),
                            DisplayOrder = 1,
                        });
                        _productService.UpdateProduct(productVariant.Product);

                    }
                }
            }
        }

IImportManager.cs

Add   void ImportPicturesFromXls(string filePath); to IImportManager.cs (Don't ask, just do it)
NOTE:  Don't forget to rebuild the Nop.Services

/// <summary>
        /// Import pictures from XLS file
        /// </summary>
        /// <param name="filePath">Excel file path</param>
        void ImportPicturesFromXls(string filePath);

The Excel Spreadsheet:
All you need now is an Excel Spreadsheet with 2 columns:
Sku - Contains the Product Sku
Picture - Contains the Full Path of the image file on your hard drive.

NOTE: the ImportPicturesFromXls is looking to read a spreadsheet called Products so make sure you name Sheet1 to Products.

Run your modified product administration control panel and import your product catalog pictures.  You can thank me by sending whatever you would bill your customers for inserting their pictures (Optional).

Will try this in my next project , Also have u done something like showing products and categories on the same page for instance , i have got products with only 4 columns , price , code , description and one more column , so what i was looking for when a client click on a category the system should navigate to the page like below instead of going through several pages, Is it possible to display in this manner:

Parent Category(Description)


sub-category1(description)
product 1(columns , price , code , description)
product 2(columns , price , code , description)
product 3(columns , price , code , description)
product 4(columns , price , code , description)
product 5(columns , price , code , description)

sub-category1(description)
product 1(columns , price , code , description)
product 2(columns , price , code , description)
product 3(columns , price , code , description)
product 4(columns , price , code , description)
product 5(columns , price , code , description)

Thanks

Is this for the front end or back end adminiistration?  Are you trying to easily assign products to categories or are you trying to easily maintain products within a category?

No its for the front end, might consider back-end later on when i will have a grip on nop-commerce

I'll have to look into it. I would start by checking out the code with the left side navigation when you click on a Category that has sub-categories.