EXCEL列幅を徹底解明する

開発

仕事で Apache POI を使っているのだけれど、getColumnWidth や getDefaultColumnWidth の返す値が EXCEL のそれとはまったく異なる値を返すので大変困ってしまった。仕方なくEXCEL VBA を使ってフォントやサイズを変更しながら値を出力してみたのだが、線形に変化していないため、どういうロジックでフォントから列幅を導出しているのか全然わからない。

POIのjavadoc には'0'の文字列が8個入る幅と書かれているのだが、日本語環境のMS Pゴシックが指定されたEXCELでは 8.38 が返ってくる。５時間近く対照表と格闘しようやく実際のEXCELと同じ結果になるロジックを突き止めたのでここに書きとめておく。グーグル先生に問い合わせても情報が見つからなかったので、結構レアな内容なのかも。

まず、EXCELの列幅に影響を与えるプロパティは以下の四つ。

  • デフォルトフォント（VBA では Application.StandardFont。日本語環境のデフォルトは MS Pゴシック）
  • デフォルトフォントサイズ（VBA では Application.StandardFontSize。デフォルトは 11ポイント）
  • デフォルト列幅[文字数]（VBA では Sheet#StandardWidth。デフォルトはフォントにより異なる）
  • 列幅[文字数]（VBA では Range#ColumnWidth）

まず、列幅[文字数]から列幅[ピクセル]への変換は次の手順を踏むことでできる。

  1. デフォルトフォント、デフォルトフォントサイズで'0'を表示した場合の文字幅[ピクセル]を取得する（参考：StandardWidth Property）
    • これ自体は、GDIなどを使う必要がある。ピクセルなので整数値となる。MS Pゴシックの場合は、8px。
    • 資料によっては '0'〜'9'のうち最大の文字幅[ピクセル]となっているものもあったが、'0'と'9'の文字幅が異なるフォントが見つけられず検証できなかった（例: Open XML 仕様）。
  2. 次の式に従い、基準列幅[ピクセル]を求める。なお、計算は32bit整数型で行い端数は切り捨てる。
    • 基準列幅[ピクセル] = ( ( ('0'文字幅[ピクセル] - 1) / 4 + 3 ) * 5 / 4 ) * 2 + '0'文字幅[ピクセル] - 5　（※6/7 式が正しくなかったので修正しました）
  3. 列幅[ピクセル]は次の式で求めることができる（ROUND関数の第２引数は丸めの小数点位置）。

列幅[ピクセル]から列幅[文字数]への変換も逆にするだけで求まる。

  1. 列幅[文字数]は次の式で求めることができる。

EXCEL上で明示的に横幅を指定した場合は EXCEL ファイル上に列幅[文字数]が保存されるため、POIでも上述の方法で列幅[ピクセル]を求めることができるが、そうでない場合はフォントサイズなどから導出する必要がある。

デフォルト列幅[文字数]は次の手順で求めることができる。

  1. シートにデフォルト列幅[文字数]が存在すればそれを使う。
  2. シートのデフォルト列幅[文字数]がない場合は 8 文字分にあたる列幅のピクセル数を求め、（EXCEL表示の高速化のため）8の倍数に拡張し、再度列幅に変換したものを用いる。式にすると次のようになる。
    • デフォルト列幅[文字数] = 列幅ピクセル文字数変換(CEILING(列幅文字数ピクセル変換(8), 8))

最後にVBAではRange#Widthで取得できる列幅[ポイント]の算出の仕方だが、これはピクセルを基準にすると簡単に求まる。

  1. 列幅[ポイント]は次の式で求めることができる。

POIのパッチを書きたい気もするが、ASFへのコントリビューションだと英語でやり取りしなきゃなあならないから気が重い……。

【2015/6/7追記】
POIにパッチを送付した。簡単に対応終わるかと思ったら、EXCEL内部に保存される値とColumnWidthの文字数が違う、というところから始まり、ひとつひとつ検証していった結果ずいぶん時間がかかってしまった。その挙句、一部のメソッドは最後まで誤差を解消することができなかったのは大変心残り（とはいっても1%くらいの差）。EXCELの列幅、どこまで奥が深いんだ……。

【2015/6/11追記】
上記のパッチを当ててビルドした jar をダウンロードできるようにしました。

※上記意外のファイルは、poi-3.12をそのまま使ってください。

【2015/12/2追記】
最新の 3.13 に対してパッチを当てたものをダウンロードできるようにしました。