MNB PHPExcelDeveloper Guide
v2.0.5
Documentation versions v2.0.5Current v2.0.4Maintenance v2.0.3Maintenance v2.0.0Initial v2 Compare versions →
Imports

Production-Safe Excel Upload and Import

Build one secure workflow that validates an HTTP upload, stores it privately, inspects the workbook, checks headers, previews SQL changes, and imports through PDO.

Updated

Full packagePDOUpload validationProduction
LevelAdvancedReading time20 minPackagemnb/mnb-phpexcel
MnbExcel::validateUpload()MnbExcel::read()ReadSession::inspect()ReadSession::dryRunImportToSql()ReadSession::importToSql()

What you will learn

  • Validate an untrusted HTTP upload before opening it.
  • Keep uploaded files outside the public web root under generated names.
  • Verify worksheet and headers before a dry-run and transactional import.

Before you start

  • Install mnb/mnb-phpexcel:^2.0.
  • Enable the PDO driver for the target database.
  • Create the target table and a unique index for the business key.

Define the import contract#

import-products.php
$requiredHeaders = ['SKU', 'Product Name', 'Price'];
$columnMap = [
    'SKU' => 'sku',
    'Product Name' => 'name',
    'Price' => 'price',
];

The spreadsheet contract, validation rules, SQL schema, and unique indexes must describe the same business data.

Validate and store the uploaded file#

upload-and-import.php
use Mnb\PHPExcel\MnbExcel;
use Mnb\PHPExcel\Support\MnbExcelException;

$validation = MnbExcel::validateUpload($_FILES['spreadsheet'], [
    'allowed_extensions' => ['xlsx'],
    'max_size_mb' => 100,
    'validate_package' => true,
    'max_zip_entries' => 5000,
    'max_uncompressed_size_mb' => 500,
]);

if (($validation['valid'] ?? false) !== true) {
    throw new RuntimeException('The uploaded workbook failed validation.');
}

$storageDirectory = dirname(__DIR__) . '/storage/uploads';
if (!is_dir($storageDirectory) && !mkdir($storageDirectory, 0770, true) && !is_dir($storageDirectory)) {
    throw new RuntimeException('Upload storage could not be created.');
}

$storedPath = $storageDirectory . '/' . bin2hex(random_bytes(16)) . '.xlsx';

if (!move_uploaded_file($_FILES['spreadsheet']['tmp_name'], $storedPath)) {
    throw new RuntimeException('The uploaded workbook could not be stored.');
}

Inspect the workbook and verify headers#

PHP
$workbook = MnbExcel::read($storedPath);
$inspection = $workbook->inspect();

$sheet = $workbook->sheetIfExists('Products');
if ($sheet === null) {
    throw new RuntimeException(
        'Products worksheet not found. Available: ' . implode(', ', $workbook->sheetNames())
    );
}

$session = $sheet
    ->withHeaderRow(1)
    ->withOptions([
        'skip_empty_rows' => true,
        'max_rows' => 250000,
    ])
    ->assertHasRows();

$firstRow = $session->first();
$actualHeaders = array_keys($firstRow ?? []);
$missingHeaders = array_values(array_diff($requiredHeaders, $actualHeaders));

if ($missingHeaders !== []) {
    throw new RuntimeException('Missing required headers: ' . implode(', ', $missingHeaders));
}

Dry-run before changing the database#

PHP
$preview = $session->dryRunImportToSql($pdo, 'products', [
    'map' => $columnMap,
    'unique_by' => ['sku'],
    'duplicate_strategy' => 'update',
    'batch_size' => 500,
]);

print_r($preview);
ReturnsarrayShapeDry-run summary; exact keys depend on importer result options
Expected output
Array
(
    [would_insert] => 1720
    [would_update] => 85
    [invalid] => 4
    [skipped] => 2
)

Review invalid rows and duplicate behavior before enabling the final import action.

Import with a safe application boundary#

PHP
try {
    $result = $session->importToSql($pdo, 'products', [
        'map' => $columnMap,
        'unique_by' => ['sku'],
        'duplicate_strategy' => 'update',
        'batch_size' => 500,
    ]);

    echo json_encode([
        'ok' => true,
        'result' => $result,
    ], JSON_THROW_ON_ERROR);
} catch (MnbExcelException|PDOException $error) {
    error_log(json_encode([
        'message' => $error->getMessage(),
        'file' => $storedPath,
    ], JSON_THROW_ON_ERROR));

    http_response_code(422);
    echo json_encode([
        'ok' => false,
        'message' => 'The workbook could not be imported.',
    ], JSON_THROW_ON_ERROR);
} finally {
    if (is_file($storedPath)) {
        unlink($storedPath);
    }
}

Production checklist#

  • Require authentication and CSRF protection before accepting uploads.
  • Enforce PHP, web-server, application, package, worksheet, row, and execution-time limits.
  • Use generated filenames and private storage; never trust a browser-supplied path.
  • Inspect the workbook and verify the expected worksheet and headers before SQL.
  • Use a dry-run or approval step for high-impact imports.
  • Create a database unique index for the keys used in duplicate handling.
  • Keep detailed exception context in server logs and return safe messages to users.
  • Delete uploaded and temporary files on success, failure, and expiry.
  • Run very large imports in a queue or separate worker process.