Invalid sheet name error exporting datatable to excel

Question

I want to export jsf datatable as excelsheet when I click on "export as excel " link. I am using "Apache poi library" and "PrimeFaces" library to do this task.

I am successfully displaying my datatable using PrimeFaces. When I click on "export as excel" link I am getting exception as

javax.faces.FacesException: sheetName '' is invalid - character count MUST be greater than or equal to 1 and less than or equal to 31.

Please help me to solve my problem.

Thank you.

this my jsf file.

     <p:dataTable id = "dataTableId" var = "pr" value = "#{dashboard.beanList}"  >

        <p:panel header="Export All Data">

        <h:commandLink value="export as excel sheet">

            <p:dataExporter type="xls"  target="dataTableId" fileName="myexcel" />

        </h:commandLink>
        </p:panel>

this is my stacktrace file.

javax.faces.FacesException: sheetName '' is invalid - character count MUST be greater than or equal to 1 and less than or equal to 31
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:85)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:97)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:114)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:334)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
          Caused by: java.lang.IllegalArgumentException: sheetName '' is invalid - character count MUST be greater than or equal to 1 and less than or equal to 31 at org.apache.poi.ss.util.WorkbookUtil.validateSheetName(WorkbookUtil.java:108)
at org.apache.poi.hssf.record.BoundSheetRecord.setSheetname(BoundSheetRecord.java:94)
at org.apache.poi.hssf.model.InternalWorkbook.setSheetName(InternalWorkbook.java:588)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createSheet(HSSFWorkbook.java:798)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createSheet(HSSFWorkbook.java:78)
at org.primefaces.component.export.ExcelExporter.export(ExcelExporter.java:52)
at org.primefaces.component.export.DataExporter.processAction(DataExporter.java:112)
at javax.faces.event.ActionEvent.processListener(ActionEvent.java:84)
at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:773)
at javax.faces.component.UICommand.broadcast(UICommand.java:296)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1255)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:77)
... 22 more

This is my complete Xhtml file.

    <p:dataTable id = "dataTableId" var = "pr" value = "#{dashboard.beanList}"  >

    <f:facet name="header">
<!-- edit button -->    
    <p:commandButton id="toggler1" type="button" value="Customize Columns" style="float:right" icon="ui-icon-calculator" />
    <p:columnToggler datasource="dataTableId" trigger="toggler1" />
</f:facet>


            <p:column>
                <f:facet name="header"><h:selectBooleanCheckbox /> </f:facet>
                <h:selectBooleanCheckbox />
            </p:column>


            <p:column>

                <f:facet name="header"> UPC </f:facet>
                <div class="help-tip">
                    <h:outputLabel value="#{pr.lUpc}" id="l1" />
                </div>
            </p:column>


            <p:column>
                <f:facet name="header"> Cost </f:facet>
                <h:outputLabel value="#{pr.dCost}" />
            </p:column>



            <p:column>
                <f:facet name="header"> SKU </f:facet>
                <h:outputLabel value="#{pr.sSku}" />
            </p:column>


            <p:column>
                <f:facet name="header"> ProductName </f:facet>
                <h:outputLabel value="#{pr.sProductName}" id="l2" />
            </p:column>




            <p:column>

                <p:commandButton id="btn" value="Edit" onclick = "getupc(#{pr.lUpc})" >

                </p:commandButton>
            </p:column>

        </p:dataTable>




    <p:panel header="Export All Data">

    <h:commandLink value="export as excel sheet">

        <p:dataExporter type="xls"  target="dataTableId" fileName="myexcel" />

    </h:commandLink>
    </p:panel>

Answer 1

Try to comment this line: edit button
</f:facet> -->

Answer 2

If you updated your primefaces to 6.0 or more., Then it will check the sheetName of excel. It will check inside f:facet's , if it contains any value, it will print that name in sheet0. If there is no value, sheet will be empty. Then it will return exception.

Answer 3

i had the same problem.

as "Anand" said in the answer before , primefaces search the name into facets.

i had this code:

 <p:dataTable id="xxxxxxx" value="#{bean.xxxxxxxx}" var="row" scrollable="true" scrollHeight="800" style="margin-top:10px; " >
         <f:facet name="header">
             <p:outputPanel style="display:flex;justify-content:space-between; width:100%;">
                        <h:commandLink>
                            <p:graphicImage
                                style="width:21px;height: 21px;margin:2px"
                                name="images/excel.png"/>
                               <p:dataExporter type="xls" target="xxxxxxx" fileName="Detail" postProcessor="#{bean.postProcessXLS}" />
                        </h:commandLink> 
             </p:outputPanel>
            </f:facet>
          
          ...........

and the same exception occurs but adding a label inside facet like this

<p:dataTable id="xxxxxxx" value="#{bean.xxxxxxxx}" var="row" scrollable="true" scrollHeight="800" style="margin-top:10px; " >
         <f:facet name="header">
             <p:outputPanel style="display:flex;justify-content:space-between; width:100%;">
                 <p:outputLabel value="xxxxxxxxx" escape="false" style="line-height: 2; font-size: 13px;"></p:outputLabel>
                        <h:commandLink>
                            <p:graphicImage
                                style="width:21px;height: 21px;margin:2px"
                                name="images/excel.png"/>
                               <p:dataExporter type="xls" target="xxxxxxx" fileName="Detail" postProcessor="#{bean.postProcessXLS}" />
                        </h:commandLink> 
             </p:outputPanel>
            </f:facet>
          
          ...........

It works... dont ask me why..