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.
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.
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 -
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).
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 -
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 -
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 -
→ 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 -
whereAfter creating an overlay at - /apps/cq/core/content/nav, create new node reports under nav with the following properties -
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 -
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 -
Step #4 - Code the component
- In the HTML file of your component, paste the following code -
- Now, create the required clientlib folder with the following properties -
- Create required script.js as follows -
- Create required style.css for styling the form as follows -
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 -
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