Views: 3431
Number of votes: 2
Average rating:

Create custom report in MVC with Excel Export

Lately I have seen a couple of questions about how to make a excel export from a custom report and also if it is possible to make a custom report in MVC.

I had never done any custom reports in MVC so I decided to try this things out and it showed that is was pretty simple.

For the excel-export function I am using the external packaged EPPlus.

I decided to make a very simple report on the pages that are exist on a website, feel free to get inspired from it.

First I created a Controller that looks like this:

namespace AlloyExample.Controllers
{
   [EPiServer.PlugIn.GuiPlugIn(
        Area = EPiServer.PlugIn.PlugInArea.ReportMenu, 
        Url = "~/existingpagesreport",
        Category = "Existing Pages",
        DisplayName = "Pages By PageType")]
    [Authorize(Roles = "Administrators, WebAdmins")]
    public class ExistingPagesReportController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
    }
  
}

Here I define that this controller also is a GuiPlugin that should be showed in the reportmenu and I give it a url that is /existingpagesreport. I also protect it so you have to be part of the administrator group to be able to use it.

For this url to work, we need to add a route to it so I add this in global.asax.cs

protected override void RegisterRoutes(RouteCollection routes)
        {
            base.RegisterRoutes(routes);
            routes.MapRoute(
                "ExistingPagesReport",
                "existingpagesreport/{action}",
                new { controller = "ExistingPagesReport", action = "Index" });
        }

After that I created a simple view and just tried out that it showed up in the report center.

To make it a little more interesting I first added a ViewModel with the properties I needed. It looks like this:

public class ExistingPagesReportViewModel
    {
        public IEnumerable<PageType> PageTypes { get; set; }
        public PageDataCollection Pages { get; set; }
        public string SelectedPageType { get; set; }
    }

Then I created a helper class to make my request against Episerver and that looks like this:

public static class ExistingPagesHelper
    {
        public static IEnumerable<PageType> GetAllPageTypes()
        {
            var contentTypeRepository = ServiceLocator.Current.GetInstance<IContentTypeRepository>();
            return contentTypeRepository.List().OfType<PageType>();
        }

        public static void SetPagesForPageTypeName(ExistingPagesReportViewModel model)
        {
            var criterias = new PropertyCriteriaCollection();

            var criteria = new PropertyCriteria();
            criteria.Condition = CompareCondition.Equal;
            criteria.Name = "PageTypeID";
            criteria.Type = PropertyDataType.PageType;
            criteria.Value = model.SelectedPageType;
            criteria.Required = true;

            criterias.Add(criteria);

            var pages = DataFactory.Instance.FindPagesWithCriteria(ContentReference.RootPage, criterias);

            model.Pages = pages;
        }
    }

After that I updated my index action so it created an instance of the view model and assigned all pagetypes to it. So now it looks like this.

public ActionResult Index()
        {
           var model = new ExistingPagesReportViewModel { PageTypes = ExistingPagesHelper.GetAllPageTypes() };

            return View(model);
        }

Now I created my view and since I wanted it to feel the same way as the built in reports I added Epi’s css and javascript to it. The complete view looks like this:

@model ExistingPagesReportViewModel

@using EPiServer.DataAbstraction
@using EPiServer.Framework.Web.Resources

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <title>@ViewBag.Title</title>
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
    <!-- Shell -->
    @Html.Raw(ClientResources.RenderResources("ShellCore"))
    <!-- LightTheme -->
    @Html.Raw(ClientResources.RenderResources("ShellCoreLightTheme"))

    <link href="/EPiServer/CMS/App_Themes/Default/Styles/system.css" type="text/css" rel="stylesheet">
    <link href="/EPiServer/CMS/App_Themes/Default/Styles/ToolButton.css" type="text/css" rel="stylesheet">
</head>
<body>
    @Html.Raw(Html.ShellInitializationScript())

    <div class="epi-contentContainer epi-padding">
        <div class="epi-contentArea">
            <div class="EP-systemImage" style="background-image: url('/App_Themes/Default/Images/ReportCenter/PublishedPages.gif');">
                <h1 class="EP-prefix">
                    Existing Pages
                </h1>
                <p class="EP-systemInfo">
                    This report displays pages that exists on the site.
                </p>
            </div>
            <div id="FullRegion_ValidationSummary" class="EP-validationSummary" style="color: Black; display: none;">

            </div>
        </div>
        @using (Html.BeginForm("ListPages", "ExistingPagesReport", FormMethod.Post))
        {
            <script src="/Util/javascript/episerverscriptmanager.js" type="text/javascript"></script>
            <script src="/EPiServer/CMS/javascript/system.js" type="text/javascript"></script>
            <script src="/EPiServer/CMS/javascript/dialog.js" type="text/javascript"></script>
            <script src="/EPiServer/CMS/javascript/system.aspx" type="text/javascript"></script>

            <input type="hidden" id="doExport" name="doExport" value="False">
            <div class="epi-formArea">
                <fieldset>
                    <legend>
                        Report Criteria
                    </legend>
                    <div class="epi-size10">
                        <label for="pageTypes">Select PageType</label>
                        <select name="pageType" id="pageType">
                            @foreach (var type in Model.PageTypes.Where(w => w.ID != 1).OrderBy(o => o.Name))
                            {
                                <option value="@type.ID" @(type.ID.ToString() == Model.SelectedPageType ? "selected=selected" : "") >@type.Name</option>
                            }
                        </select>
                    </div>
                </fieldset>

                <div class="epitoolbuttonrow">
                    <span class="epi-cmsButton"><input class="epi-cmsButton-text epi-cmsButton-tools epi-cmsButton-Report" type="submit" name="showReport" id="showReport" value="Show Report" onmouseover="EPi.ToolButton.MouseDownHandler(this)" onmouseout="EPi.ToolButton.ResetMouseDownHandler(this)" /></span>
                    <span class="epi-cmsButton"><input class="epi-cmsButton-text epi-cmsButton-tools epi-cmsButton-Report" type="submit" name="exportReport" id="exportReport" value="Export Report" onmouseover="EPi.ToolButton.MouseDownHandler(this)" onmouseout="EPi.ToolButton.ResetMouseDownHandler(this)" /></span>
                </div>

            </div>
        }

        @if (Model.Pages != null && Model.Pages.Count > 0)
        {
            <div class="epi-floatLeft epi-marginVertical-small">Number of Hits: @Model.Pages.Count</div>
            <div class="epi-contentArea epi-clear">
                <div>
                    <table class="epi-default epi-default-legacy" cellspacing="0" id="FullRegion_MainRegion_ReportView" style="border-style: None; width: 100%; border-collapse: collapse;">
                        <tr>
                            <th scope="col">Page Id</th>
                            <th scope="col">Page Name</th>
                            <th scope="col">Page Url</th>
                            <th scope="col">Published Date</th>
                        </tr>
                        @foreach (var page in Model.Pages)
                        {
                            <tr>
                                <td style="width: 27%;">@page.ContentLink.ID</td>
                                <td>@page.PageName</td>
                                <td>@Url.ContentUrl(page.ContentLink)</td>
                                <td>@(page.StartPublish.HasValue ? page.StartPublish.Value.ToString("yyyy-MM-dd HH:mm") : "Not published")</td>
                            </tr>
                        }
                    </table>
                </div>
            </div>

        }

    </div>
    <script type="text/javascript">
        document.getElementById("exportReport").onclick = function () {
            document.getElementById("doExport").value = "True";
        };
        document.getElementById("showReport").onclick = function () {
            document.getElementById("doExport").value = "False";
        };
    </script>
</body>
</html>

As you can see I have two submit buttons but only one form actions and because of that I have connected javascript function to the click event on the export and search submit button that updates a hidden value telling the controller to do an export of the data or not.

The action in the controller that handles this looks like this:

[HttpPost]
        public ActionResult ListPages(FormCollection form)
        {
            var model = new ExistingPagesReportViewModel
            {
                PageTypes = ExistingPagesHelper.GetAllPageTypes(),
                SelectedPageType = form["pageType"]
            };

            ExistingPagesHelper.SetPagesForPageTypeName(model);


            var doExport = false;

            if (bool.TryParse(form["doExport"], out doExport) && doExport && model.Pages != null && model.Pages.Count > 0)
            {
                Export(model.Pages, System.Web.HttpContext.Current.Response);
            }

            return View("Index", model);
        }

As you can see it returns the view if the export values is not true. To do the export I use EPPlus and for this simple export I just created a function in the same class that looks like this:

public void Export(PageDataCollection pagesToExport, HttpResponse response)
        {
            using (var package = new ExcelPackage())
            {
                ExcelWorksheet ws = package.Workbook.Worksheets.Add("pages");

                ws.Cells[1, 1].Value = "PageId";
                ws.Cells[1, 2].Value = "PageName";
                ws.Cells[1, 3].Value = "PageUrl";
                ws.Cells[1, 4].Value = "Published Date";

                ws.Row(1).Style.Font.Bold = true;
                ws.Row(1).Style.Locked = true;

                int row = 2;

                foreach (var page in pagesToExport)
                {
                    ws.Cells[row, 1].Value = page.ContentLink.ID;
                    ws.Cells[row, 2].Value = page.PageName;
                    ws.Cells[row, 3].Value = Url.ContentUrl(page.ContentLink);
                    ws.Cells[row, 4].Value = page.StartPublish.HasValue ? page.StartPublish.Value.ToString("yyyy-MM-dd HH:mm") : "Not published";

                    ++row;
                }

                response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                response.AddHeader("content-disposition", string.Format("attachment; filename=pages{0}.xlsx", DateTime.Now.ToString("yyyyMMdd")));
                response.BinaryWrite(package.GetAsByteArray());
                response.Flush();
                response.End();
            }
        }

This is a simple example on how to make a custom report in MVC with an Excel export function in it. Hope that it will help you get started and make your own great reports!

Happy coding!!

    (By valdis iljuconoks , 29 March 2016 00:01, Permanent link)

    Nice! I used to export Excel via DataSets :)

    https://github.com/Geta/DdsAdmin/blob/master/CMS7/modules/_protected/Geta.DdsAdmin/Admin/DdsAdmin.aspx.cs

    No need to manipulate cell level info. May become handy ;)

    (By code monkey , 25 July 2016 12:37, Permanent link)

    Exact what I am looking for. Thanks for your sharing. 

    I am trying to group controller/views/models/route within one feature folder, how could I load css/js properly? 

    Thanks

    (By Adrian Chandler , 19 October 2016 12:43, Permanent link)

    How do we use dojo templates in MVC reports? For instance to display dates or start page functionality that we see on the standard reports?

    (By Henrik Fransas , 19 October 2016 13:01, Permanent link)

    The reports do not use DOJO, Admin interface are "DOJO-free" ;-)

    (By Adrian Chandler , 19 October 2016 13:25, Permanent link)

    So how do we have episerver dialogs for selecting content references, links, dates etc in MVC? In web forms there are various episerver controls we can use but there is no MVC equivalent?

    (By Henrik Fransas , 19 October 2016 14:39, Permanent link)

    Look how Episerver has done that by looking at their reports.

    You find them here: [ProjectPath]\modules\_protected\CMS\CMS.zip\Report\Reports\....

    (By Adrian Chandler , 19 October 2016 18:33, Permanent link)

    Yes, but those reports are web forms. They use web controls:


    <EPiServer:InputDate style="width: auto;" CssClass="epiinlineinputcontrol" ID="StartDateSelector" runat="server" />
    <asp:Literal runat="server" ID="DateDash" Text="─" />
    <EPiServer:InputDate style="width: auto;" CssClass="epiinlineinputcontrol" ID="EndDateSelector" runat="server" />

    and

    <EPiServer:InputPageReference CssClass="epiinlineinputcontrol" ID="StartPageSelector" runat="server" DisableCurrentPageOption="true"/>

    I've tried look around for something similar in MVC but can't find anything.

    (By Adrian Chandler , 19 October 2016 18:46, Permanent link)

    I tried @Html.PropertyFor and even @Html.DojoEditorFor but these don't work (or won't without some help)

    (By Henrik Fransas , 19 October 2016 19:03, Permanent link)

    That is true.

    Hmm, never thought about it and I think it could be hard since admin part is 100% webforms.

    will try out a couple of things and get back to you

    (By Jonas Carlbaum , 01 November 2016 15:40, Permanent link)

    Any success implementing a content-reference-picker?

    Tried an approach where I would render a InputPageReference to a MvcHtmlString, without any success...

        public static class FormsHelper
        {
            private static Injected<IContentRepository> ContentRepository { get; }
    
            public static MvcHtmlString InputPageReference(this HtmlHelper html, string propertyName)
            {
                var inputPageReference = new InputPageReference(ContentRepository.Service)
                {
                    ID = propertyName,
                    CssClass = "epiinlineinputcontrol",
                    DisableCurrentPageOption = true,
                };
                
                var writer = new HtmlTextWriter(new StringWriter());
                inputPageReference.RenderControl(writer);
    
                return MvcHtmlString.Create(writer.InnerWriter.ToString());
            }
        }

    Guess it won't work to take this approach further! Any other ideas?

    (By Henrik Fransas , 10 November 2016 19:50, Permanent link)

    What do you mean with a picker? Some kind of tree view?

    If it is a tree view then I would use some jquery plugin like https://www.jstree.com/

    I would fill it with the root or start page and then for each click I would do a web API call that are doing a get children and return the names and id for all children

    (By Henrik Fransas , 13 November 2016 20:31, Permanent link)

    Just updated the blog to not use Episerver Find and also fixed a couple of bugs with missing javascript files

    (By Jonas Carlbaum , 13 February 2018 23:35, Permanent link)

    Oh, read your response a little late... A colleague had to ask me how I did solve my issues before I came back to see this post again. wink

    If I remember correctly we had a requirement or a request to select a root node on the reports, so my initial thought was to use one of the EPiServer built-in editors used in different admin tools. Those happens to be WebForms-only, too bad, I refuse to use WebForms, only if I’m being forced against my will...

    I wasn’t that eager to put in any custom stuff that didn’t have the EPiServer look’n’feel. So I guess we skipped the export root selection requirement.

    Still thinking it would be great if we could use those properties/editors in MVC admin tools. Guess we aren’t there yet, even with EPiServer CMS 11!?

  Please login to post a comment