phpによるサイズの大きなExcelデータファイルの読み込み

センサーデータを格納したExcelデータファイル等、サイズの大きなExcelファイル内のデータをphpで読み取ろうとしたときのメモになります。

下記の2つのライブラリを使用しました。

1. ライブラリのインストール

1.1. phpのライブラリを管理するComposerのインストール

$ mkdir composer
$ cd composer/
$ curl -sS https://getcomposer.org/installer | php
$ sudo mv composer.phar /usr/local/bin/composer
$ sudo chmod +x /usr/local/bin/composer

1.2. Excelファイルの操作のAPIを提供するライブラリPhpSpreadsheetのインストール

$ composer require phpoffice/phpspreadsheet

1.3. Excelファイルの操作のAPIを提供するライブラリSpoutのインストール

$ composer require "box/spout"

2. Excelデータファイルの読み込み

用意しようとしていたのはExcelファイル(拡張子はxlsまたはxlsx)とCSVファイル(区切り文字は「,」)内のセンサーデータファイル(サイズの上限は2MB)を読み取って処理するphpスクリプトです。

2.1. PhpSpreadsheetを使ってExcelとCSVデータファイルの中身を二次元配列に格納する下記のような関数を用意して動作確認をしました。

require_once('/path-to-composer/composer/vendor/autoload.php');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv as CsvReader;
use PhpOffice\PhpSpreadsheet\Reader\Xls as XlsReader;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;

function getExcelSheetData($targetFileName, $fileType) {

    $reader = null;

    if ($fileType == "csv") {
        $reader = new CsvReader();
    } else if ($fileType == "xls") {
        $reader = new XlsReader();
    } else if ($fileType == "xlsx") {
        $reader = new XlsxReader();
    } else {
        // Error Handling
    }

    $reader->setReadDataOnly(TRUE);

    try {
        $spreadSheet = $reader->load($targetFileName);
    } catch (Error | Exception $ex) {
        // Error Handling
    }

    // get data from active sheet
    $sheet = $spreadSheet->getActiveSheet();

    $row = 1;
    foreach ($sheet->getRowIterator() as $eachRow) {
        foreach($sheet->getColumnIterator() as $column) {
            $sheetData[$row - 1][]
                = $sheet->getCell($column->getColumnIndex() . $row)->getValue();
        }
        $row++;
    }

    $spreadSheet->disconnectWorksheets();
    return $sheetData;
}

getExcelSheetData関数の一つ目の引数\$targetFileNameでは読み込むファイルのファイル名がパス付きで指定され、二つ目の引数\$fileTypeではファイルの拡張子が指定されると仮定しています。

メモリ使用量を128MBまでに制限してphpスクリプトを動作させていたところ、サイズが約600KBで拡張子がxlsxのExcelファイルを読み込んだ際に下記のようなエラーが表示されました。

Webで検索して調べてみると、例えば、PhpSpreadSheetのWebページに1セルあたり1KBのメモリーを使用するとの記載がありました。
phpスクリプトに下記のようなコードを記述してメモリ使用量の最大値を128MBから512MBに増やしましたが、やはりメモリ不足で拡張子xlsxのExcelファイルの読み込みに失敗しました。

サイズが約1300KBで拡張子がxlsのExcelファイルと、それをCSVファイルにして保存したファイルを読み込んだ際には、メモリ使用量の最大値を128MBに制限していても問題なくファイルを読み込むことができていました。

2.2. そこで、拡張子がxlsxのExcelファイルを読み込む際には、下記のようにExcelファイル操作用の別のphpライブラリSpoutを使用するように書き換えました。

require_once('/path-to-composer/composer/vendor/autoload.php');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv as CsvReader;
use PhpOffice\PhpSpreadsheet\Reader\Xls as XlsReader;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;

use Box\Spout\Reader\Common\Creator\ReaderEntityFactory as SpoutReaderEntityFactory;

function getExcelSheetData($targetFileName, $fileType) {

    $sheetData = "";

    if ($fileType == "csv" || $fileType == "xls") {
        $sheetData = getExcelSheetDataWithPhpSpreadsheet($targetFileName, $fileType);
    } else if ($fileType == "xlsx") {
        $sheetData = getExcelSheetDataWithSpout($targetFileName, $fileType);
    } else {
        // Error Handling
    }

    return $sheetData;
}


function getExcelSheetDataWithPhpSpreadsheet($targetFileName, $fileType) {

    $reader = null;

    if ($fileType == "csv") {
        $reader = new CsvReader();
    } else if ($fileType == "xls") {
        $reader = new XlsReader();
    } else {
        // Error Handling
    }

    $reader->setReadDataOnly(TRUE);

    try {
        $spreadSheet = $reader->load($targetFileName);
    } catch (Error | Exception $ex) {
        // Error Handling
    }

    // get data from active sheet
    $sheet = $spreadSheet->getActiveSheet();

    $row = 1;
    foreach ($sheet->getRowIterator() as $eachRow) {
        foreach($sheet->getColumnIterator() as $column) {
            $sheetData[$row - 1][]
                = $sheet->getCell($column->getColumnIndex() . $row)->getValue();
        }
        $row++;
    }

    $spreadSheet->disconnectWorksheets();
    return $sheetData;
}


function getExcelSheetDataWithSpout($targetFileName, $fileType) {

    if ($fileType != "xlsx") {
        // Error Handling
    }

    $reader = SpoutReaderEntityFactory::createXLSXReader();

    try {
        $reader->open($targetFileName);
    } catch (Error | Exception $ex) {
        // Error Handling
    }

    // read data from "active" sheet
    foreach ($reader->getSheetIterator() as $sheet) {
        if ($sheet->isActive()) {
            $row = 1;
            foreach ($sheet->getRowIterator() as $eachRow) {
                $column = 1;
                foreach ($eachRow->getCells() as $cell) {
                    $sheetData[$row - 1][] = $cell->getValue();
                    $column++;
                }
                $row++;
            }
            break; // no need to read more sheets
        }
    }

    $reader->close();
    return $sheetData;
}

Spout v3.3.0は拡張子がxlsのExcelファイルの読み込みには対応していなかったため、拡張子がxlsxのExcelファイルを読み込むときだけSpoutを使用するように書き換えました。phpスクリプトを書き換えたところ、サイズが約600KBで拡張子がxlsxのExcelファイルを読み込んでもメモリ不足となることはなくなりました。

最大メモリ使用量を128MBに戻し、サイズが約1800KBで拡張子がxlsxのExcelファイルを読み込むテストもしましたが、問題なくファイルが読み込まれるのを確認しています。