Great question! Since you’re already using PHP_XLSXWriter successfully for creating large files without memory overflow, let’s break down your questions about loading large files in PHPExcel and PhpSpreadsheet.

1. How to Load Large Files in PHPExcel? #

The core solution for loading large files in PHPExcel without memory bloat is using Read Filters—this lets you load only specific rows or columns instead of the entire workbook. Here’s a practical example:

First, create a custom filter class to define which data to load:

class MyReadFilter implements PHPExcel_Reader_IReadFilter {
    private $_startRow = 0;
    private $_endRow = 0;

    public function __construct($startRow, $endRow) {
        $this->_startRow = $startRow;
        $this->_endRow = $endRow;
    }

    public function readCell($column, $row, $worksheetName = '') {
        // Load only rows within the specified range
        if ($row >= $this->_startRow && $row <= $this->_endRow) {
            return true;
        }
        return false;
    }
}

Then use this filter to load the file in batches:

$inputFileName = 'large_file.xlsx';
$reader = PHPExcel_IOFactory::createReader('Xlsx');

// Enable data-only mode to skip style loading (saves tons of memory)
$reader->setReadDataOnly(true);

// Load first batch: rows 1 to 1000
$filterSubset = new MyReadFilter(1, 1000);
$reader->setReadFilter($filterSubset);
$spreadsheet = $reader->load($inputFileName);

// Process this batch of data here...

// Load next batch: rows 1001 to 2000
$filterSubset = new MyReadFilter(1001, 2000);
$reader->setReadFilter($filterSubset);
$spreadsheet = $reader->load($inputFileName);

// Repeat until all rows are processed

2. Other Methods for Handling Large Files in PHPExcel #

Beyond read filters, these tricks can help with large file processing:

• Load only specific worksheets: Skip loading unused sheets to cut down on memory:

  $reader->setLoadSheetsOnly(['SalesData']); // Load only the "SalesData" sheet
  

• Convert to CSV first: If your source is an XLSX file, convert it to CSV (via PHPExcel or a command-line tool) first. CSV files are far lighter to process line-by-line with fopen() and fgetcsv().
• Disable formula pre-calculation: Turn off automatic formula evaluation if you don’t need computed values:

  $reader->setPreCalculateFormulas(false);
  

3. Does PhpSpreadsheet Solve Large File Loading Issues? #

Absolutely! PhpSpreadsheet is the official, maintained successor to PHPExcel (which is now deprecated), and it’s been optimized specifically for better memory management with large files.

Key improvements for large file loading:

• Enhanced Read Filters: Works similarly to PHPExcel’s filters but with more efficient underlying code that uses less RAM.
• Row Iterators: You can iterate over rows without loading the entire workbook into memory. Example:

  $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
  $reader->setReadDataOnly(true);
  $reader->setLoadSheetsOnly('SalesData');
  
  $worksheet = $reader->load('large_file.xlsx')->getActiveSheet();
  foreach ($worksheet->getRowIterator() as $row) {
      $cellIterator = $row->getCellIterator();
      $cellIterator->setIterateOnlyExistingCells(false); // Include empty cells if needed
      foreach ($cellIterator as $cell) {
          // Process individual cell values
          $value = $cell->getValue();
      }
  }
  

• Better Memory Footprint: PhpSpreadsheet uses optimized data structures, so even loading large data subsets uses significantly less memory than PHPExcel.
• Batch Processing Support: Combine read filters with loops to process ultra-large files in chunks—just like PHPExcel, but with smoother performance.

Final Takeaways #

• PHPExcel can handle large files, but it requires careful use of read filters and memory optimizations.
• PhpSpreadsheet is the recommended tool now (since PHPExcel is no longer updated) and offers better out-of-the-box support for large file loading.
• PHP_XLSXWriter shines at writing large files, while PhpSpreadsheet is a strong all-around solution for both reading and writing large spreadsheets.

内容的提问来源于stack exchange，提问作者Yurley Sánchez