Exporting form data to Excel in EPiServer 10

EPiServer allows you to export form data to text-based files such as XML, CSV, and JSON. All  you have to do is to create a class that derives from DataExporterBase class, as described here: http://world.episerver.com/releases/episerver---update-128/

DataExporterBase class has an abstract method called Export which returns a string. But what if you want to export form data to Excel, which is a binary file? We need to have a possibility to return byte[] instead of a string.

To solve this, I've created a new base class:

public abstract class BinaryDataExporterBase : DataExporterBase
{
    public abstract byte[] ExportBinary(DataTable dataTable);
}

And a new data exporter class that derives from BinaryDataExporterBase:

public class ExcelDataExporter : BinaryDataExporterBase
{
    public override string Export(DataTable dataTable)
    {
        throw new NotImplementedException();
    }

    public override string MimeType => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    public override string Name => "Excel";
    public override string Description => "Export form data in Excel format";

    public override string ExportFileExtension => "xlsx";

    public override byte[] ExportBinary(DataTable dataTable)
    {
        using (var workbook = new XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("Sample Sheet");

            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                worksheet.Cell(1, i + 1).Value = dataTable.Columns[i].ColumnName;
            }

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    string text = dataTable.Rows[i][j].ToString();
                    if (string.IsNullOrWhiteSpace(text)) continue;

                    worksheet.Cell(i + 2, j + 1).Value = text;
                }
            }

            byte[] fileContent;
            using (var ms = new MemoryStream())
            {
                workbook.SaveAs(ms);
                ms.Position = 0;
                fileContent = ms.ToArray();
            }

            return fileContent;
        }
    }
}

Here I'm using https://github.com/ClosedXML/ClosedXML to generate the Excel file in memory.

The code that is responsible for generating the file when the user clicks on Export as button is located inside DataExportingController, which can be found in EPiServer.Forms.UI assembly.

All we have to do is to create a custom controller that derives from DataExportingController and override the PrepareExport method:

public class ExtendedDataExportingController : DataExportingController
{
    private readonly IFormRepository _formRepository;
    private readonly IFormDataRepository _formDataRepository;

    public ExtendedDataExportingController(
        IFormDataRepository formDataRepository,
        IFormRepository formRepository)
    {
        _formDataRepository = formDataRepository;
        _formRepository = formRepository;
    }

    public override JsonResult PrepareExport(
        ContentReference contentLink,
        string extension,
        IEnumerable<string> submissionIds,
        DateTime? beginDate,
        DateTime? endDate,
        bool? finalizedOnly)
    {
        var content = contentLink.GetContent();
        if (content == null || !content.HasAccessRightToReadFormData())
        {
            return Json(null);
        }

        var exporterFromExtension = GetExporterFromExtension(extension);
        if (exporterFromExtension == null)
        {
            throw new NotSupportedException("extension is not supported.");
        }

        var formIdentity = new FormIdentity
        {
            Guid = content.ContentGuid,
            Language = (content as ILocale)?.Language.Name
        };

        List<Submission> submissionData;
        if (submissionIds == null)
        {
            submissionData = _formDataRepository
                .GetSubmissionData(
                    formIdentity, beginDate ?? DateTime.MinValue,
                    endDate ?? DateTime.MaxValue,
                    finalizedOnly.HasValue && finalizedOnly.Value).ToList();
        }
        else
        {
            submissionData = _formDataRepository
                .GetSubmissionData(formIdentity, submissionIds.ToArray())
                .ToList();
        }

        if (submissionData.Count == 0)
        {
            return Json(null);
        }

        var table = CreateTable(_formRepository.GetDataFriendlyNameInfos(formIdentity));
        foreach (var submission in submissionData)
        {
            var row = table.NewRow();
            foreach (DataColumn column in table.Columns)
                row[column] = submission.Data.ContainsKey(column.ColumnName)
                    ? submission.Data[column.ColumnName]
                    : null;

            table.Rows.Add(row);
        }

        for (int index = 0; index < table.Columns.Count; ++index)
        {
            if (!string.IsNullOrEmpty(table.Columns[index].Caption))
            {
                table.Columns[index].ColumnName = GetValidColumnName(table.Columns[index].Caption);
            }
        }

        byte[] fileContent;
        var binaryDataExporter = exporterFromExtension as BinaryDataExporterBase;
        if (binaryDataExporter != null)
        {
            fileContent = binaryDataExporter.ExportBinary(table);
        }
        else
        {
            fileContent = Encoding.UTF8.GetPreamble()
                                    .Concat(Encoding.UTF8.GetBytes(exporterFromExtension.Export(table)))
                                    .ToArray();
        }

        string tempFileName = System.IO.Path.GetTempFileName();
        System.IO.File.WriteAllBytes(tempFileName, fileContent);

        return Json(new
        {
            FileName = $"{content.Name}.{extension}",
            TempFilePath = tempFileName,
            ContentType = exporterFromExtension.MimeType
        });
    }
}

Next, we need to configure the IoC so that our ExtendedDataExportingController is used instead of DataExportingController.

This can be done with an initialization module like this:

[ModuleDependency(typeof(ServiceContainerInitialization))]
public class InitModule : IConfigurableModule
{
    public void ConfigureContainer(ServiceConfigurationContext context)
    {
        context.Container.Configure(
                    x =>
                    {
                        x.For<DataExportingController>().Use<ExtendedDataExportingController>();
                    });
    }

    public void Initialize(InitializationEngine context)
    {
    }

    public void Uninitialize(InitializationEngine context)
    {
    }
}

And that's it. If you navigate to Form Submissions, you should be able to see the new menu item.

Exporting Form Data to Excel in EPiServer 10

comments powered by Disqus