Hey everyone, sometimes the client requirement is to capture a certain report with specific data and export in a certain format.
In this post, we will see how can we create custom reports and export them to an excel file as and when needed. After going through this post, we will get an understanding how
Sling Resource Merger helps us to create a custom icon on the AEM start screen and how can we write and download an excel file with the required data.
Goal
The goal of this article is to create a utility that will fetch the list of the pages in our /content folder where a specific component is used. The specific use case of such a utility is for the auditing purpose where we may need a list of the components that are deprecated or going to be.
The list of the components will be exported in an excel file that will have two columns - the path of the page and the number of times a component is used on that page. (You can add more columns as per your need).
Design
We are going to create following to fulfil this task -
- An icon on the AEM start page called "Reports" (or "Custom Reports", maybe)
 |
| Custom Icon |
- After clicking on this icon, we will get a form where we can fill the details of the report
 |
| Reporting form |
Here, you can fill in the following details
→ Report name (mandatory) - the name of the report
→ Search path - Path under which we want to search a component in the page. For e.g., /content/we-retail
→ Component path (mandatory) - sling:resourceType value of the component or the path of the component in apps. For e.g. my-project/components/content/text
→ Only include activated pages - if checked, the search will happen only in the pages which are activated.
→ Fetch results - on clicking, an excel file with the details will be downloaded.
Development
So, without further ado, its now time to get our hands dirty with some code. Below are the steps which define how are we going to develop this utility.
Step #1 - Creating a custom icon on the start page
To create a custom icon on the start page, we will be using the
Sling Resource Merger concept which provides services to access and merge resources. All the navigation items that are present on the start page live here -
/libs/cq/core/content/nav. Therefore, to add a new option on the start page, we need to overlay this path in our /apps folder.
After creating an overlay at -
/apps/cq/core/content/nav, create new node
reports under
nav with the following properties -
<reports
jcr:primaryType="nt:unstructured"
jcr:title="Reports"
href="/reports.html"
consoleId="reports"
icon="report"
order="{Long}200"
/>
where
href - URL of the console. For e.g. http://localhost:4502/reports.html.
id - This is the ID by which we can tie our console to the navigational item.
icon - Coral UI icon.
order - Relative order of the item.
Step #2 - Design the console
Our console will be a page on which we will render a component which will define the layout of our form. To do that, in our project structure, create a node called reports with the following properties -
<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:sling="http://sling.apache.org/jcr/sling/1.0" xmlns:cq="http://www.day.com/jcr/cq/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0"
jcr:primaryType="cq:Page">
<jcr:content
jcr:mixinTypes="[sling:VanityPath]"
jcr:primaryType="nt:unstructured"
jcr:title="Component Report"
sling:redirect="{Boolean}false"
sling:resourceType="granite/ui/components/shell/page"
sling:vanityOrder="{Long}301"
sling:vanityPath="/reports">
<head jcr:primaryType="nt:unstructured">
<viewport
jcr:primaryType="nt:unstructured"
sling:resourceType="granite/ui/components/coral/foundation/admin/page/viewport"/>
<breadcrumbs
jcr:primaryType="nt:unstructured"
sling:resourceType="wcm/commons/ui/shell/datasources/breadcrumbs"
path="${requestPathInfo.suffix}"
rootPath="/reports"
rootTitle="Component Report"/>
<clientlibs
jcr:primaryType="nt:unstructured"
sling:resourceType="granite/ui/components/foundation/includeclientlibs"
categories="[coralui3,granite.ui.coral.foundation,reporting]"/>
</head>
<content
jcr:primaryType="nt:unstructured"
sling:resourceType="granite/ui/components/coral/foundation/container">
<items jcr:primaryType="nt:unstructured">
<reportContent
jcr:primaryType="nt:unstructured"
sling:resourceType="Reporting/components/content/report/componentReport"/>
</items>
</content>
</jcr:content>
</jcr:root>
This is basically a node of type
cq:Page which will open when we will click on the
Reports icon from the start page.
This node can be created anywhere in your project structure. I have created this under
/apps/my-project/reports.
Some key concepts here are -
- Clientlibs - The clientlibs declaration in the head allows us to specify any clientlib categories we want to automatically include as part of our console.
- I'm referring an HTL component using sling:resourceType just as you would anywhere else for dynamically including a component but keep in mind everything up there is leveraging Granite as a thin client to set everything up.
- I'm using the generic "container" layout as so to include HTL component and takes the whole are in the console.
Step #3 - Creating the layout component
Now, it's time to create an HTL component which will be tied to our console. The properties of the component are below -
<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:sling="http://sling.apache.org/jcr/sling/1.0" xmlns:cq="http://www.day.com/jcr/cq/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0"
jcr:description="This component creates a simple form to fetch the list of components used on a page "
jcr:primaryType="cq:Component"
jcr:title="Component Report"
sling:resourceSuperType="wcm/foundation/components/parbase"
componentGroup="Reporting"/>
Step #4 - Code the component
- In the HTML file of your component, paste the following code -
<sly
data-sly-use.clientLib="/libs/granite/sightly/templates/clientlib.html"
data-sly-call="${clientLib.all @ categories='reporting.componentreport'}" data-sly-unwrap />
<form action="/bin/reports/component" method="post">
<h1>Component Report</h1>
<fieldset>
<legend><span class="number">R</span> Basic report parameters</legend>
<label>Report Name:*</label>
<input type="text" id="reportName" name="reportName" required>
<label>Search Path:</label>
<input type="text" id="searchPath" name="searchPath">
<label>Component Path:*</label>
<input type="text" id="componentPath" name="componentPath" required>
<label>Only include activated pages</label>
<input type="checkbox" id="activated" value="true" name="isActivated">
</fieldset>
<button type="submit" onClick="window.location.reload();">Fetch results</button>
</form>
This is a simple HTML form in which our form action, we are referring to our backend servlet (which we will create in a later step).
- Now, create the required clientlib folder with the following properties -
<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:cq="http://www.day.com/jcr/cq/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0"
jcr:primaryType="cq:ClientLibraryFolder"
categories="reporting.componentreport"
dependencies="[cq.jquery,cq.jquery.ui]"/>
- Create required script.js as follows -
$(function() {
var components = [ "core/wcm/components/text/v2/text" ];
$("#componentPath").autocomplete({
source : components
});
});
Here,
components is an array which we will contain the relative paths of the components for which we want to search for in the pages. In the above array, I have put only one component but you can have as many components as you want as per your requirement.
- Create required style.css for styling the form as follows -
*, *:before, *:after {
-moz-box-sizing: border-box;
-webkit-box-sizing: border-box;
box-sizing: border-box;
}
body {
font-family: 'Nunito', sans-serif;
color: #834047;
}
form {
max-width: 300px;
margin: 10px auto;
padding: 10px 20px;
background: #1f5ed64d;
border-radius: 8px;
}
h1 {
margin: 0 0 30px 0;
text-align: center;
}
input[type="text"] {
background: rgba(255, 255, 255, 0.1);
border: none;
font-size: 16px;
height: auto;
margin: 0;
outline: 0;
padding: 15px;
width: 100%;
background-color: #edf3f0;
color: #000000;
box-shadow: 0 1px 0 rgba(0, 0, 0, 0.03) inset;
margin-bottom: 30px;
}
input[type="checkbox"] {
width: 30px; /*Desired width*/
height: 30px; /*Desired height*/
}
select {
padding: 6px;
height: 32px;
border-radius: 2px;
}
button {
padding: 19px 39px 18px 39px;
color: #FFF;
background-color: #000000;
font-size: 18px;
text-align: center;
font-style: normal;
border-radius: 5px;
width: 100%;
border-width: 1px 1px 3px;
box-shadow: 0 -1px 0 rgba(255, 255, 255, 0.1) inset;
margin-bottom: 10px;
}
fieldset {
margin-bottom: 30px;
border: none;
}
legend {
font-size: 1.4em;
margin-bottom: 10px;
}
label {
display: block;
margin-bottom: 8px;
}
label.light {
font-weight: 300;
display: inline;
}
.number {
background-color: #5fcf80;
color: #fff;
height: 30px;
width: 30px;
display: inline-block;
font-size: 0.8em;
margin-right: 4px;
line-height: 30px;
text-align: center;
text-shadow: 0 1px 0 rgba(255, 255, 255, 0.2);
border-radius: 100%;
}
@media screen and (min-width: 480px) {
form {
max-width: 480px;
}
}
Thus, we are done with the front end part of our reporting tool. Now, its time to create the backend part.
Step #5 - Create the backend Sling servlet
Now, we are going to create a backend servlet that will contain the code for reading values and it will then run a query to find out all the pages which use the specified component. After it's done finding the list of pages, it will write the data into an excel file and will download it.
Below is the code for the servlet -
package org.redquark.aem.reporting.core.servlets;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.jcr.query.Row;
import javax.jcr.query.RowIterator;
import javax.servlet.Servlet;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.servlets.HttpConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.service.component.annotations.Component;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @author Anirudh Sharma
*
*/
@Component(immediate = true, service = Servlet.class, property = { "sling.servlet.methods=" + HttpConstants.METHOD_POST,
"sling.servlet.paths=" + "/bin/reports/component" })
public class ComponentReportServlet extends SlingAllMethodsServlet {
// Generates serial version UID
private static final long serialVersionUID = -7024907417541933763L;
// Logger
private final Logger log = LoggerFactory.getLogger(this.getClass());
@Override
protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) {
log.info("Invoking component report servlet...");
try {
String reportName = request.getParameter("reportName");
String searchPath = request.getParameter("searchPath");
String componentPath = request.getParameter("componentPath");
boolean isActivated = Boolean.parseBoolean(request.getParameter("isActivated"));
if (searchPath.isEmpty() || searchPath == null) {
searchPath = "/content";
}
String queryString = "SELECT * FROM [cq:PageContent] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent) WHERE ISDESCENDANTNODE(parent,["
+ searchPath + "]) AND child.[sling:resourceType]='" + componentPath + "'";
if (isActivated) {
queryString += " AND parent.[cq:lastReplicationAction] = 'Activate'";
}
// Getting the ResourceResolver from the current request
ResourceResolver resourceResolver = request.getResourceResolver();
// Getting the session instance by adapting ResourceResolver
Session session = resourceResolver.adaptTo(Session.class);
QueryManager queryManager = session.getWorkspace().getQueryManager();
Query query = queryManager.createQuery(queryString, "JCR-SQL2");
QueryResult queryResult = query.execute();
RowIterator rowIterator = queryResult.getRows();
Map<String, String> data = new TreeMap<>();
// data.put("Page path", "Count");
while (rowIterator.hasNext()) {
Row row = rowIterator.nextRow();
String path = StringUtils.substringBefore(row.getPath("child"), "jcr:content");
Integer temp = StringUtils.isNotBlank(data.get(path)) ? Integer.parseInt(data.get(path)) : 0;
data.put(path, (temp == 0) ? String.valueOf(1) : String.valueOf(temp + 1));
}
writeDataToFile(reportName, data, response);
response.getWriter().println(data.toString());
} catch (RepositoryException | IOException e) {
log.error(e.getMessage(), e);
try {
response.getWriter().println(e.getMessage());
} catch (IOException e1) {
log.error(e1.getMessage());
}
}
}
private void writeDataToFile(String reportName, Map<String, String> data, SlingHttpServletResponse response) {
// Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Component data");
Set<String> keyset = data.keySet();
org.apache.poi.ss.usermodel.Row row = sheet.createRow(0);
Cell pageTitle = row.createCell(0);
pageTitle.setCellValue("Page path");
Cell countTitle = row.createCell(1);
countTitle.setCellValue("Number of times component appear on a page");
int rowNum = 1;
for (String key : keyset) {
row = sheet.createRow(rowNum++);
Cell pagePath = row.createCell(0);
pagePath.setCellValue(key);
Cell count = row.createCell(1);
count.setCellValue(data.get(key));
}
try {
response.setContentType("application/ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=" + reportName + ".xlsx");
// Write the workbook in file system
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Here, in this servlet, we are reading the values from the form data and are passing them into an SQL2 query which will be as follows -
SELECT * FROM [cq:PageContent] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent)
WHERE ISDESCENDANTNODE(parent,[CONTENT_PATH]) AND child.[sling:resourceType]='COMPONENT_PATH'
AND parent.[cq:lastReplicationAction] = 'Activate'
Here, CONTENT_PATH is the path of pages under which you want to search, for e.g., /content/we-retail while COMPONENT_PATH is the resourceType property of the component which we want to search. You can modify this query as per your need.
After getting the list of pages using the
QueryManager API, we are using Apache POI to write the data into the excel file, which then will be downloaded. IT will look something like this -
 |
| Sample excel report |
Conclusion
Pheww! This is a long post 😫 and required a lot of code and concepts but I am sure you would have enjoyed it.
If you wish to see the complete code of this project and contribute towards the same with your suggestions and code, then you can find it on my
GitHub. Please star it if you find it useful.
I hope this will help you in your projects and speed up your development.
I would love to hear your thoughts on this post and would like to have suggestions from you to make this post better.
Happy Learning 😊
Have you every done anything with emailed automated reports with AEM?
ReplyDelete