PHPSpreadsheet · Google Sheets API · Excel

Spreadsheets, driven by code.

Hands-on PHP tutorials for working with Excel and Google Sheets — read and write .xlsx, convert files to JSON, stream downloads in the browser, and insert images, formulas, and styling. Every guide ships with code you can copy, run, and adapt.

Latest complete code

excel-to-mysql.php View article

<?php

require 'vendor/autoload.php';

use \PhpOffice\PhpSpreadsheet\IOFactory;

$pdo = new PDO('mysql:host=localhost;dbname=demo;charset=utf8mb4', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$inputFile = 'users.xlsx';

$reader = IOFactory::createReaderForFile($inputFile);
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFile);

$rows = $spreadsheet->getActiveSheet()->toArray();
$headers = array_shift($rows);

$sql = 'INSERT INTO users (id, name, email) VALUES (:id, :name, :email)
        ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email)';

$statement = $pdo->prepare($sql);

$pdo->beginTransaction();

try {
    $imported = 0;

    foreach ($rows as $row) {
        $record = array_combine($headers, $row);

        if ($record['id'] === null || $record['name'] === null) {
            continue;
        }

        $statement->execute([
            ':id' => (int) $record['id'],
            ':name' => (string) $record['name'],
            ':email' => (string) $record['email'],
        ]);

        $imported++;
    }

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();

    throw $e;
}

echo "Done. Imported {$imported} rows into users.\n";