Create New Content Nodes Programmatically In Umbraco 8 From Excel.

Sometimes we need to import data into the CMS via Excel, there are products on the market that we can purchase to do this, but what if the client is not willing to purchase the product.

In that case, then we have to roll our sleeves up and write some code that does the job for us.

In this blog, I'll show you how to do this using ExcelDataReader that can be installed via NuGet in Visual Studio.

I'm using ExcelDataReader" version="3.6.0" and ExcelDataReader.DataSet" version="3.6.0" and Umbraco version="8.12.2" in this example.

The code in this blog also shows how you can delete empty folders under the Media folder as well as delete all images and nodes from under the parent node.

Finally, it also shows how you can use Serilog to log errors with custom messages. 

using System.Linq;
using System.Web;
using Umbraco.Core.Composing;
using Umbraco.Core.Events;
using Umbraco.Core.Services;
using Umbraco.Core.Services.Implement;

namespace Web.ContentHelper.Helpers
{
    public class ExtractProductsComponent : IComponent
    {
        private readonly IGetExcelDataFromFile _getExcelDataFromFile;

        public ExtractProductsComponent(IGetExcelDataFromFile getExcelDataFromFile)
        {
            _getExcelDataFromFile = getExcelDataFromFile;
        }
        public void Initialize()
        {
            ContentService.Published += ContentService_Published;
        }
        private void ContentService_Published(IContentService sender, ContentPublishedEventArgs e)
        {
            var node = e.PublishedEntities.ToList();
            if (e.PublishedEntities.Any(x => x.ContentType.Alias == "cmsProductHomeDocumentType"))
            {
                foreach (var items in node)
                {
                    string urlToFile    = items.GetValue("fileUploadCmsProductHomeDocumentType");
                    string mapPath      = HttpContext.Current.Server.MapPath(urlToFile);
                    e.Messages.Add(new EventMessage("Zip File", "Extracting Excel File to upload", EventMessageType.Success));
                    bool status         = _getExcelDataFromFile.ReadExcelFile(mapPath, out int recordsInserted, out int totalRecordCount);

                    if (status)
                    {
                        string message;
                        if (recordsInserted == totalRecordCount)
                        {
                            message = $"Inserted {recordsInserted} out of {totalRecordCount} records";
                            e.Messages.Add(new EventMessage("Zip File", message, EventMessageType.Success));

                        }
                        else
                        {
                            message = $"Inserted {recordsInserted} out of {totalRecordCount} records, please check logfile for failed products";
                            e.Messages.Add(new EventMessage("Zip File", message, EventMessageType.Error));
                        }
                    }
                    else
                    {
                        e.Messages.Add(new EventMessage("Zip File", "Insert failed", EventMessageType.Error));
                    }
                    
                }
            }
        }
        public void Terminate()
        {
            //unsubscribe during shutdown
            ContentService.Published -= ContentService_Published;
        }
    }
}

The code below shows how we can loop over the Excel file.

using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Web;
using Umbraco.Core;
using Umbraco.Core.Models;
using Umbraco.Core.Services;
using Umbraco.Core.Composing;
using Umbraco.Core.Models.PublishedContent;
using Umbraco.Core.Logging;
using Umbraco.Web;
using File = System.IO.File;

namespace Web.ContentHelper.Helpers
{
    public class GetExcelDataFromFile : IGetExcelDataFromFile
    {
        private readonly IUmbracoContextFactory     _contextFactory;
        private readonly IContentService            _contentService;
        private readonly IMediaService              _mediaService;
        private readonly ILogger                    _logger;

        public GetExcelDataFromFile(IUmbracoContextFactory umbracoContextFactory, IContentService contentService, IMediaService mediaService, ILogger logger)
        {
            _contextFactory     = umbracoContextFactory;
            _contentService     = contentService;
            _mediaService       = mediaService;
            _logger             = logger;
        }

        public bool ReadExcelFile(string pathToMedia, out int recordsInserted, out int totalRecordCount)
        {
            string extractImagesTo  = HttpContext.Current.Server.MapPath("~/ExtractedImages");
            string zipFilePath      = HttpContext.Current.Server.MapPath("~/ExtractedZipFile");

            int successfullInsert   = 0;
            int totalRecords        = 0;

            string pathToExcelFileOnServer = string.Empty;

            using (ZipArchive archive = ZipFile.OpenRead(pathToMedia))
            {
                foreach (ZipArchiveEntry entry in archive.Entries)
                {
                    if (entry.FullName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) || entry.FullName.EndsWith("xls", StringComparison.OrdinalIgnoreCase))
                    {
                        if (File.Exists(Path.Combine(zipFilePath, entry.Name)))
                        {
                            File.Delete(Path.Combine(zipFilePath, entry.Name));
                        }

                        pathToExcelFileOnServer = Path.Combine(zipFilePath, entry.Name);
                        entry.ExtractToFile(Path.Combine(zipFilePath, entry.Name));
                    }
                    else
                    {
                        if (!entry.FullName.EndsWith(".png", StringComparison.OrdinalIgnoreCase) && !entry.FullName.EndsWith(".jpg", StringComparison.OrdinalIgnoreCase)) continue;
                        if (File.Exists(Path.Combine(extractImagesTo, entry.Name)))
                        {
                            File.Delete(Path.Combine(extractImagesTo, entry.Name));
                        }
                        entry.ExtractToFile(Path.Combine(extractImagesTo, entry.Name));
                    }
                }
            }

            if (!string.IsNullOrEmpty(pathToExcelFileOnServer))
            {
                using (var stream = File.Open(pathToExcelFileOnServer, FileMode.Open, FileAccess.Read))
                {
                    using (var reader = ExcelReaderFactory.CreateReader(stream))
                    {
                        var result = reader.AsDataSet();

                        List excelList = new List();

                        foreach (DataTable table in result.Tables)
                        {
                            foreach (DataRow dr in table.Rows.Cast().Skip(1)) //Skipping header
                            {
                                excelList.Add(new ProductsViewModel(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), Path.Combine(extractImagesTo, dr[4].ToString())));
                            }
                        }

                        var cmsProductHomeDocumentTypeNodeId = _contextFactory.EnsureUmbracoContext().UmbracoContext.Content.GetByXPath("//cmsProductHomeDocumentType").First();


                        var itemsForSale = _contextFactory.EnsureUmbracoContext().UmbracoContext.Content.GetByXPath("//cmsProductHomeDocumentType").SelectMany(x => x.ChildrenOfType("cmsProductDescriptionDocumentType")).ToList();

                        //Deleting all nodes and images
                        foreach (var nodes in itemsForSale)
                        {
                            var deleteItem = _contentService.GetById(nodes.Id);
                            var mediaId = nodes.Value("cmsProductDescriptionDocumentTypeImage");
                            //Check media exists
                            if (mediaId != null)
                            {
                                DeleteMediaById(mediaId.Id);
                            }
                            
                            if (deleteItem != null)
                            {
                                _contentService.Delete(deleteItem);
                            }
                        }

                        totalRecords = excelList.Count;
                        //Loop over all items in list and insert into CMS 
                        foreach (var item in excelList)
                        {
                            try
                            {
                                //If price is not a valid double, throw an error, could do more validation if required
                                double price = double.Parse(item.Price);
                                var content = _contentService.Create($"{item.Title}", Convert.ToInt32(cmsProductHomeDocumentTypeNodeId.Id), "cmsProductDescriptionDocumentType");
                                var contentTypeBaseServiceProvider = Current.Services.ContentTypeBaseServices;
                                IMedia newImage = CreateMediaItem(-1, item.Image, contentTypeBaseServiceProvider);
                                content.SetValue("cmsProductDescriptionDocumentTypeProductId", item.ProductId);
                                content.SetValue("cmsProductDescriptionDocumentTypeTitle", item.Title);
                                content.SetValue("cmsProductDescriptionDocumentTypeDescription", item.Description);
                                content.SetValue("cmsProductDescriptionDocumentTypePrice", price);
                                content.SetValue("cmsProductDescriptionDocumentTypeImage", newImage.GetUdi().ToString());
                                _contentService.SaveAndPublish(content);
                                successfullInsert++;
                            }
                            catch (Exception e)
                            {
                                _logger.Error("Failed to insert productID {0} error {1}", item.ProductId, e.Message);
                            }
                            
                        }
                    }
                }
                //Delete content of ExtractedImages once complete
                Array.ForEach(Directory.GetFiles(extractImagesTo), File.Delete);
                //Delete content of zipFilePath once complete
                Array.ForEach(Directory.GetFiles(zipFilePath), File.Delete);
                //Clean up Media folder and delete all empty folders
                DeleteEmptyFolders();
                recordsInserted     = successfullInsert;
                totalRecordCount    = totalRecords;
                return true;
            }
            recordsInserted     = successfullInsert;
            totalRecordCount    = totalRecords;
            return false;
        }

        /// 
        /// Create New Media Image in CMS
        /// 
        ///
        ///
        ///
        /// 
        private IMedia CreateMediaItem(int parentId, string fileName, IContentTypeBaseServiceProvider contentTypeBaseServiceProvider)
        {
            string newFileName = Guid.NewGuid().ToString();
            IMedia newFile = _mediaService.CreateMedia(newFileName, parentId, "Image");
            string filePath = fileName;

            using (FileStream stream = File.Open(filePath, FileMode.Open))
            {
                newFile.SetValue(contentTypeBaseServiceProvider, "umbracoFile", $"{newFileName}.png", stream);
            }
            _mediaService.Save(newFile);
            return newFile;
        }

        /// 
        /// Delete all images that match ID
        /// 
        ///
        private void DeleteMediaById(int mediaId)
        {
            IMedia media = _mediaService.GetById(mediaId);
            if (media != null)
                _mediaService.Delete(media);
        }

        /// 
        /// Delete all empty folders in the Media Folder
        /// 
        private static void DeleteEmptyFolders()
        {
            string pathToMediaFolder = "~/Media";

            foreach (string subDictionary in Directory.GetDirectories(HttpContext.Current.Server.MapPath(pathToMediaFolder)))
            {
                if (Directory.Exists(HttpContext.Current.Server.MapPath(pathToMediaFolder)))
                {
                    string[] file = Directory.GetFiles(subDictionary, "*.*");

                    if (file.Length == 0)
                    {
                        Directory.Delete(subDictionary);
                    }
                }
            }
        }
    }
}

View Model Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Web.ContentHelper.Helpers
{
    public class ProductsViewModel
    {
        public ProductsViewModel(string productId, string title, string description, string price, string image)
        {
            ProductId       = productId;
            Title           = title;
            Description     = description;
            Price           = price;
            Image           = image;
        }
        public string ProductId         { get;}
        public string Title             { get;}
        public string Description       { get;}
        public string Price             { get; }
        public string Image             { get; }
    }
}

Summary

In this blog, I showed how you can insert data into Umbraco 8 from Excel using ExcelDataReader. This can be improved in the back office by polling the records inserted to show the progress. Instead of just showing an EventMessage.