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