In the previous parts (Part 1Part 2Part 3Part 4) we covered plain export, styling your exports and doing selective exports. Today I want to show you how to create more advanced and flexible way to get your data using a java object that we use for the export.
We start by creating 2 java classes one for the export and one of the Export Data
Then in each class add implements Serializable and the private variable on the row below. This is so the java VM can swap the data out disk if needed.
We will start by creating our data class, very simple. Check what type of data you want to export in each column, in my case I want to export
And then we use the generate setters/getters function in DDE by right clicking in the source window
And select all variables you want to autogenerate setter and getters for an click on OK
Time to create the getData function in the Export class the important thing here is that we return a a java object of the type List.
I also create the function as static because I will only run this function and return the data once. This is the code for the export class. The important thing to point out is
The I create the Exportdata object on each row and store it in the RowData variable
And when I have populated it I add it to the Data ArrayList Object the is returned from the function at the end.
In my Excel export component in All properties datarowexport and I select to create a wgpoi:ListObjectDataSource.
And click on the buildValues property to add some source
And we add this simple code to get the data from the Java objects returned you can expand you getData class and sending in filter parameters from the XPage as we did in Part 4 of this series but in this case the filtering will be done in java.
But wait, you might say how do we populate the column data. That’s the great part of the implementation. You write the name of you variable in you data class and the getter will do the rest. so in this case it will call getName from your row class that is returned.
With this new way of exporting you can combine the data from different sources not only one as I’ve shown in the past parts of this series of posts and giving even better exports to you users.
Integrating a better export into your application will make your users happier
The recent parts of this series has shown you how to create excel exports from a view and also changing the layout of the reports with headers and logos. This post will show you how to filter the data in your excel reports both using keys and fulltext searches. Let’s get started.
To get some filtered export we add an combobox into our XPage and in this case to show both a Export with a key and search we have two buttons for the different export options.
The code behind the combobox is a standard lookup fro the value data
And storing the information within a viewScope variable ExportData
The setup behind the buttons is the same as we done in the previous posts but we add a scriptblock before doing the actual export
If we look at the simple formula for a key based export.
We clear the ExportSearch viewScope var I’ll explain this later and assign the ExportData var into the ExportKey var. The view we are working against is an ordinary view with a categorized first column.
What you need is assigning the key param with the viewScope.ExportKey. In this case I’ve also assigned the search param. But in an ordinary case you would probably assign only one of them. Depending on if you want to get all documents by category or by Fulltext search.
The code behind the search export button is this
We place a standard FT search formula inside the ExportSearch viewScope variable. Selecting all documents that the field Region has the value stored in the ExportData viewScope variable.
So exporting to excel with user customized data has never been easier.
Apache POI for XPages has more options up it sleave so this series will continue so stay tuned in.
Making you report look a bit nicer with some formatting.
Excel reporting from views can be so dull and boring the XPages for POI can give you more options in this matter and help you create some design to your report.
1. Customizing your template
Because POI is using a copy of you template you can customize your temple with logos and predefined information.
We need to import this new template and change the startRow and template name
In this case we start at row 12 below the logo and we assign the new template with the logo that we created. Note that the data will be placed on the row below the one you specified, don’t know why but good to know.
And the Export will be a combined with the logo from the template and the data from the Domino view.
But we want more
2. Setting up a header for you data
This can be done in two ways 1 directly in your template but that isn’t good in if you want to add a column that you need to change the template or if you want to use the template for multiple exports.
So what we do is that we add columns to our Export above the data, we need 4 columns for this example.
We click on add on cellValues and add 4 items of cellValue and as you can see we could also add data to Excel bookmarks. Could be handy if you have a multi sheet excel file.
We specify the columnNumber, rowNumber and because we know the header value we write it but we could actually compute the value also. But a header in plain text is quite dull so we click on cellStyle, scroll down and set the property to get bold on the font and a bigger size.
Your Excel export with a custom logo and a styled header is now ready. There is lots of stuff to cover with POI and application integration so this series will continue, the more I dig into POI for XPages the more I love it.
Part 1: https://www.xpagedeveloper.com/2015/if-your-user-want-reports-in-excel-you-should-read-this
Part 2: https://www.xpagedeveloper.com/2015/moving-on-with-excel-export-part-2
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.