jsfprimefacesexport-to-excelprimefaces-dataexporter

Unable to export the dataTable when one of the column contains only unicode number


I am using PrimeFaces 11 and I have a scenario where I have to export dataTable to Excel in a form. My .xhtml code looks like this:

<p:commandButton id="cmdexcel" value="#{text.Excel}"
                    icon="fa fa-file-excel-o" ajax="false"
                    disabled="#{empty ledgerMB.lstLedger}">
                    <p:dataExporter type="xls" target="dataTblLedger" encoding="UTF-8"
                        fileName="Ledger"
                        postProcessor="#{ledgerMB.postProcessXLS}" />
</p:commandButton>

<p:dataTable var="tblLedger" value="#{ledgerMB.lstLedger}"
                id="dataTblLedger" rowIndexVar="rowSn" paginator="true" rows="25"
                scrollable="true" scrollHeight="490" scrollWidth="98%"
                rowStyleClass="#{tblLedger.boldFlag ? 'bold-total':''}"
                paginatorTemplate="{FirstPageLink} {PreviousPageLink} {CurrentPageReport} {NextPageLink} {LastPageLink} {RowsPerPageDropdown}"
                rowsPerPageTemplate="25,50,100,150,200" resizableColumns="true"
                emptyMessage="#{text.NoRecordFound}">
                <p:column headerText="#{text.SNo}" width="50">
                    <h:outputText value="#{tblLedger.sno eq 0?' ':tblLedger.sno}"
                        styleClass="np" />
                </p:column>
                <p:column headerText="#{text.Date}" width="100">
                    <h:outputText value="#{tblLedger.approvedDateBs}" styleClass="np" />
                </p:column>
                <p:column headerText="#{text.VNo}" width="70">
                    <h:outputText value="#{tblLedger.jvNo eq 0 ? '' : tblLedger.jvNo}"
                        styleClass="np" />
                </p:column>
                <p:column headerText="#{text.Remarks}" width="70">
                    <h:outputText value="#{tblLedger.remarks}"
                        styleClass="np" />
                </p:column>
                
                
</p:dataTable>

My managed bean code is like this:

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    sheet.shiftRows(0, sheet.getLastRowNum(), 0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    HSSFFont fontHeader = (HSSFFont) wb.createFont();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    fontHeader.setFontName("Magnus Nepali");
    cellStyle.setFont(fontHeader);
    for (Row row : sheet) {
        for (Cell cell : row) {
            cell.setCellStyle(cellStyle);
        }
    }
    try {
        wb.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

When my remarks column contains only unicode number like '२२२५०' i get following error:

SEVERE: Servlet.service() for servlet [Faces Servlet] threw exception
java.lang.NumberFormatException: For input string: "?????"
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043)
    at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
    at java.lang.Double.parseDouble(Double.java:538)
    at org.primefaces.component.datatable.export.DataTableExcelExporter.updateCell(DataTableExcelExporter.java:344)
    at org.primefaces.component.datatable.export.DataTableExcelExporter.lambda$addColumnValue$0(DataTableExcelExporter.java:232)
    at org.primefaces.component.export.TableExporter.exportColumn(TableExporter.java:79)
    at org.primefaces.component.datatable.export.DataTableExcelExporter.addColumnValue(DataTableExcelExporter.java:232)
    at org.primefaces.component.datatable.export.DataTableExcelExporter.exportCells(DataTableExcelExporter.java:129)
    at org.primefaces.component.datatable.export.DataTableExporter.exportRow(DataTableExporter.java:144)
    at org.primefaces.component.datatable.export.DataTableExporter.exportAll(DataTableExporter.java:129)
    at org.primefaces.component.datatable.export.DataTableExcelExporter.exportTable(DataTableExcelExporter.java:405)
    at org.primefaces.component.datatable.export.DataTableExcelExporter.doExport(DataTableExcelExporter.java:97)
    at org.primefaces.component.datatable.export.DataTableExporter$ExportVisitCallback.visit(DataTableExporter.java:241)
    at org.apache.myfaces.component.visit.PartialVisitContext.invokeVisitCallback(PartialVisitContext.java:213)

But when I add some character to unicode number remarks other than unicode numbers then excel is being exported. I am using Apache POI 5.2.2


Solution

  • Try setting setStronglyTypedCells(false); which won't try and translate numbers for Excel.

    https://primefaces.github.io/primefaces/11_0_0/#/components/dataexporter?id=customization

    public class CustomizedDocumentsView implements Serializable {
        private ExcelOptions excelOpt;
    
        @PostConstruct
        public void init() {
            excelOpt = new ExcelOptions();
            excelOpt.setStronglyTypedCells(false);
        }
        public ExcelOptions getExcelOpt() {
            return excelOpt;
        }
    }
    
    <h:commandButton value="Export as XLS">
        <p:dataExporter type="xls" target="tableId" fileName="cars" options="#{customizedDocumentsView.excelOpt}"/>
    </h:commandButton>