最近ExcelをPHPに読み込んであれやこれややることが多いのだが、その中でハマった話。
主要部分だけピックアップするとこんな感じのスクリプト。
require('/lib/vendor/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
$file = '/var/www/file/test.xlsx';
$reader = new XlsxReader();
$spreadsheet = $reader -> load($file);
$worksheet = $spreadsheet -> getSheetByName('test');
echo $worksheet -> getCell('D60') -> getCalculatedValue();
こいつを走らせてみたところ
Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: test!D60 -> test!F53 -> test!F52 -> test!F49 -> Formula Error: Unexpected , in /var/www/html/lib/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:295 Stack trace: #0 /var/www/html/test.php(81): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue() #1 {main} thrown in /var/www/html/lib/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php on line 295
Formula Error=式のエラー、ということらしいがExcel上では特にエラーが出ていない。
ちなみにD60のセルに入っているのはこんな式だった。
=IF($C$4="",0,(VLOOKUP($C$4,$C$78:$T$101,14,0)*F53+VLOOKUP($C$4,$C$78:$T$101,15,0)*M53+VLOOKUP($C$4,$C$78:$T$101,16,0)*T53+VLOOKUP($C$4,$C$78:$T$101,17,0)*AA53)/VLOOKUP($C$4,$C$78:$T$101,18,0))
Cell.phpの該当箇所を見たのだが
public function getCalculatedValue(bool $resetLog = true)
{
if ($this->dataType === DataType::TYPE_FORMULA) {
try {
$index = $this->getWorksheet()->getParent()->getActiveSheetIndex();
$selected = $this->getWorksheet()->getSelectedCells();
$result = Calculation::getInstance(
$this->getWorksheet()->getParent()
)->calculateCellValue($this, $resetLog);
$this->getWorksheet()->setSelectedCells($selected);
$this->getWorksheet()->getParent()->setActiveSheetIndex($index);
// We don't yet handle array returns
if (is_array($result)) {
while (is_array($result)) {
$result = array_shift($result);
}
}
//以下略
と関数が定義してあるだけで要領を得ない。
色々ググってみたところ、Yahoo!知恵袋がヒット1。
getCalculatedValue()メソッドは通常、Excelのセルに記載された数式の計算結果を正しく取得します。しかし、一部の複雑な数式や特定の関数(例えば、VLOOKUPやINDIRECTなど)は、PhpSpreadsheetで正しく計算されない場合があります。
これはPhpSpreadsheetの制限によるもので、解決策は限られています。(以下略)
ということらしい。まあ確かに複雑な式だということは認める。ともあれPhpSpreadsheetの限界ということならどうしようもないので、元のExcelのほうで該当するセルをコピー→値のみペースト、という姑息な2手段で解決した。この場合getCalculatedValue()
である必要はないのでgetValue()
にスクリプトを変更して終わり。