2013.01.17   |   PHP

Spreadsheet_Excel_Writerでの数式操作

PHPからExcelを扱うためのPearパッケージ
"Spreadsheet_Excel_Writer"
http://pear.php.net/manual/ja/package.fileformats.spreadsheet-excel-writer.php
があるのですが、数式を埋め込む際に苦労したのでメモしときます。

writeFormula(行,列,数式,(書式))
のように簡単に利用できるのですが、意外と制約があります。

個人的に大きく躓いたのがシートをまたぐ処理です。
通常のExcelなら
=Sheet2!A1
のようにすればSheet2のA1セルを参照できます。
しかしこのWriterを使って同じ事
$worksheet->writeFormula(0,0,"=Sheet2!A1");
をしてみると、なぜか数式でなく0が書き込まれてしまいます。
"='Sheet2'!A1"
のようにシート名をクオートしたりしてみましたが、
結局0のままで取得することが出来ませんでした。
存在しないシート名を入れたり、
数式のsyntaxを間違えたりするとエラーが出るので、
シート名を扱う仕組みは内蔵されていそうなのですが、
どうもうまくいきません。

いっそ文字列として出力してみればどうか、と思い
writeStringを使って出力してみました。
今度は正しく出力されるのですが、当然のことながら
数式としては解釈されません。
ただ、クオートとかがされているわけではないので、
一回セルを選択して確定すると数式として認識される、
という不思議な現象がおきます。
ならPHP側で解釈を行わず、文字列を数式として認識できるよう
出力するようにモジュール自体を書き換えてしまおうとも思ったのですが、
Writerコード内のバイナリの仕組みが今ひとつ掴めず断念。

次の手段としてevalみたいな関数がExcelにあれば、
このへん回避していけるんじゃないかと思いつき探してみました。
すると見事EVALUATEという関数を発見。
これで万事解決…と思ったのですが、ここにも罠が。
Spreadsheet_Excel_WriterではEVALUATE関数は登録されていないみたいで、
未定義の関数として弾かれてしまいます。…そんな。
登録すればいいんじゃないか、とももちろん思いましたが、
中みてみると関数に割り当てられた専用のコードが必要そうで、
またしても断念せざるを得ませんでした。

断念続きでかなり心折れましたが、
最後の手段として"シート名を引数に取る関数"を探してみることに。
するとうまい具合にADDRESSという関数を見つけました。
通常は座標をA1のような形式の文字列に変換する関数なのですが、
これの第5引数にシート名を取ることが出来ます。
これとINDIRECTというA1形式の文字列を解釈する関数を組み合わせると…!
$worksheet->writeFormula(0,0,"=INDIRECT(ADDRESS(1,1,1,1,\"sheet2\")");
やっと値を取得することができました。
やたら冗長な書き方ですが。

まとめ

sheet2!A1

INDIRECT(ADDRESS(1,1,1,1,\"sheet2\")
に置き換える