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\") に置き換える