I am querying data from a database, and presenting the results in a separate <p:dataTable/> for each result. I am using an <p:accordionPanel> with multiple <p:tab/> to hold this list of results. Each result is under a separate tab, and uses a <p:dataTable/> to display its values. I have customized the tab title using <f:facet/>, and each title contains a <p:commandButton/> with a <p:dataExporter/> component to help me export(in xls format) the <p:dataTable/> in the associated <p:tab/>. In each <p:dataExporter/> component I have targeted the <p:dataTable/> contained in that associated tab whose title it is situated in. When I try to export any one <p:dataTable/> in xls format using the <p:dataExporter> in its tab, it creates a worksheet for every one of the <p:dataTable/> inside the <p:accordionPanel/> instead of just its targeted <p:dataTable/>.
The <p:dataExporter/> component in primefaces utilizes the Apache POI library for exporting a <p:dataTable /> into different formats. I am trying to debug an issue with the xls format. It seems that the expected behavior is that each <p:dataExporter/> component when triggered should create a separate HSSFWorkbook with a HSSFSheet representing the targeted <p:dataTable/>. Here is what is happening,
- When I try to look at the state of the HSSFWorkbook in the postprocessor for the <p:dataExporter/> through the object implicitly passed through in the server side function, I noticed that the workbook contains multiple sheets, one for each <p:dataTable/> in the <p:accordionPanel/>
- The name of each sheet in the downloaded xls file is equal to the id of the <p:dataTable/> in the
- facelets file (not the client id which has all information of the accordionPanel and the tab prepended to it, to make the <p:dataTable/> unique) plus an index 1,2,3 denoting the tab placement.
- When I take a look at the actual DataTableExcelExporter class in primefaces responsible for exporting the <p:dataTable/>, I noticed that the doExport function that creates those sheets is called more than once, one for each <p:dataTable/> instead of just the <p:dataTable/> that was targeted by the <p:dataExporter/> component.
- Now I can do things to manipulate the HSSFSheets in the postprocessor server side callback, and remove the unwanted sheets, but I do not think this is the right way to go. Plus I am also using a csv exporter, and the result in that exporter is that information for every <p:dataTable/> gets mushed up in a single file.
- I looked up the error chain when I was getting Sheet naming conflicts for results which have the same name from the query, and it seems that the <p:accordionPanel/> and <p:tab/> component broadcasts any event (like click) to its surrounding, and inner components, so was thinking if this might be the root cause of the problem.
Facelets
<p:accordionPanel class="search-results-panel" id="searchResultsPanel" rendered="true" multiple="true" value="#{deepBean.searchResults}" var="searchResult">
<p:tab closable="true" id="tab#{deepBean.searchResults.indexOf(searchResult)}">
<f:facet name="title">
<p:outputPanel class="search-results-tab-grid">
<p:outputLabel value="#{searchResult.searchName}"
style="margin-right: 20px;"
class="search-results-tab-name"/>
<p:outputPanel class="search-results-tab-num">
<p:outputLabel value="#{searchResult.data.size()}" class="number-records" style="font-size: 1.5em; padding-right: 10px;"/>
<p:outputLabel value=" Records"/>
</p:outputPanel>
<p:outputPanel class="search-results-tab-exports">
<p:commandButton value="xls" process="@this">
<f:setPropertyActionListener value="#{deepBean.searchResults.indexOf(searchResult)}" target="#{deepBean.searchResultToExport}"/>
<p:dataExporter type="xls" target="#{deepBean.getExportDatatableId(searchResult)}"
fileName="#{searchResult.searchName}-results"
postProcessor="#{deepBean.preProcessDoc}"/>
</p:commandButton>
<p:commandButton value="csv" process="@this">
<p:dataExporter type="csv" target="#{deepBean.getExportDatatableId(searchResult)}"
fileName="#{searchResult.searchName}-results" />
</p:commandButton>
<p:commandButton value="pdf" process="@this">
<p:dataExporter type="pdf" target="#{deepBean.getExportDatatableId(searchResult)}"
fileName="#{searchResult.searchName}-results" />
</p:commandButton>
<p:commandButton value="txt" process="@this">
<p:dataExporter type="txt" target="#{deepBean.getExportDatatableId(searchResult)}"
fileName="#{searchResult.searchName}-results" />
</p:commandButton>
</p:outputPanel>
</p:outputPanel>
</f:facet>
<p:dataTable value="#{searchResult.data}"
rowIndexVar="rowIdx"
emptyMessage="Run a Search to Populate this"
scrollRows="10"
var="resultsMap"
scrollable="true"
scrollHeight="400"
style="overflow-x: auto"
>
<f:facet name="header">
<h:outputText value="#{searchResult.searchName}-results"/>
</f:facet>
<p:columns value="#{searchResult.resultColumns}"
var="columnName"
headerText="#{columnName}"
width="auto"
style="white-space: nowrap; text-align: center;"
filterable="true"
filterMatchMode="contains"
sortable="true"
>
<f:facet name="filter">
</f:facet>
<h:outputText value="#{resultsMap.get(columnName)}"/>
</p:columns>
<f:facet name="footer">
<p:outputLabel value="Returned #{searchResult.data.size()} results"/>
</f:facet>
</p:dataTable>
</p:tab>
</p:accordionPanel>
Primefaces DataTableExcelExport doExport function
@Override
public void doExport(FacesContext context, DataTable table, ExportConfiguration exportConfiguration, int index) throws IOException {
String sheetName = getSheetName(context, table);
if (sheetName == null) {
sheetName = table.getId() + (index + 1);
}
sheetName = WorkbookUtil.createSafeSheetName(sheetName);
if ("empty".equals(sheetName) || "null".equals(sheetName)) {
sheetName = "Sheet (" + (index + 1) + ")";
}
ExcelOptions options = (ExcelOptions) exportConfiguration.getOptions();
if (options == null) {
stronglyTypedCells = true;
}
else {
stronglyTypedCells = options.isStronglyTypedCells();
}
Sheet sheet = createSheet(wb, sheetName, options);
applyOptions(wb, table, sheet, options);
exportTable(context, table, sheet, exportConfiguration);
if (options == null || options.isAutoSizeColumn()) {
short colIndex = 0;
for (UIColumn col : table.getColumns()) {
if (col instanceof DynamicColumn) {
((DynamicColumn) col).applyStatelessModel();
}
if (col.isRendered() && col.isExportable()) {
sheet.autoSizeColumn(colIndex);
colIndex++;
}
}
}
}
Error
SEVERE: The workbook already contains a sheet named 'test-search-results'
java.lang.IllegalArgumentException: The workbook already contains a sheet named 'test-search-results'
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createSheet(HSSFWorkbook.java:939)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createSheet(HSSFWorkbook.java:124)
at org.primefaces.component.datatable.export.DataTableExcelExporter.createSheet(DataTableExcelExporter.java:394)
at org.primefaces.component.datatable.export.DataTableExcelExporter.doExport(DataTableExcelExporter.java:93)
at org.primefaces.component.datatable.export.DataTableExporter$DataTableVisitCallBack.visit(DataTableExporter.java:365)
at com.sun.faces.component.visit.FullVisitContext.invokeVisitCallback(FullVisitContext.java:127)
at org.primefaces.component.api.UIData.visitTree(UIData.java:691)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1468)
at org.primefaces.component.api.UITabPanel.visitTree(UITabPanel.java:1006)
at org.primefaces.component.datatable.export.DataTableExporter$DataTableVisitCallBack.lambda$invoke$0(DataTableExporter.java:384)
at org.primefaces.util.ComponentUtils.invokeOnClosestIteratorParent(ComponentUtils.java:530)
at org.primefaces.component.datatable.export.DataTableExporter$DataTableVisitCallBack.invoke(DataTableExporter.java:382)
at org.primefaces.component.datatable.export.DataTableExporter.export(DataTableExporter.java:326)
at org.primefaces.component.export.DataExporter.processAction(DataExporter.java:165)
at javax.faces.event.ActionEvent.processListener(ActionEvent.java:72)
at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:490)
at javax.faces.component.UICommand.broadcast(UICommand.java:211)
at org.primefaces.component.api.UITabPanel.broadcast(UITabPanel.java:1197)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:847)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1396)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:58)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:76)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:177)
at javax.faces.webapp.FacesServlet.executeLifecyle(FacesServlet.java:707)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:451)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1637)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:331)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:211)
at org.glassfish.tyrus.servlet.TyrusServletFilter.doFilter(TyrusServletFilter.java:282)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:253)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:211)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:257)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:757)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:577)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:158)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:371)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:238)
at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:520)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:217)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:182)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:156)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:218)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:95)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:260)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:177)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:109)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:88)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:53)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:524)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:89)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:94)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:33)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:114)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:569)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:549)
at java.base/java.lang.Thread.run(Thread.java:834)
