PhpSpreadsheetで、xls形式を読み込み、CSV出力を行うとSUM関数が実行されない。

掲題の件ですが、
現在、PhpSpreadsheetで、xls形式のExcelファイルを読み込み、その内容をCSV出力しようとしておりますが、
CSVの出力結果に、シート上に埋め込まれたSUM関数が実行されず、0で出力されるという現象が発生しております。

PHPのバージョンは、7.4.6となります。(XAMPP上に環境作成）

以下のことを行いました。
(1):setPreCalculateFormulas(true)を設定しても変化がありませんでした。
(2):xls形式のシートのセルに数値が入らない部分にすべて0をセットしても変化がありませんでした。
(3):PhpSpreadSheetをComposerで再度取り直し、再配置しても変化がありませんでした。
Composerが使えない環境にあるため、一旦Win10の仮想環境上にComposerの環境を作成し、
取得後、実行環境へ配置いたしました。
(4):Excelのシートの計算は「自動」になっています。

ただ、問題が起こっているxls形式のブックをxlsx形式に変換し、
実行すると、問題なくSUM関数が動作し、CSVにも出力されます。

本内容は、PHPExcelの使用が非推奨となったため、
PhpSpreadSheetに移植を行っているのですが、その過程で起こったものです。
csv出力以外にもxls形式の読み込み、出力はあるのですが、
こちらは問題なく動作しております。
PhpExcelで使用していたときは、問題なくSUM関数も動作しておりました。

コードは、以下のようになっております。
本内容は関数化されており、$arg_anscore_dataバラメータに
xls形式のExcelファイルのパスが渡されてくるようになっております。

//ライブラリのインクルード
require_once "../php/vendor/autoload.php";

try {
	//ファイルを読み込んでインスタンス化 (※Excel2003以前の形式)
	$l_reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');   //xls形式
	$l_spreadsheet = $l_reader->load($arg_anscore_data);
} catch (Exception $e) {
	print("失敗しました。". "\n");
	print($e->getMessage(). "\n");
	return;
	
}
// CSV書込クラス生成
$objWriter = new PhpOffice\PhpSpreadsheet\Writer\Csv($l_spreadsheet);

// 区切り記号（カンマ）
$objWriter->setDelimiter(',');
// 文字列の囲み文字（"）
$objWriter->setEnclosure('');   // この形にしないと空白行に""が入ってしまう。
// 改行コード
$objWriter->setLineEnding("\r\n");
// シートの位置
$objWriter->setSheetIndex(0);

$objWriter->setPreCalculateFormulas(true);

// 書込み処理
$objWriter->save("../text/getsurei/getsurei_sum/getsurei_sum.txt");

xlsx形式に変換して回避する手もあるのですが、
個数が多いため、本手段は回避したいところです。

どなたか、同様な現象に遭われた方がいらっしゃいましたら、
アドバイスいただければと思います。

以上、よろしくお願い申し上げます。

行動規範の内容に同意します

回答1件

ベストアンサー

Excelの代わりにLibreOffice Calcで下図のようなファイルを作りました。
D列でSUM()を使っています。

これで試したところ、CSVの内容は↓こんな感じです。

item,in,out,sum
apple,10,20,10
orange,20,0,20
banana,100,50,100

C列がカウントされてない感じですね。

そこで、D2セルの値だけを出力してみます。

php
1$sheet = $l_spreadsheet->getActiveSheet();
2$v = $sheet->getCell('D2')->getCalculatedValue();
3echo $v.PHP_EOL;    //=>  10
4$v = $sheet->getCell('D2')->getOldCalculatedValue();
5echo $v.PHP_EOL;    //=>  30
6$v = $sheet->getCell('D2')->getValue();
7echo $v.PHP_EOL;    //=>  =SUM(B2:B2)

何やら不思議な結果になってますね。
PhpExcel/PhpSpreadsheetあるあるかも。

式を再設定して、キャッシュをクリアすると、一応、回避できました。

php
1$sheet->getCell('D2')->setValue('=SUM(B2:C2)');
2$sheet->getCell('D3')->setValue('=SUM(B3:C3)');
3$sheet->getCell('D4')->setValue('=SUM(B4:C4)');
4\PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance($l_spreadsheet)->clearCalculationCache();

全セルの式を再設定しないといけないので、受け入れがたいかもしれませんが…。

投稿2021/05/03 01:14

gpsoft

総合スコア1323

yenomoto

2021/05/03 10:29

gpsoft様返信ありがとうございます。わざわざ試していただき、大変恐縮しております。 gpsoft様の方でも、同様な現象が起きているようですね・・・複数の環境で同様な現象が出ていると言うことは、 PhpSpreadsheetに何かありそうですね。セルの式の再設定で回避出来るとのこと、試していただきありがとうございます。ただこれだと、シートにSUM関数を入れた意味が薄れてしまうので、 CSV出力した結果を配列に入れて、PHP上で集計することで回避しようかとも考えています。

gpsoft

2021/05/03 14:02

> ただこれだと、シートにSUM関数を入れた意味が薄れてしまうおっしゃる通りですね〜 Writerは自作できると思うので、状況に応じてgetCalculatedValue()とgetOldCalculatedValue()を使い分けるような、賢いWriterを実装する、という手もあるかもしれません。現実的かどうか分かりませんが…。

yenomoto

2021/05/06 12:31

gpsoft様アドバイスありがとうございます。検討してみます。現状では、こうなってしまうようですので、本内容をベストアンサーとさせていただきます。

行動規範の内容に同意します