Ways to optimize phpexcel code when writing data in many worksheet

Question

I am generating reports in .xlsx using PHPExcel.There I am getting some speed issues whenever i generate multiple worksheet report. And the same report when i generate with spreadsheet_excel_writer in .xls it takes much less time.

Some of the statistics analysis when comparing multiple worksheet report to single worksheet report with same data(using PHPExcel) are

In my local:

1200 rows in Single Worksheet Report : 10 seconds.

1200 rows in 15 Worksheets Report : 30 seconds.

On Production:

1200 rows in Single Worksheet Report : 24 seconds.

1200 rows in 15 Worksheets Report : 73 seconds.

Don't know why this difference is coming.

Apart from this,I have created the same report using spreadsheet_excel_writer in .xls i.e. the 1200 rows report in 15 Worksheets Report that takes the 6-7 second to generate.

I am using this to create multiple worksheet

while($data = $data_loc->fetchRow()) { 

 if($check_first_worksheet == 1){ // if it is first worksheet it doesnt create it,just get the activesheet
            $objWorkSheet = $objPHPExcel->getActiveSheet();
            $check_first_worksheet++;  
        }
        else{
            $objWorkSheet = $objPHPExcel->createSheet();     
        }

}

Am I doing anything wrong ? Please suggest to improve this ?

Also used Mark Baker's Answer to optimize my excel. Anything else i can do ? Please suggest ?

Answer 1

If you already followed Mark's recommendation in the post you mentioned, then there is not much more you can do.. The more sheets you have, the more data PHPExcel has to keep track and write, doing a bunch a expensive calculation for each worksheet.

If you are not satisfied with the performance of PHPExcel, I would suggest you to use an alternative library. The spreadsheet_excel_writer library that you mentioned is super old and only support BIFF5 files ((it was introduced with Excel 95!!). Another alternative is Spout, which should be even faster and allow you to easily write XLSX files.