In Release R21e of October 2021, Calem will select a library based on the number of rows to export to Excel in reports.

  • PhpSpreadsheet is used as the default library to generate Excel file. 
    • It has rich formatting features. The Excel looks better.
    • It takes long time in generating an excel report of thousands of rows.
    • It takes about 1KB of memory for an Excel cell. A significant amount of memory must be available for a report of thousands of rows.
    • A cache may be configured to reduce memory usage. It was tedious to configure a cache, and the Excel generation time is extended significantly.
  • Box/Spout is the one used for large data list.
    • It takes about half the time of PhpSpreadsheet to generate an Excel of thousands of rows.
    • It keeps the memory usage extremely low (3MB).
    • It does not have as rich formatting features. For instance, setting column width for each cell is not in general availability at this writing. Therefore, the resulting Excel sheets have columns of the same length by default.


Here is a summary of the differences. 

​ ​PhpSpreadsheet ​Box/Spout​
​Formatting and Styles ​Rich​​ Simple (e.g. lack of column width control)​​​
​Performance​Slow​​Fast (half of the time of PhpSpreasheet)
​Memory Usage​Large (for reports of thousands of rows)​​Small (3MB)​​

1. Library Configuration

A system configuration is available to control when to use the Spout library. If a report has 5000 rows (default configuration) the Spout library is used to generate the excel report. Otherwise, PhpSpreadsheet is used. 

Add the following config lines to server/conf/calem.custom.php to tweak the use of Spout.

  • An option to enable Spout library. If it is not enabled, the Spout library will not be used.
  • The minimum number of rows to use Spout (default is 5000)
  • Optionally to exclude the use of Spout for certain reports (by adding report Ids to the exclusion array).

//Enable spout library
$_CALEM_dist['report_conf']['spout_by_row']=true;

//Minimum number of rows to use Spout
$_CALEM_dist['report_conf']['spout_row_min']=5000;

//Optionally exclude report from Spout
$_CALEM_dist['report_conf']['spout_by_row_ex']=array(

);

2. Samples of Excel Export

The first screenshot is an export with PhpSpreadsheet, the second one is with Spout. 


Additional resources