How to use phpexcel to read data and insert into database?

How to use phpexcel to read data and insert into database?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Migrating from PHPEExcel to Modern Spreadsheet Handling

If you are looking to read data from an Excel file, insert it into a database, and subsequently generate PDF reports, the first technical decision you face is choosing your spreadsheet library. The name "PHPEExcel" often brings up old packages like phpexcel or phpspreadsheet, but relying on legacy code in 2024 introduces significant maintenance risks. Modern applications should prioritize libraries that are actively maintained and support current OpenXML standards, as older versions struggle with newer Excel formats and lack robust error handling for complex data structures.

The Architecture of File Parsing

Reading an Excel file begins with determining the library's ability to parse specific row structures and cell types. Legacy packages frequently fail when encountering merged cells, rich text, or embedded images without throwing clear exceptions. When you choose a modern alternative like PHPSpreadsheet (the successor to PHPEExcel) or PhpOffice\PhpSpreadsheet, you gain access to better event listeners and memory management.

The core logic for reading data usually involves iterating through rows and mapping columns to your database schema. Consider this conceptual approach using a modern PHP environment:

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

$reader = new Xlsx();
$file = 'reports.xlsx';

if ($spreadsheet = $reader->load($file)) {
    $worksheet = $spreadsheet->getActiveSheet();
    
    foreach ($worksheet->getRowIterator(1) as $row) { // Skip header row at index 0
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
        
        $rowData = [];
        foreach ($cellIterator as $cell) {
            $rowData[] = [
                'column' => $cell->getColumn(),
                'value' => $cell->getValue()
            ];
        }

        // Here you would prepare the data for database insertion
        insertIntoDatabase($rowData);
    }
}

This pattern demonstrates how to safely iterate through existing cells while avoiding index errors. A critical pitfall occurs when assuming all rows have the same number of columns; failing to check for empty or merged cells can lead to IndexOutOfBoundsException during bulk inserts. Always validate that the data structure matches your database constraints before executing the write operation.

Database Insertion Strategies

Once you have extracted the data, inserting it into a database requires careful consideration of performance and data integrity. A naive approach using a loop with individual INSERT statements is inefficient for large datasets. Instead, leverage batch operations or prepared statements to prevent SQL injection and reduce network overhead.

If your application runs on Laravel, utilize Eloquent's insert() method or raw query batching:

use Illuminate\Support\Facades\DB;

// Collect data in chunks to avoid memory exhaustion
$dataChunks = array_chunk($rowData, 100);

foreach ($dataChunks as $chunk) {
    // Assuming a table 'users' with columns matching the row data
    DB::table('users')->insert($chunk); 
}

However, if you are dealing with very large files (e.g., over 100MB), streaming the data might be necessary. You can parse the file in chunks and insert incrementally to keep memory usage low. This is particularly important when generating PDFs immediately after, as holding massive datasets in memory will cause PHP scripts to hit the memory_limit directive and crash.

Generating PDF Reports for Specific Users

The final step—generating PDF reports—depends heavily on how you structure your data before sending it to the PDF generation engine. Whether you use a library like DomPDF, TCPDF, or Snappy (via wkhtmltopdf), the input source is typically HTML or an array of data rows.

A common requirement is filtering the inserted data for "specific users" before rendering. Since you have already populated the database, you can query specific user records and pass them to the PDF generator. Be mindful of the overhead here: if you insert 50,000 rows but only generate a report for 10 users, ensure your SQL queries are optimized with appropriate indexes.

// Fetch only the required data for the specific user IDs
$targetUsers = DB::table('users')
    ->whereIn('id', [123, 456]) 
    ->get(['name', 'email', 'balance']);

// Pass this filtered collection to your PDF generator
$pdf->addPageFromHtml($htmlTemplate, $targetUsers);

Why Not Stick with Legacy PHPEExcel?

The decision to move away from phpexcel is not just about feature parity; it is about security and compatibility. Legacy libraries often contain unpatched vulnerabilities and do not support the latest Excel file formats (.xlsx) efficiently. If you are maintaining an older codebase, the immediate task is to refactor the imports and update dependencies to PHPSpreadsheet.

While the transition requires rewriting some logic—specifically how cells and styles are accessed—the long-term payoff is a stable foundation for your reporting pipeline. By handling file parsing with modern tools and batching your database operations, you create a scalable system that can handle thousands of users without hitting memory limits or performance bottlenecks.

Our network