Large data set causes Writer object to kill process

Topics: Developer Forum, User Forum
Aug 1, 2011 at 11:14 AM

Using PHPExcel 1.7.6 on PHP 5.2.14 on Debian 4.0.

My code is pretty straightforward. I loop through a MySQL DB and populate a 2-d worksheet, doing minimal formatting (posted below). Each worksheet is appended to a larger workbook. For smaller datasets PHPExcel is able to process the data. Here is an example of some the output from my test tool; parsing the output should be pretty easy. The first line tells you how many datasets from the DB are going to be processed. The next two lines are per worksheet. If it finishes successfully it gives you total time + total memory used.

[03:42:30] numSurveys: 6 
[03:42:34] time:  4s memoryUsed: 15.73MB
[03:42:34] dimensions: A1:AE883
[03:42:38] time:  8s memoryUsed: 25.73MB
[03:42:38] dimensions: A1:AC810
[03:42:43] time: 13s memoryUsed: 32.32MB
[03:42:43] dimensions: A1:U787
[03:42:49] time: 19s memoryUsed: 38.70MB
[03:42:49] dimensions: A1:U734
[03:42:55] time: 25s memoryUsed: 46.35MB
[03:42:55] dimensions: A1:AA633
[03:43:02] time: 32s memoryUsed: 52.94MB
[03:43:02] dimensions: A1:U748
[03:43:02] Finished data loop
[03:43:02] Created docWriter
[03:43:02] Saving file@/source/epwbst/content/temporary/LACOE___LACEF_export_2011-08-01_03-42.xls
[03:43:02] Saved file
[03:43:25] total time: 55s total memory: 62.30MB

[03:55:21] numSurveys: 6 
[03:55:34] time: 13s memoryUsed: 42.74MB
[03:55:34] dimensions: A1:U2772
[03:55:51] time: 30s memoryUsed: 77.82MB
[03:55:51] dimensions: A1:Y2681
[03:56:05] time: 44s memoryUsed: 95.47MB
[03:56:05] dimensions: A1:U2295
[03:56:21] time: 60s memoryUsed: 115.24MB
[03:56:21] dimensions: A1:U2230
[03:56:39] time: 78s memoryUsed: 134.17MB
[03:56:39] dimensions: A1:U2113
[03:56:59] time: 98s memoryUsed: 160.96MB
[03:56:59] dimensions: A1:AA2151
[03:56:59] Finished data loop
[03:56:59] Created docWriter
[03:56:59] Saving file@/source/epwbst/content/temporary/I_CAN_West_Coast_export_2011-08-01_03-55.xls

The big difference is, for the larger data set, the file never gets saved. The Writer object gets created but I don't know how to debug it because asking PHP to print_r it, kills the process. Is this a bug? Or does this mean I need to allocate more memory. Memory is at a premium for us, but time is not. I've been trying the other cell caching methods but the only that works is cache_in_memory_serialized. The other configurations just cause my PHP script to die.

 

    public function exportAllSurveys(&$surveyObj, $filePath, $agencyID=SRV_EXPORT_ALL_AGENCIES, 
                                     $includeAnon=true, $sortBy='')
    {	
      $this->_clearError();
      
      $surveyIndices = $this->getSurveyIndices($surveyObj);
      $numSurveys = count($surveyIndices);

      //$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
      $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
      // $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
      //$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
      PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
/*	    
      $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
      $cacheSettings = array('memoryCacheSize' =>'1MB');
      PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
*/
      $exportDoc = new PHPExcel();

      for ($i = 1; $i <= $numSurveys; $i++){        
        $surveyData = $this->_retrieveSurveyData($surveyIndices[$i-1], $agencyID, $includeAnon, $sortBy);
        $exportDoc->createSheet($i);
        $exportDoc->setActiveSheetIndex($i);    
        $exportDoc = $this->_formatSpreadsheet($exportDoc, $surveyData['docData'], $surveyData['docHeader']);	    
  
        // Rename sheet
        $exportDoc->getActiveSheet()->setTitle($surveyObj[$surveyIndices[$i-1]][F_NAME]);

      } 

        $exportDoc->setActiveSheetIndex(0);
        $docWriter = PHPExcel_IOFactory::createWriter($exportDoc, 'Excel5');
        $docWriter->save($filePath);

        return true;
    }
    
    /*
        Packages surveyData in a form that PHPExcel likes. If there is no data in the
        data or header arrays, we just print a notice in the first cell of the worksheet.
    */
    private function _formatSpreadsheet(&$dataDoc, $data, $header = array()){        
        // Populate Excel doc object. Columns are 0-indexed, rows are 1-indexed.
        $docCol = 0;
        $docRow = 1;
        
        if (count($data) > 0 && count($header) > 0){
            $activeSheet = $dataDoc->getActiveSheet();        
            // The header rows are formatted as bold, horizontal center.
            if (count($header) > 0){
                foreach($header as $cell){
                    $activeSheet->getCellByColumnAndRow($docCol, $docRow)->setValue($cell);
                    $headerCellStyle = $activeSheet->getStyleByColumnAndRow($docCol, $docRow);
                    $headerCellStyle->getAlignment()
                                    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $headerCellStyle->getFont()
                                    ->setBold(true);
                    $docCol++;
                }
                $docCol = 0;
                $docRow++;
            }
            
            foreach($data as $dataRow){
                foreach($dataRow as $dataCell){
                    $activeSheet->getCellByColumnAndRow($docCol, $docRow)
                                             ->setValue($dataCell);
                    $docCol++;
                }
                $docCol = 0;
                $docRow++;
            }
        } else {
            $dataDoc->getActiveSheet()->getCellByColumnAndRow($docCol,$docRow)
                                      ->setValue("No data has been entered for this survey.");
        }
        $activeSheet = $dataDoc->getActiveSheet();                
        $docHighCol = chr(SRV_SIZE_SSHDR + ord('A'));
        for ($col='A'; $col <= $docHighCol; $col++){
            $activeSheet->getColumnDimension($col)->setAutoSize(true);
        }

        return $dataDoc;
    }