また、えらく重箱の隅つつきな話です。
バグとして報告したいところだけど、やり方わからないからとりあえず書き留めとく。
現在行っている仕事で、「データをExcel出力」している個所がある。
まぁ、よく見るね、こういうの。
サービスの主要部分は node.js で作成している。
サーバー側で javascript を動かす仕組みだ。
でも、Excel 出力する部分は、PHP でやっている。
node に良いライブラリがなく、phpSpreadsheet というライブラリが良い、と担当者が判断したためだ。
（少なくとも、xlsx と xls の両方出力でき、細かく装飾されたシートを出力できる必要があった。
ただこれだけの条件で、できるライブラリは非常に限られている）
担当者が、と書いた通り、僕はこの部分の担当ではなかった。
でも、バグが報告され、その時担当者が忙しかったために、僕が原因を調べることになった。
具体的には、「文字列」として入力された、小数点を伴う数字を複数 Excel のセルに含めようとしたところ、
正しい値が入らずにぐちゃぐちゃになった。
最初は何が起きているかわからず、いろいろなデータを入れて試したところ、次のようなことが分かった。
・小数点を伴う数値と見なせる文字列を複数のセルに記載した場合、同じ整数部を持つ、先に入れたセルの内容が、後のセルの内容にコピーされる。
何を言っているのかわからないだろうから、実例。
0.1 / 1.1 / 0.2 / 1.2
という４つの数値があったとしよう。これは以下のようになる。
0.1 / 1.1 / 0.1 / 1.1
「同じ整数部を持つ、先に入れた内容が、後のセルにコピーされる」というのは、こういうことだ。
整数部が同じであれば全部同じ内容になってしまう。
数値としてみなせない文字列であれば、このような問題はない。
また、小数点を含まなければ問題はない。
数字だけの文字列でも
0120
のようなものも問題はない。
phpSpreadsheet は非常に高機能なライブラリだが、遅くてメモリ食いだ。
というのも、このライブラリは、「エクセルの読み書き」だけでなく、本当にスプレッドシートの中身を実現しようとしているため。
で、遅さをカバーするためにも、データはまとめて入れることが推奨されている。
fromArray というメソッドでデータをまとめて入れられる。
applyFromArray というメソッドで、セルの装飾データなどをまとめて与えられる。
そして、fromArray は高機能で、入れようとしたデータを自動的に識別し、正しく扱ってくれる。
= で始まるデータは、数式だ。セルの式をセットしてくれる。
数値として扱えるものは数値として入れてくれるし、そうでなければ文字列にしてくれる。
…ここで、文字列を与えたとしても、数値として扱えるものは数値になってしまう。
数値は、Excel 上「右寄せ」で表示される。文字列なら「左寄せ」だ。
文字列として入れたのに、右寄せになると格好が悪い。
そのため、担当者が作ったプログラムでは、データを入れた後で、セルに入っている値のデータを明示していた。
数値として入ったものであっても、「文字列」と指示すれば、文字列になってくれるようで、左寄せになる。
これは、Excel でいう「セルの書式設定」とは違うものだ。
セルの書式設定にも「文字列」とか「数値」があるのだけど、それはセル内部のデータを「書式設定のデータに変換して」表示する、という設定。
データの型を指定する、というのは、表示時に変換するのではなく、セルの中の値の型を指定するものだ。
そう、エクセルの値には型がある。恥ずかしながら、僕は今回の調査までそれを知らなかった。
phpSpreadsheet では、セルに入れたデータは、php としてのデータ型がある。
そして、それとは別に、セルの中の値の型を保持するようになっていた。
fromArray で入れた場合は、自動判別され、数値型であれば、php のデータとしても数値に変換され、セルの値の型も数値になった。
しかし、この後で「文字列型」と指定すると、php データは数値のまま、セルの値の型は文字列になった。
そして、出力の段階に至る。
これも知らなかったのだが、xlsx ファイルでは、セルの情報と、「文字列」情報は別に保持されている。
セルに文字列を入れる場合は、文字列へのポインタ（参照番号）が入る形、だそうだ。
これにより、同じ文字列が大量に入るような Excel ファイルは…非常に一般的だと思うが、データが圧縮されることになる。
ライブラリはセルの値の型が文字列型だった場合に、配列として対応表を作っている。
「文字列」に対して、「参照番号」を調べられる対応表で、出力時にこれを使って、同じ文字列には同じ参照番号を出力するのだ。
この時、「文字列」に対して、というのは、セルの値の型のことだ。
でも、実際の php のデータは、文字列とは限らない。
そこで、ライブラリのプログラムでは、「実際の php データが文字列か否か」で処理を分けている。
文字列の場合、ハッシュ関数を通して一意な数値を得て、それを引数として配列にアクセスする。
文字列ではない場合は数値なので、そのまま配列にアクセスする。
…ここにバグがある。
文字列ではない場合、数値ではあるが、整数とは限らない。
小数点を含む場合、php によって小数部分が切り捨てられ、整数化されたアクセスになってしまう。
結果として、小数点以下を含む値を文字列として扱おうとすると、同じ整数部を持つ値のコピーになってしまう。
回避方法。
fromArray でまとめて値をセットするのをやめればよい。
fromArray の中身は、setValue という関数を使い、ループで書き込んでいる。
この setValue は、入れられるデータを検査し、文字列であっても数値と見なせる場合は数値に変換する、などの高度な処理を行う。
これに対し、setValueExplicit を使うと、データを入れる際にそのセルの値の型を一緒に指定できる。
勝手な変換も行われない。
今回は、fromArray でデータを入れやすいように、データだけを配列としてあらかじめまとめてあった。
セルの値の型の指定は、後で行えるように別にまとめてあった。
なので、先にセルの値の型を指定してしまう。
セルに値が入っていなくても、型だけ指定できる。
その後で、自分で２重ループを作ってデータを入れる。
まず、セルの値の型を確認する。(getDataType でとれる）
型を指定していない場合は、文字列の "null" が返るので、この場合は setValue でデータを入れる。
null 以外が返っている場合は、データ型の指定があるので、setValueExplicit で型を指定してデータを入れる。
これで、勝手な型の変換はなくなり、想定していた型で入ることになる。
文字列型が指定されているのに内部が数値、などということもなくなり、おかしな挙動に悩まされなくなる。
phpSpreadsheet 、すごく動作が遅いし、ここに書いたような複雑怪奇な動作によるバグもある。
もっといいライブラリがあれば乗り換えるのだろうけど、
・xlsx 、xls の両方を出力できる。
・細かな装飾も行える
・計算式も含む、複雑な表を作れる。
などを満足するライブラリがなかなかないんだよね…
（PHP に限る必要はない。
go の excelize はかなり良い線行ってそうなので少し試してみたのだけど、「既定のフォント」指定はできるのに、「既定のフォントサイズ」が指定できない、というかなり基本的な部分でダメだったよ…）
同じテーマの日記(最近の一覧)
別年同日の日記
申し訳ありませんが、現在意見投稿をできない状態にしています。 |