技術資料

PhpSpreadsheetでExcelを取り込むとき、必要なのは一部のシートや列だけなのに、ファイル全体を読み込んでいませんか。大きなファイルでは、使わないセルの読み込みもメモリを使う原因になります。

今回は、シートを指定する方法と、読み込みフィルターで列・行を絞る方法を説明し、同じXLSXで処理時間とピークメモリを比較します。書式を読み込まない設定も試します。こちらは日付などの扱いが変わるので、範囲指定とは分けて考えます。

読み込むシート・セル・情報を分けて考える

読み込み方法を変える前に、取り込み処理で何が必要なのかを整理しておきます。

「一部だけ読み込むこと」と「値だけ読み込むこと」は別の設定です。値だけでよいと思っていても、Excelの日付は数値と表示形式の組み合わせなので、書式が必要な場合があります。値・型・表示形式の違いは、PhpSpreadsheetで先頭ゼロを保持する記事も参考にしてください。

シート名を指定して読み込む

XLSXを読む場合は、XlsxのReaderを作り、load()の前に設定します。例えば、Ordersというシートだけなら次の形です。PhpSpreadsheetのオートローダーは読み込み済みとします。

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setLoadSheetsOnly('Orders');
$spreadsheet = $reader->load(__DIR__ . '/sample.xlsx');

複数ならsetLoadSheetsOnly(['Orders', 'Customers'])のように配列で渡せます。今回は中身がXLSXだと分かっているファイルを扱い、形式の自動判定は使いません。

列・行を絞る読み込みフィルター

セル単位で絞る場合は、IReadFilterを実装します。readCell()には列名、行番号、シート名が渡され、読み込みたいセルにtrueを返します。

final class OrderReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell(
        string $columnAddress,
        int $row,
        string $worksheetName = ''
    ): bool {
        return $worksheetName === 'Orders'
            && $row >= 1
            && $row <= 1001
            && in_array($columnAddress, ['A', 'C', 'E', 'F'], true);
    }
}

この例は、Ordersシートの1〜1001行、A・C・E・F列を対象にします。1行目は見出し、残りは1000件のデータという想定です。作成したフィルターを$reader->setReadFilter(new OrderReadFilter())で設定してから読み込みます。

元のセル座標は詰め直されません。C列はC列のままですし、100行目から読み込んでも1行目へ移動するわけではありません。また、これは値による検索条件ではなく、セルの位置で読み込む範囲を決める仕組みです。

比較用のXLSXを作る

ここからは、同じ入力で読み込み方法を比較してみます。PHP 8.3.36・PhpSpreadsheet 5.10.0のCLI用サンプルです。Docker上のLinux（x86_64）で実行を確認しました。

PhpSpreadsheetはComposerで導入済みとし、各PHPファイルをvendorディレクトリと同じ場所へ置きます。導入はComposerでPhpSpreadsheetをインストールする記事を参照できますが、過去記事の古いPHPバージョン設定は流用せず、対象版のPHP・拡張の要件を確認してください。

作るのは、Orders・Archive・Referenceの3シートです。各シートは見出し1行とデータ5000行、A〜Lの12列。全体で180,036セルになります。A列は文字列の商品コード、E列は日付の数値と表示形式、F列はB列とD列を掛ける数式です。

以下をcreate.phpとして保存します。サンプル専用の書き込み可能なディレクトリで実行してください。同じ場所にあるsample.xlsxは上書きされます。

<?php
declare(strict_types=1);

require __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
foreach (['Orders', 'Archive', 'Reference'] as $index => $name) {
    $sheet = $index === 0
        ? $spreadsheet->getActiveSheet()
        : $spreadsheet->createSheet();
    $sheet->setTitle($name);
    $sheet->fromArray([
        '商品コード', '数量', '商品名', '単価', '日付', '金額',
        '備考1', '備考2', '備考3', '備考4', '備考5', '備考6',
    ], null, 'A1');

    for ($row = 2; $row <= 5001; ++$row) {
        $sheet->setCellValueExplicit(
            'A' . $row,
            sprintf('%06d', $row - 1),
            DataType::TYPE_STRING
        );
        $sheet->setCellValue('B' . $row, 2);
        $sheet->setCellValue('C' . $row, '商品' . ($row - 1));
        $sheet->setCellValue('D' . $row, 100);
        $sheet->setCellValue('E' . $row, 46000 + (($row - 2) % 30));
        $sheet->setCellValue('F' . $row, '=B' . $row . '*D' . $row);
        foreach (range('G', 'L') as $column) {
            $sheet->setCellValue(
                $column . $row,
                $name . '-' . $column . '-' . $row
            );
        }
    }
    $sheet->getStyle('E2:E5001')->getNumberFormat()
        ->setFormatCode('yyyy-mm-dd');
}

$writer = new Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false);
$writer->save(__DIR__ . '/sample.xlsx');
$spreadsheet->disconnectWorksheets();
echo 'bytes=', filesize(__DIR__ . '/sample.xlsx'), PHP_EOL;

php -d memory_limit=512M create.php

4通りの設定で読み込む

次をread.phpとして保存します。引数で読み込み方法を切り替えます。

• all：3シートを全件読み込み
• sheet：Ordersだけ全件読み込み
• filter：OrdersのA・C・E・F列、1〜1001行だけ
• data：filterと同じ範囲で、書式情報を読み込まない

<?php
declare(strict_types=1);

require __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Shared\Date;

final class OrderReadFilter implements IReadFilter
{
    public function readCell(
        string $columnAddress,
        int $row,
        string $worksheetName = ''
    ): bool {
        return $worksheetName === 'Orders'
            && $row >= 1
            && $row <= 1001
            && in_array($columnAddress, ['A', 'C', 'E', 'F'], true);
    }
}

$mode = $argv[1] ?? 'all';
if (!in_array($mode, ['all', 'sheet', 'filter', 'data'], true)) {
    fwrite(STDERR, "mode: all / sheet / filter / data\n");
    exit(1);
}
$reader = new Xlsx();
if ($mode !== 'all') {
    $reader->setLoadSheetsOnly('Orders');
}
if ($mode === 'filter' || $mode === 'data') {
    $reader->setReadFilter(new OrderReadFilter());
}
if ($mode === 'data') {
    $reader->setReadDataOnly(true);
}

memory_reset_peak_usage();
$start = hrtime(true);
$spreadsheet = $reader->load(__DIR__ . '/sample.xlsx');
$seconds = (hrtime(true) - $start) / 1_000_000_000;
$peakMiB = memory_get_peak_usage(true) / 1024 / 1024;

try {
    $sheet = $spreadsheet->getSheetByName('Orders');
    if ($sheet === null) {
        throw new RuntimeException('Ordersシートがありません。');
    }
    $cellCount = 0;
    foreach ($spreadsheet->getAllSheets() as $loadedSheet) {
        $cellCount += count($loadedSheet->getCellCollection()->getCoordinates());
    }

    // 読み込み方式にかかわらず、必要な4004セルだけを比較する。
    $values = hash_init('sha256');
    $styles = hash_init('sha256');
    foreach (range(1, 1001) as $row) {
        foreach (['A', 'C', 'E', 'F'] as $column) {
            $address = $column . $row;
            $cell = $sheet->getCell($address);
            hash_update($values, json_encode([
                $address, $cell->getValue(), $cell->getDataType(),
            ], JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR) . "\n");
            hash_update($styles, $cell->getStyle()->getNumberFormat()
                ->getFormatCode() . "\n");
        }
    }
    $date = $sheet->getCell('E2');
    $dateValue = $date->getValue();
    $dateFormat = $date->getStyle()->getNumberFormat()->getFormatCode();
    $dateIsDate = Date::isDateTime($date);
    $result = [
        'mode' => $mode,
        'seconds' => round($seconds, 4),
        'peak_mib' => $peakMiB,
        'sheet_names' => $spreadsheet->getSheetNames(),
        'cell_count' => $cellCount,
        'values_sha256' => hash_final($values),
        'styles_sha256' => hash_final($styles),
        'A2' => $sheet->getCell('A2')->getValue(),
        'A1001' => $sheet->getCell('A1001')->getValue(),
        'E2_value' => $dateValue,
        'E2_format' => $dateFormat,
        'E2_is_date' => $dateIsDate,
        'F2_formula' => $sheet->getCell('F2')->getValue(),
        'F2_calculated' => $sheet->getCell('F2')->getCalculatedValue(),
    ];
    echo json_encode($result, JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR), PHP_EOL;
} finally {
    $spreadsheet->disconnectWorksheets();
}

php -d memory_limit=512M read.php all
php -d memory_limit=512M read.php sheet
php -d memory_limit=512M read.php filter
php -d memory_limit=512M read.php data

各コマンドは別のPHPプロセスで実行します。時間はload()の呼び出し直前から戻るまで。取得結果の確認、ハッシュ作成、数式の再計算は計測区間の外です。

ピークメモリはload()の直前にリセットし、直後に取得しています。ただし、memory_get_peak_usage(true)はPHPが確保したメモリの指標で、開始時から増えた量でも、OSが観測するプロセス全体のメモリでもありません。XMLやZIPを扱うライブラリの使用量などをすべて表す数値ではない点に注意してください。

読み込み時間とピークメモリの比較

各方式を1回ずつ実行してウォームアップした後、先に実行する方式を順番に変えながら、別プロセスで5回ずつ測定しました。以下は時間とピークメモリそれぞれの中央値です。コンテナはCPU上限1、メモリ上限768MiB、PHPのmemory_limitは512Mです。

今回の入力では、不要なシートを外すだけでも時間とメモリが減り、列・行を絞るとさらに減りました。一方、filterからdataへの変更では、表示桁の範囲でピークメモリは同じです。時間もほぼ同程度で、書式を読み込まなければ、その分だけ速くなるとは言えない結果でした。

全件の時間は5回で2.6548〜3.1770秒、filterは0.4285〜0.4619秒でした。共有文字列を多く含む今回のダミーXLSXは980,756バイトで、約0.94MiBです。圧縮されたファイルサイズが小さくても、読み込み後のPHPのメモリ使用量が同じ程度になるわけではありません。

これは繰り返し読み込んだ環境での結果で、初回やコールドキャッシュの性能ではありません。ファイルの内容や環境によって値は変わります。

値が同じでも、日付判定や数式の結果は変わる

values_sha256は必要な4004セルの座標・値・型を順番にハッシュ化したものです。styles_sha256は同じ範囲の表示形式を比較します。単に読み込みが成功したかだけでなく、必要なデータが一致するかも確認します。

必要な4004セルの座標・値・型のハッシュは、4方式すべてで一致しました。表示形式のハッシュはall・sheet・filterで一致し、dataだけ異なっています。A2の商品コードはどの方式も文字列の000001でした。

E2はどの方式でも数値の46000ですが、dataでは表示形式がなくなり、Date::isDateTime()もfalseです。日付の列だと取り込み側の仕様で分かっている場合と、Excelの表示形式から判定したい場合では、必要な情報が違います。

また、setReadDataOnly(true)は数式を計算済みの値へ置き換える設定ではありません。F2にはどの方式でも数式が残っていますが、filterとdataでは参照元のB2・D2を読み込んでいないため、PHPで再計算した結果は200ではなく0になりました。数式を保持できたことと、同じ計算結果を得られることは別ですね。

金額の再計算も必要なら、フィルターへB・D列などの参照元を含めます。別シートを参照する数式なら、そのシートも必要です。保存済みの計算結果を使う方法もありますが、今回は事前計算を無効にして生成しており、利用できる結果を用意していません。キャッシュの有無や新しさまで確認せず、読み込めた金額を正しいものとして扱わないようにします。

読み込み後の処理でもメモリを増やしすぎない

読み込み範囲を絞っても、その後でtoArray()を使ってシート全体を配列化すると、別の大きなデータをメモリへ作ることになります。今回のコードでは必要なセルだけ順番に調べ、全体の配列化は行っていません。

配列が必要なら対象範囲を指定するrangeToArray()も候補になりますが、A〜Fのような範囲には、今回読み込まないB・D列も含まれます。フィルター後のセル座標を前提に、取り出す範囲や計算・整形の有無を決めてください。

読み込みフィルターは、XLSXを行単位でストリーミング処理する指定ではありません。XLSXの共有文字列やXMLなどを解析する処理は残るため、セル数に比例して時間やメモリが減るとは限りません。少しずつ範囲を変えて何度もload()する方法も、ファイルを読み直す分の時間がかかります。

実際の取り込みでは、まず不要なシートを外し、次に列・行を絞る。書式を捨ててよいかは、その後で判断する。この順に比較すると、どの変更が効いたかと、何を取得しなくなったかを追いやすいかと思います。入力のサイズや処理時間にも上限を設け、読み込み対象を絞れば任意のファイルを安全に処理できるとは考えないようにしてください。

まとめ

今回の約18万セルのXLSXでは、読み込む範囲を絞ることで、PHPのピークメモリは次のように減りました。

• 必要なシートだけに絞る：119.5MiBから47.0MiBへ、全件読み込みと比べて約61%削減。
• さらに必要な列・行まで絞る：16.0MiBへ減り、全件読み込みと比べて約87%削減。読み込み時間も約2.71秒から約0.44秒になりました。
• 同じ範囲で書式も省く：今回は16.0MiBのままで、追加のメモリ削減は見られませんでした。

まず不要なシートを外し、次に列・行を絞るのがよさそうです。ただし、日付の判定に必要な書式や、数式の参照元まで除かないようにしましょう。削減量は入力によって変わるので、必要な取得結果が保たれているかとあわせて確認してください。

参考資料

• PhpSpreadsheet公式：シート指定・読み込みフィルター・データのみの読み込み
• PhpSpreadsheet 5.10.0：IReadFilterの定義
• PhpSpreadsheet 5.10.0：XLSX Readerの実装
• PHPマニュアル：memory_get_peak_usage
• PHPマニュアル：PHPが追跡するメモリの範囲
• PHPマニュアル：memory_reset_peak_usage
• PHPマニュアル：hrtime