Timeout when exporting large dataset as XLSX using p:dataExporter

Question

I am able to export 68000 records. When I try to export records more than 80000+ records, it takes more than 2 mins and I will get server timed out error.

<h:commandLink id="excelLinkId">
    <p:dataExporter type="xlsx" target="dataTableId" fileName="file"/>
</h:commandLink>

I'm using PrimeFaces 8.0.

Answer 1

Don't use type="xlsx" that stores it all in memory! Use type="xlsxstream" which writes one row at a time and then garbage collects it. I have written out 8000 line PrimeFaces Datatable's no problem with xlsxstream.

Answer 2

To improve performance when exporting to Excel, you should use the following JSF code:

 <p:dataExporter type="xlsx" target="myDataTable" options="#{mybBean.excelOpt}" fileName="fichero.xslx" />

In the Java class representing the bean, you should include the following code, where it is indicated not to calculate the width of the columns. Bean code

 public ExcelOptions getExcelOpt() {
var excelOpt = new ExcelOptions();
excelOpt.setAutoSizeColumn(false);
return excelOpt; 
}

The reason why the export fails or takes long is because the exporter analyzes all the rows of each column and calculates the maximum width that will be applied to that column. This process is not optimal in the POI library of Java.