PHPExcel Writer object uses huge memory and time outs eventually

Question

I am creating a excel sheet using:

1. Codeigniter 2.2.1
2. PHP 5.4.25
3. Apache 2.4.7
4. XAMPP 1.8.2
5. PHPExcel 1.8.0

I am fetching 35000 rows (mostly empty) with 79 columns from database and writing to excel file (Excel5).

It works just fine for 30000 rows with peak memory usage of 1.44GB,file size 24MB. But when I go for 35000 it times out saying,

Fatal error: Out of memory (allocated 1780219904) (tried to allocate 17301483 bytes) in E:\XAMPP\htdocs\ProjectName\application\libraries\PHPExcel\Writer\Excel5\BIFFwriter.php on line 144

When I am creating PHPExcel object it uses big amount of memory and time outs finally. I have set "memory_limit= -1" and "max_execution_time= 1000" in php.ini file and tried different cache storage method in PHPExcel.

My algorithm in controller looks like this

public function write_controller() {    
    error_reporting(E_ALL);
    ini_set("display_errors", 1);
    ini_set('memory_limit', '-1'); //-1 for unlimited memory
    $dir = "assets/output/";

    //FIRST CHECK IF PREVIOUS FILE EXISTS OR NOT
    $this->clear_directory($dir);

    // Loading PHPExcel library
    $this->load->library('PHPExcel');
    $this->load->library('PHPExcel/IOFactory');

    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    $cacheSettings = array('memoryCacheSize' => '5000MB', 'cacheTime' => '1000'); 

    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

    //First Create the xls file and then insert rest of the data
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");

    //activate sheet number 1
    $objPHPExcel->setActiveSheetIndex(0);

    //Setting font styles
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial')->setSize(8)->setBold(false);

    //Setting number format as TEXT
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

    //Freezing first top row
    $objPHPExcel->getActiveSheet()->freezePane('A2');


    $objWorksheet = $objPHPExcel->getActiveSheet();

    $row1 = 1;

    $objWorksheet->setCellValueByColumnAndRow(0, $row1, "Site name");
    $objWorksheet->setCellValueByColumnAndRow(1, $row1, "Vendor_1");
    $objWorksheet->setCellValueByColumnAndRow(2, $row1, "Status");
    $objWorksheet->setCellValueByColumnAndRow(3, $row1, "Easting");
    $objWorksheet->setCellValueByColumnAndRow(4, $row1, "Northing");
    $objWorksheet->setCellValueByColumnAndRow(5, $row1, "Sector_1");
    .
    .
    .
    //Rest of the 74 columns

    $style = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)
    );

    $objWorksheet->getDefaultStyle()->applyFromArray($style);

    $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');

    $saved_location ='assets/output/Piano11.xls'; 
    $objWriter->save($saved_location); 

    //Now reading the saved xls file
    $objReader = new PHPExcel_Reader_Excel5();

    $newPHPExcel = $objReader->load($saved_location); 

    $newWorksheet = $newPHPExcel->getActiveSheet();

    //Now insert rest of the data from Piano table which will come from database
    $table_name = 'piano_test';

    $query = $this->db->get('tbl_piano');

    if (!$query) {
        return false;
    }

    // Fetching the data from table
    $fields = $query->list_fields();

    $row = 2;
    foreach ($query->result() as $data) {
        set_time_limit(0);
        $col = 0;
        foreach ($fields as $field) {
            $newWorksheet->setCellValueByColumnAndRow($col, $row, $data->$field); //<- This skips leading 0s
            $col++;
        }
        $row++;
    }

    $newobjWriter = IOFactory::createWriter($newPHPExcel, 'Excel5');
    $newobjWriter->save('assets/output/Piano11.xls');

    echo 'Memory peak usage: <b>'.$this->convert(memory_get_peak_usage(true)).'</b><br/>';
    gc_collect_cycles();//garbage collector
    echo 'inserted.';
}

Any solution how can I minimize memory usage & execution time? Or Any other alternative solution? Or Should I change my algorithm?

Answer 1

You're using phptemp for caching, but with settings

$cacheSettings = array('memoryCacheSize' => '5000MB', 'cacheTime' => '1000'); 

This means that PHPExcel will use 5000MB (5GB) of your PHP memory before its starts to make use of phptemp for caching..... I'd be surprised if you had php.ini max memory settings allowing PHP to use that much memory

You should use a much lower value for memoryCacheSize, perhaps 512MB, which means that PHPExcel will only use 512MB of PHP Memory for caching cell data before it switches to using php://temp