<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=1639164799743833&amp;ev=PageView&amp;noscript=1">
Diagram Views

Exporting Product Data as a CSV from Episerver Commerce

John McKillip
#Episerver, #Code
Published on July 31, 2018

Learn how Diagram's John McKillip exported product data as a CSV from Episerver Commerce.

Creating CSV reports of application data is a common programming task. On a recent Episerver Commerce project, I was tasked with building an API endpoint that exported product SKU's from Episerver Commerce to compare against a list exported from an ERP. It ended up being a great exercise in working with the Episerver Commerce catalog, the ASP.Net Web API and programmatically creating a CSV for download.

For this demo, let's pretend that we are in the middle of a sprint and the last piece of functionality we have to deliver is a way for site administrators to download a CSV export of all the product variants in the Episerver Commerce system and the last time they were updated. The first thing we will do is define a couple of interfaces:

namespace YourNamespace.Business.Interfaces
    public interface IProductExportService
        string ExportProductsLastModified();

    public interface IProductExport
        string Code { get; set; }
        string LastModified { get; set; }
        string Title { get; set; }

The first interface IProductExport has a single method which returns a string. In our implementation, we will return a CSV string. The second interface IProductExport contains the fields we will export for each product. The bulk of the code we will write is in the implementation for IProductExportService. Let's take a look at that next. I have added some comments to the code to make it easier to see what is happening:

namespace YourNamespace.Services.Export
    using EPiServer;
    using EPiServer.Commerce.Catalonamespace
    using EPiServer;
    using EPiServer.Commerce.Catalog.ContentTypes;
    using Mediachase.Commerce.Catalog;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    public class ProductSkuExportService : IProductExportService
        private readonly IContentLoader _loader;
        private readonly ReferenceConverter _referenceConverter;
        private Func<IProductExport> _productExportFactory;

        public ProductSkuExportService(
            IContentLoader loader, 
            ReferenceConverter referenceConverter,
            Func<IProductExport> productExportFactory)
            _loader = loader;          
            _referenceConverter = referenceConverter;
            _productExportFactory = productExportFactory;

        public string ExportProductsLastModified()
            var output = CreateFileForOutput();
            List<IProductExport> products = new List<IProductExport>();
            // Get a reference to the first catalog in the system
            var catalog = _loader.GetChildren<CatalogContent>(_referenceConverter.GetRootLink()).FirstOrDefault();

            // Get the descendents of the catalog root
            var content = _loader.GetDescendents(catalog.ContentLink);

            // For the sake of brevity. If we wanted this to perform better, 
            // we would build a service that uses Vulcan or Find to pull variants directly 
            // instead of looping through everything in the catalog
            foreach (var item in content)
                var p = _loader.Get<VariationContent>(item);
                if (p != null)
                    var product = _productExportFactory();
                    product.Code = p.Code;
                    product.LastModified = p.Changed;
                    product.Title = p.Name;


            // Add the products to our StringBuilder
            var productIterator = products.GetEnumerator();
            while (productIterator.MoveNext())

             return output.ToString();

        private StringBuilder CreateFileForOutput()
            StringBuilder output = new StringBuilder(100000);
            output.AppendLine("\"Code\",\"Title\",\"Last Modified\"");

            return output;

        private static string SetCsvValue(string value) => WriteCSV(string.IsNullOrWhiteSpace(value) ? "n/a" : value);

        private string WriteCSV(string input)
            input = HttpUtility.HtmlDecode(input);

            if (string.IsNullOrEmpty(input))
                return string.Empty;
            else if (input.ToString().IndexOf(",") == -1)
                return input;
                return string.Format(@"""{0}""", input.Replace(@"""", @""""""));

As far as Episerver Commerce is concerned, working with catalog data is pretty easy. First, we use ReferenceConverter to get the first catalog in the system. From there, we use IContentLoader to get the descendants of the catalog root. Next, we loop through them and create our instances of IProductExport and add them to a list. The last part of this service loops through the list of products we just created and adds the data to a StringBuilder property. There are a few private helper methods to create the StringBuilder object and set the CSV value. Once we have our StringBuilder object, we call ToString() and return it.

One thing to note here, which you may have noticed getting injected into the constructor of our service is this: Func<IProductExport> productExport. With this, we can simply set a variable to _productExportFactory() and the container will create an instance of our IProductExport interface, which I think is pretty cool. Now we can avoid having to new-up instances of our concrete implementation class. Just remember to wire-up the implementation of IProductExport with a transient lifecycle, as you will create many multiples of this for the export.

Lastly, we need to create an API controller to setup the endpoint admin users can access for the download:

namespace YourNamespace.Controllers.api
    using YourNamespace.Business.Interfaces;
    using System.Net;
    using System.Net.Http;
    using System.Net.Http.Headers;
    using System.Web.Http;

    [Authorize(Roles = "Administrators")]
    public class ProductReportController : ApiController
        private readonly IProductSkuExportService _productExportService;

        public ProductReconciliationController(IProductSkuExportService productExportService)
            _productExportService = productExportService;

        [Route("ExportEpiserverProducts"), HttpGet]
        public HttpResponseMessage ExportEpiserverProducts()
            HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);

            result.Content = new StringContent(_productExportService.ExportEpiserverProducts());
            result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
            result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); //attachment will force download
            result.Content.Headers.ContentDisposition.FileName = "EpiserverProductExport.csv";

            return result;

It really is that simple. All we have to do is inject our product export service and call the ExportEpiserverProducts method, then create our HttpResponseMessage to return to the browser. There are a few things to note in this class.

Remember that one of the requirements was that only site administrators can run the export.
We don't want every user who comes to the site having the ability to download this CSV export. To achieve this, we simply use the  [Authorize(Roles = "Administrators")] attribute on our API controller class definition.

You can specify a single role, or multiple roles or no roles at all. If there are no roles specified, it just means that a user will have to be logged in to hit the end point. Now, if we build our solution and go to {www.yoursiteurl.com}/api/ProductExport/ExportEpiserverProducts, you will get a sweet CSV download of all the products in your system! 

As you can see, by utilizing native Episerver and ASP.Net tools, you can easily create and export reports of Episerver Commerce data . By specifying roles and authorizing administrators, you are able to control who has the ability to download this export. If you have questions about the code or feedback, please leave a comment below. I'd enjoy chatting with you.