Access a multi-sheet spreadsheet

Topics: User Forum
Aug 4, 2014 at 6:13 PM
Hi,

I need to extract information from a Excel spreadsheet, including background colour, font colour, border and content.

The file is around 5 or 6 Meg.

This is my code:
 $storagename=$_SERVER["DOCUMENT_ROOT"].'/spreadsheets/sites/default/files/spread1.xlsx';
  // This is the file path to be uploaded.
  if (file_exists($storagename)) {
    $bgcolor=array();
    $inputFileType = PHPExcel_IOFactory::identify($storagename);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    // Get names of worksheets in the file.
    $worksheetNames = $objReader->listWorksheetNames($storagename);
    // Print worksheet names.
    foreach ($worksheetNames as $sheetName) {
      echo '<h3>'.$sheetName.'</h3>';
      
      $objReader = PHPExcel_IOFactory::createReader('Excel2007');
      $objReader->setLoadSheetsOnly($sheetName);
      $objPHPExcel = $objReader->load($storagename);
      $objWorksheet = $objPHPExcel->getActiveSheet();
      $rownumber=0;
      foreach ($objWorksheet->getRowIterator() as $row){
        $cellIterator = $row->getCellIterator();
        $rownumber++;
        $colwidths=array();
        foreach ($cellIterator as $cell) {
          $cellcolumn=$cell->getColumn();
          $cellindex=PHPExcel_Cell::columnIndexFromString($cellcolumn);
          if (!array_key_exists($cellcolumn,$colwidths)) {
            $colwidths[$cellcolumn]= $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($cellcolumn)->getWidth();
          }
          echo '; '.$cellcolumn.$rownumber.'='.$colwidths[$cellcolumn].', #'.$bgcolor[$cellindex.'_'.$rownumber];
        }
        echo '<hr/>';
      }
    }
  }else {
    die('File not found.<hr/>');
  }
The spreadsheet has the following sheet names:
"Definitions", "SAQA", "DPSA", "PSETA", "PALAMA", "FASSET", "QCTO", "CHE (Summary)"

The first sheet has 17 columns and 40 rows.
The second sheet has 26 columns and 64 rows.
The third sheet has 6 columns and 39 rows.

The first two sheets are processed correctly.
The sheetnames "Definitions" and "SAQA" are echoed as expected. However, my /var/log/apache2/error.log file has the following error:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 88 bytes) in /var/www/spreadsheets/sites/all/libs/Classes/PHPExcel/Style.php on line 120.

I've researched the error and I believe that the way to address this is to read a few lines at a time.

However, the spreadsheet worksheets don't seem to be overly large.

Should I be looking elsewhere?
Coordinator
Aug 5, 2014 at 12:45 AM
Physical size of a file is less relevant than the number of rows and columns, and the number of rows and columns may not always be what you believe, because an empty cell is still a cell.

Look at some of the suggestions given in the documentation about memory use, such as cell caching, and also on clearing memory when reading repeatedly in a loop.
Aug 6, 2014 at 8:30 AM
Hi Mark,

Thanks for your reply.

I've altered my code:

  if (file_exists($storagename)) {
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;//<-------------------------Added
    $cacheSettings = array('dir' => '/tmp');//<-------------------------Added
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);//<-------------------------Added
    $inputFileType = PHPExcel_IOFactory::identify($storagename);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $worksheetNames = $objReader->listWorksheetNames($storagename);
    foreach ($worksheetNames as $sheetName) {
      echo '<h3>'.$sheetName.'</h3>';
      $objReader = PHPExcel_IOFactory::createReader($inputFileType);
      $objReader->setLoadSheetsOnly($sheetName);
      $objPHPExcel = $objReader->load($storagename);
      $objWorksheet = $objPHPExcel->getActiveSheet();
      PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
      $rownumber=0;
      foreach ($objWorksheet->getRowIterator() as $row){
        $cellIterator = $row->getCellIterator();
        $rownumber++;
        $colwidths=array();
        foreach ($cellIterator as $cell) {
          $cellcolumn=$cell->getColumn();
          $cellindex=PHPExcel_Cell::columnIndexFromString($cellcolumn);
          if (!array_key_exists($cellcolumn,$colwidths)) {
            $colwidths[$cellcolumn]= $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($cellcolumn)->getWidth();
          }
          $bgcolor=$cell->getStyle()->getFill()->getStartColor()->getRGB();
          echo '; '.$cellcolumn.$rownumber.'='.$colwidths[$cellcolumn].', #'.$bgcolor;
        }
        echo '<hr/>';
      }
      $objPHPExcel->disconnectWorksheets();//<-------------------------Added
      unset($objPHPExcel);//<-------------------------Added
    }
  }else {
    die('File not found.<hr/>');
  }
The second sheetname is echoed, but the script halts before the second sheet is traversed.

The error in /var/log/apache2/error.log is:

[error] [client 127.0.0.1] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in /var/www/spreadsheets/sites/all/libs/Classes/PHPExcel/Style/Borders.php on line 137

Thanks for your assistance.

~ Dave