Henrik Fransas

Mar 19, 2016

13762

(4 votes)

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!!

Mar 19, 2016

Comments

valdis Mar 29, 2016 12:01 AM

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 ;)

Vincent Jul 25, 2016 12:37 PM

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

Adrian Chandler Oct 19, 2016 12:43 PM

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?

Henrik Fransas Oct 19, 2016 01:01 PM

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

Adrian Chandler Oct 19, 2016 01:25 PM

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?

Henrik Fransas Oct 19, 2016 02:39 PM

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

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

Adrian Chandler Oct 19, 2016 06:33 PM

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

and

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

Adrian Chandler Oct 19, 2016 06:46 PM

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

Henrik Fransas Oct 19, 2016 07:03 PM

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

Jonas Carlbaum Nov 1, 2016 03:40 PM

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 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?

Henrik Fransas Nov 10, 2016 07:50 PM

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

Henrik Fransas Nov 13, 2016 08:31 PM

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

Jonas Carlbaum Feb 13, 2018 11:35 PM

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.

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!?

Kristoffer Lindén Dec 22, 2020 10:31 AM

Hi, just found this when I was looking for a content picker in a MVC report. Did you guys solve this?

Thanks!

/Kristoffer

Please login to comment.