Instead of designing and writing the data in a Excel file, it is much easier to have a template with all the designs embedded. We have to just read the template file and write our data in the particular cells. But if we are going to have, say, ‘n’ number of sheets, then the template file also should have the same number of sheets whose memory will also be high and the time it takes to read it will also be high.
However, we can work around it to avoid the extra memory allocation and delay. To make dynamic generation of sheets, we have to clone a sheet, which means we will be having one sheet template file, through which we can clone and add any number of sheets dynamically. This also reduces the time it takes to read a template file.
The following snippet of code demonstrates how to do it using PHPExcel library file
$objPHPExcel = PHPExcel_IOFactory::load("../excel_templates/q4full.xls");
$sheet1 = $objPHPExcel->getActiveSheet()->copy();
//here template is copied
$sheet2 = clone $sheet1;
//notice that here sheet is cloned
//it works and solved problem with memory leaks
$sheet_title = 'Cloned'.$i;
In my project, I need 85 sheets with the same design, the size of the template with all 85 sheets designed is 381 KB, but the size of the single sheet template is 12KB.
After this cloning, we can generate the xls file more dynamically with any number of sheets.