Import Excel Data with PhpSpreadsheet and Export Database Data to Excel

Excel is an excellent data management tool. In the hands of someone who really knows how to use it, it can feel almost like a programming environment. But most people only use it for basic tables and simple formulas.

If a CMS can export data to Excel or import data from Excel, then the CMS and Excel can work together as a much more practical data-management system. This article introduces that workflow with PhpSpreadsheet. The original example came from a Laravel-based CMS, but the approach works in WordPress too once the framework-specific pieces are replaced.

First, install the PhpSpreadsheet library

Install the package in your theme or plugin project with Composer:

composer require maatwebsite/excel

Implement the import feature

Some data may already exist in Excel files. Entering it into a CMS one row at a time is repetitive work that can usually be automated. The core of an import flow is to read the spreadsheet, extract rows, and write those values to the database.

Writing one row at a time is not always the most efficient approach, so in larger imports you may want to optimize further and batch the writes.

try {
    $spreadsheet = PhpOfficePhpSpreadsheetIOFactory::load( $info_student );

    // $cells is an array containing the worksheet data.
    foreach ( $spreadsheet->getWorksheetIterator() as $cell ) {
        $cells = $cell->toArray();
    }

    // Remove the header row.
    unset( $cells[0] );

    foreach ( $cells as $row ) {
        // Transform and save each row to the database.
    }
} catch ( Throwable $e ) {
    // Handle the import error.
}

Implement the export feature

Exporting is also straightforward. First fetch the records to be exported, then loop through them and write them into a worksheet, save the resulting file, and return it to the user.

One practical concern is performance. If the dataset is very large, test whether the export may fail because of time limits or memory limits. If that becomes a problem, exporting in batches may be necessary.

$students = StudentRegister::query()
    ->where( 'school_id', $logged_user->school_id )
    ->where( 'form_id', $id )
    ->get();

try {
    $spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();
    $sheet       = $spreadsheet->getActiveSheet();

    // Fill header cells and export each student row.
} catch ( Throwable $e ) {
    // Handle the export error.
}

Automatically calculate Excel column letters

When there are only a few columns, hard-coding cell names like A1 and B1 is easy enough. But once a worksheet has dozens of columns, hard-coding becomes inefficient and error-prone.

A helper function that generates Excel column letters automatically makes the export code much easier to maintain.

function excel_header( $num = 0 ) {
    $arr = range( 'A', 'Z' );
    $no  = ceil( $num / count( $arr ) );

    $data = [];

    if ( $no <= 1 ) {
        for ( $i = 0; $i < $num; $i++ ) {
            $data[] = $arr[$i];
        }
    } else {
        for ( $i = 0; $i < count( $arr ); $i++ ) {
            for ( $j = 0; $j < count( $arr ); $j++ ) {
                $data[] = $arr[$i] . $arr[$j];
            }
        }
    }

    return array_slice( $data, 0, $num );
}

Although the code in this article originally came from a Laravel project, most of the spreadsheet logic is not tied to Laravel itself. Once the request-handling and data-fetching pieces are replaced with WordPress equivalents, the same ideas can be used in a WordPress project too.