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.
!!! That’s why you the guru and I’m the student. Thank you Fredrik.
Great that you like my series. I have more posts in this series soon. If there is something you like me to cover in future posts, let me know.
Thanks Fredrik!
Hi Fredrik,
I am beginner for xpages and looking for export to excel functionality. I am not finding “Moving on with Excel Export part 1 : XPages Export”.
Please guide me through
Thanks in advance
Hi
First part can be found here
https://www.xpagedeveloper.com/2015/if-your-user-want-reports-in-excel-you-should-read-this
Hi Fredrik,
I went thru tutorial 1-3. Very nice. But the fourth is giving me some problem. When i hit “Export with Key” i get the following error:
POI 4 XPages -> ERROR
————————————————————–
Error : Error during Workbookgeneration
POI LIB : 1.3.1.201703140726
StackTrace:
java.lang.NullPointerException
at org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheet(XSSFWorkbook.java:1007)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheet(XSSFWorkbook.java:109)
at biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.processSpreadSheet(WorkbookProcessor.java:133)
at biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.processWorkbook(WorkbookProcessor.java:118)
at biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.generateNewFile(WorkbookProcessor.java:73)
at biz.webgate.dominoext.poi.component.containers.UIWorkbook.processAjaxRequest(UIWorkbook.java:177)
at biz.webgate.dominoext.poi.component.actions.WorkbookGenerationServerAction.invoke(WorkbookGenerationServerAction.java:48)
at com.ibm.xsp.actions.ActionGroup.invoke(ActionGroup.java:135)
at com.ibm.xsp.application.ActionListenerImpl.processAction(ActionListenerImpl.java:60)
at javax.faces.component.UICommand.broadcast(UICommand.java:324)
at com.ibm.xsp.component.UIEventHandler.broadcast(UIEventHandler.java:366)
at com.ibm.xsp.component.UIDataPanelBase.broadcast(UIDataPanelBase.java:400)
at com.ibm.xsp.component.UIDataPanelBase.broadcast(UIDataPanelBase.java:400)
at com.ibm.xsp.component.UIViewRootEx.broadcast(UIViewRootEx.java:1535)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:307)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:428)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:94)
at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:210)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:96)
at com.ibm.xsp.controller.FacesControllerImpl.execute(FacesControllerImpl.java:256)
at com.ibm.xsp.webapp.FacesServlet.serviceView(FacesServlet.java:227)
at com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:157)
at com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:159)
at com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:138)
at com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103)
at com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:588)
at com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1335)
at com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:865)
at com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:808)
at com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:577)
at com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1319)
at com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:662)
at com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:482)
at com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:357)
at com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:313)
at com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272)
Any suggestion.
Thanks in advance.
Perhaps you have missed adding the sheet name and giving the sheet the same name?