Reading Large Excel Files Efficiently in PHP

zeroexp.dev ZeroonLeave a Comment on Reading Large Excel Files Efficiently in PHP
Reading Large Excel Files Efficiently in PHP

Learn how to read large Excel files efficiently in PHP using PhpSpreadsheet. Optimize memory usage, process big data files, and avoid performance bottlenecks.

Introduction

Processing large Excel files in PHP can cause high memory consumption and slow execution times. When dealing with thousands or millions of rows, an inefficient approach can crash your script or exceed server limits.

With PhpSpreadsheet, you can:

  • Read large Excel files without running out of memory
  • Process big datasets efficiently
  • Use streaming techniques to handle large Excel files
  • Optimize performance for bulk data imports

This guide covers:

  • Installing PhpSpreadsheet
  • Handling large Excel files efficiently
  • Using streaming readers to process data in chunks
  • Optimizing memory usage for improved performance

1. Installing PhpSpreadsheet for Large File Handling

To handle Excel files in PHP, install PhpSpreadsheet via Composer:

composer require phpoffice/phpspreadsheet

Include PhpSpreadsheet in your PHP script:

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Csv;

Once installed, you can process large Excel files without exhausting server resources.

2. Using a Streaming Reader for Large Excel Files

Instead of loading the entire file into memory, use a streaming reader to process large Excel files efficiently.

Example: Read Large Excel File Efficiently

$reader = new Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('large_file.xlsx');

$worksheet = $spreadsheet->getActiveSheet();

foreach ($worksheet->getRowIterator() as $row) {
    $data = [];
    foreach ($row->getCellIterator() as $cell) {
        $data[] = $cell->getValue();
    }
    print_r($data);
}

Why Use This Method?

✅ Loads only required data instead of the entire spreadsheet
✅ Reduces memory consumption for large datasets
✅ Faster processing compared to full file loading

3. Reading a Large Excel File Row by Row

If an Excel file contains millions of rows, process it row by row instead of loading everything at once.

$reader = new Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('large_data.xlsx');
$worksheet = $spreadsheet->getActiveSheet();

foreach ($worksheet->getRowIterator() as $row) {
    $cells = [];
    foreach ($row->getCellIterator() as $cell) {
        $cells[] = $cell->getValue();
    }
    // Process row data (e.g., store in database)
}

✅ Ideal for handling extremely large datasets

4. Reading Only Specific Columns to Save Memory

Instead of processing all columns, extract only the required ones.

foreach ($worksheet->getRowIterator() as $row) {
    $id = $worksheet->getCell('A' . $row->getRowIndex())->getValue();
    $name = $worksheet->getCell('B' . $row->getRowIndex())->getValue();
    $email = $worksheet->getCell('C' . $row->getRowIndex())->getValue();

    echo "$id | $name | $email \n";
}

✅ Reduces memory usage by extracting only relevant data

5. Importing Large Excel Files into MySQL Without Memory Issues

For bulk data imports, use prepared statements instead of loading everything into memory.

Example: Insert Data into MySQL Efficiently

$conn = new mysqli("localhost", "root", "", "large_excel_db");

$reader = new Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('large_data.xlsx');
$worksheet = $spreadsheet->getActiveSheet();

$stmt = $conn->prepare("INSERT INTO users (id, name, email) VALUES (?, ?, ?)");

foreach ($worksheet->getRowIterator() as $row) {
    $id = $worksheet->getCell('A' . $row->getRowIndex())->getValue();
    $name = $worksheet->getCell('B' . $row->getRowIndex())->getValue();
    $email = $worksheet->getCell('C' . $row->getRowIndex())->getValue();

    $stmt->bind_param("iss", $id, $name, $email);
    $stmt->execute();
}

echo "Large Excel data imported successfully.";

✅ Uses MySQL prepared statements for efficient bulk inserts

6. Converting Large Excel Files to CSV for Faster Processing

Sometimes, it’s better to convert Excel files into CSV before processing.

Example: Convert Large Excel to CSV

use PhpOffice\PhpSpreadsheet\Writer\Csv;

$spreadsheet = IOFactory::load('large_file.xlsx');
$writer = new Csv($spreadsheet);
$writer->save('large_file.csv');

echo "Excel converted to CSV for optimized processing.";

✅ CSV files are faster to read and process than Excel files

7. Limiting Rows While Processing Large Excel Files

If you only need a subset of rows, limit the number of rows processed.

$maxRows = 1000;
$rowCount = 0;

foreach ($worksheet->getRowIterator() as $row) {
    if ($rowCount >= $maxRows) break;
    
    $data = [];
    foreach ($row->getCellIterator() as $cell) {
        $data[] = $cell->getValue();
    }
    
    print_r($data);
    $rowCount++;
}

✅ Processes only the first 1000 rows to prevent memory overload

8. Using Memory Optimization Techniques in PHP

When handling very large files, optimize PHP memory settings.

Increase PHP Memory Limit Temporarily

ini_set('memory_limit', '512M');
set_time_limit(300);

✅ Allows PHP to process large Excel files without hitting memory limits

9. Automating Large Excel File Processing with Cron Jobs

For scheduled batch processing, use a cron job.

0 3 * * * php /var/www/html/process_large_excel.php

✅ Runs the script every day at 3 AM without manual execution

10. Processing Excel Data in Chunks Instead of One Go

If Excel files contain millions of rows, process data in chunks.

$batchSize = 500;
$batch = [];

foreach ($worksheet->getRowIterator() as $row) {
    $data = [];
    foreach ($row->getCellIterator() as $cell) {
        $data[] = $cell->getValue();
    }
    
    $batch[] = $data;

    if (count($batch) >= $batchSize) {
        processBatch($batch);
        $batch = []; // Reset batch
    }
}

if (!empty($batch)) {
    processBatch($batch);
}

function processBatch($batch) {
    // Insert batch into database or process further
}

✅ Processes large files in smaller chunks, improving performance

Best Practices for Reading Large Excel Files in PHP

  • Use streaming readers (setReadDataOnly(true)) to reduce memory usage
  • Extract only necessary columns instead of loading full rows
  • Convert large Excel files to CSV for faster processing
  • Optimize memory limits (ini_set('memory_limit', '512M'))
  • Process data in chunks to prevent timeouts and crashes
  • Use MySQL prepared statements for bulk database imports

Conclusion

Reading large Excel files efficiently in PHP requires memory optimization and structured data processing.

This guide covered:

  • Using streaming readers for large Excel files
  • Extracting only required data to reduce memory usage
  • Processing Excel rows in chunks for better performance
  • Importing Excel data into MySQL efficiently
  • Optimizing PHP scripts for large file handling

By following these techniques, PHP applications can handle large Excel datasets without performance issues.

Leave a Reply