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
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>