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 に対してパッチを当てたものをダウンロードできるようにしました。

「経済政策で人は死ぬか?: 公衆衛生学から見た不況対策」を読む

経済

経済問題は複雑だ。それは事実であり、認めざるを得ない。どのような経済現象や経済政策であっても、良い効果、悪い効果のどちらもが存在する。とは言っても、それぞれの効果の程度には違いがあり、1円を拾う程度の好影響と洪水に巻き込まれるような悪影響のような違いが出ることもある。だから、経済現象や経済政策を語る上では、効果の列挙よりも一番影響の大きいのはどの効果なのかが重要となる。

政策を売り込む経済学者は、しばしばこの影響の程度を歪めて伝える。自説に合った効果は強く喧伝し、その他の効果は語らず無視する。過去にインフレ目標を設定することがハイパーインフレに繋がったことは一度もないのに、「理論的には」ハイパーインフレになる可能性を否定出来ないと真顔で語る本職の経済学者は何人もいた（彼らは、私よりも遥かに頭が良い正統的な経済学者である）。

今日紹介する「経済政策で人は死ぬか？」の中でも、不況が死者を減少させる事実について説明されている。不況になると、人々は消費を抑え外出を抑えるため、自動車事故が減るためだ。だが、死者を減らすために不況を起こそうと思う人はいない。不況が発生すると多くの人が賃金の低下や失業を通じて大きな不幸を被ることになる。

以前聞いた話では、失業すると余暇が増えるため就業時よりも肉体的には健康になるそうだ。だからといって、健康のために失業を望む人はそう多くはないだろう。失業することで生活費が不足するだけではなく、社会から不適合者として扱われることで精神的にも追い詰められていく。

これらの問題はわかりやすい。では、経済が不況に陥った場合、財政の悪化による経済破綻を避けるため緊縮政策にとるべきか、それとも、悪化した財政は放置し当面の間は財政政策やセーフティネットの拡充など緩和政策をとるべきか、という問題ではどうだろうか。

この「経済政策で人は死ぬか？」は、この緊縮政策か緩和政策か、といった問題に過去の事例から迫った意欲作となっている。結論から言えば、不況下での緊縮政策は全然ダメだった。

思い切った緊縮策が不況に歯止めをかけることを示すデータはなく、数字はむしろその逆を示している。つまり緊縮策によって失業率がさらに上がり、消費がますます落ち込み、経済がいっそう減速したと解釈できるデータばかりである。

そして、経済はさらなる危機に陥り、人が死ぬ。著者らは語る。

研究を重ねた結果わかってきたのは、健康にとって本当に危険なのは不況それ自体ではなく、無謀な緊縮政策だということである。

緊縮政策の結果、人々のセーフティネットである失業対策予算や健康保険、医療補助、住宅補助が大幅に削減され、不況で弱った人々が社会に戻る道を閉ざしてしまったのだ。セーフティネットがあれば職場に戻り生産活動に戻れていたかもしれない人々も、精神や体を病み生涯に渡り生産活動ができなくなってしまう。このような影響は長期に渡って国の経済を蝕む（日本においても不況時に就職できず、そのまま30代、40代になってしまった元若者たちがこれから問題として持ち上がってくるはずだ）。

その一方で、セーフティネットの拡充と言った緩和政策は、財政赤字を悪化させるように思えるが、実際にはそうではなかったようだ。著者らは大恐慌下で行われたニューディール政策についてこのように語る。

ニューディール政策全体で言えば、その額がGDPの20パーセントを超えることはなかった。しかしそれは死亡率だけではなく、景気回復の加速にも役立ったのである。アメリカ人の平均所得はニューディール政策の開始後すぐに9パーセント上昇し、それが消費を押し上げ、雇用創出の下支えにもなった。この政策に反対だった人々は財政赤字と債務増加の悪循環を警戒したが、結果的にはこの政策が景気回復を助け、債務も減る方向へと動いた。

同書では、ソ連崩壊後の市場経済への移行、アジア通貨危機での各国の対応、ギリシャの緊縮政策と言った、不況での失敗策が語られるが、そこに出てくるのは無知な政治家だけではない。著名な経済学者も多数登場する。

（ソ連崩壊後の）改革のスピードについては経済学者の間でも意見が割れていた。ある人々は「ショック療法」と呼ばれる急激な市場経済導入を主張した。たとえばアンドレイ・シュライファー、スタンレー・フィッシャー、ローレンス・サマーズ、ジェフリー・サックスなどで、ハーバード大学の経済学者が中心だった。

そして、最終的には、共産主義の復活を阻止しなければならないというイデオロギーやサックスらのいるIMFの意向もあり次のような状況になった。

ローレンス・サマーズもこう述べている。「経済学者の意見が一致することはないとよく言われるが、旧ソ連・東欧諸国に対する助言では驚くほどの一致が見られた」

彼らの主張は取り入れられ、結果的にロシアは暗黒時代に陥った。一人あたりGDPは30%低下し、2%だった貧困率が40%を超え、平均寿命も5歳以上縮まった。彼らは「長期的には」急激な改革の方が良い結果をもたらすと主張していたが、ロシア人男性の平均寿命は現在もなお改革前の水準に戻っていないそうだ。

私もソ連崩壊後のロシアの状況は聞き知っていたが、それはある程度は国の崩壊に伴う必然だと思い込んでいた。しかし、実際にはそうではなかったようだ。ポーランドやチェコのように、国内の抵抗があり改革のスピードがゆるやかに行われた国では、そのようなことはまったく起きなかったのだ。

今、まさに日本でも財政再建のために消費税再増税するのか、景気悪化を考慮し取りやめるか、という議論が盛り上がっている。日本のほどんどの経済学者は消費税に賛成の立場に立っている（震災時でさえ増税を主張していたのだから、当然だろう）。

その中には、業績もある正統な経済学者も多くいる。「経済学者の意見が一致することはないとよく言われるが、消費税に対する助言では驚くほどの一致が見られた」そして、結果的に日本は暗黒時代に……などということは勘弁願いたいものであるが。

経済政策で人は死ぬか?: 公衆衛生学から見た不況対策

経済政策で人は死ぬか?: 公衆衛生学から見た不況対策

Javaのクラス内に存在する定数値をメソッド毎に出力する

開発

Javaのクラス内が依存しているフィールド、メソッドの一覧を表示する - A.R.N [日記] の続き。

Javaの各クラス内で書かれたSQL文を抽出したかったので、Javassist を使って定数を抜いてみた。
本当は、スタック位置をきちんと考えてメソッドの引数としてどの定数が設定されているかまで調べたかったけれど、スタックマシンを実装するに等しくなってしまうのでそこは断念。

ClassPool cp = ClassPool.getDefault();
CtClass cc = cp.get("test.Sample");

for (CtBehavior cb : cc.getDeclaredBehaviors()) {
  MethodInfo info = cb.getMethodInfo2();
  ConstPool pool = info.getConstPool();
  CodeAttribute code = info.getCodeAttribute();
  if (code == null)
    return;

  CodeIterator i = code.iterator();
  while (i.hasNext()) {
    int pos = i.next();
    int opecode = i.byteAt(pos);

    switch (opecode) {
    case Opcode.LDC:
    case Opcode.LDC_W:
    case Opcode.LDC2_W:
    case Opcode.ACONST_NULL:
    case Opcode.ICONST_0:
    case Opcode.ICONST_1:
    case Opcode.ICONST_2:
    case Opcode.ICONST_3:
    case Opcode.ICONST_4:
    case Opcode.ICONST_5:
    case Opcode.FCONST_0:
    case Opcode.FCONST_1:
    case Opcode.FCONST_2:
    case Opcode.BIPUSH:
    case Opcode.SIPUSH: {
      Object value;
      if (opecode == Opcode.ACONST_NULL) {
        value = null;
      } else if (opecode == Opcode.LDC) {
        value = pool.getLdcValue(i.byteAt(pos + 1));
      } else if (opecode == Opcode.LDC_W) {
        value = pool.getLdcValue(i.u16bitAt(pos + 1));
      } else if (opecode == Opcode.LDC2_W) {
        value = pool.getLdcValue(i.u16bitAt(pos + 1));
      } else if (opecode == Opcode.ICONST_0) {
        value = 0;
      } else if (opecode == Opcode.ICONST_1) {
        value = 1;
      } else if (opecode == Opcode.ICONST_2) {
        value = 2;
      } else if (opecode == Opcode.ICONST_3) {
        value = 3;
      } else if (opecode == Opcode.ICONST_4) {
        value = 4;
      } else if (opecode == Opcode.ICONST_5) {
        value = 5;
      } else if (opecode == Opcode.FCONST_0) {
        value = 0.0F;
      } else if (opecode == Opcode.FCONST_1) {
        value = 1.0F;
      } else if (opecode == Opcode.FCONST_2) {
        value = 2.0F;
      } else if (opecode == Opcode.DCONST_0) {
        value = 0.0;
      } else if (opecode == Opcode.DCONST_1) {
        value = 1.0;
      } else if (opecode == Opcode.BIPUSH) {
        value = i.byteAt(pos + 1);
      } else {
        value = i.s16bitAt(pos + 1);
      }
      System.out.print(cc.getName());
      System.out.print('\t');
      System.out.print(cb.getLongName());
      System.out.print('\t');
      System.out.print("stack");
      System.out.print('\t');
      if (value instanceof String) {
        System.out.print('"');
        System.out.print(value);
        System.out.print('"');
      } else {
        System.out.print(value);
      }
      System.out.println();
      break;
    }
    }
  }
}

Struts1 の ClassLoader 脆弱性問題について

開発

Apache Struts 2の脆弱性が、サポート終了のApache Struts 1にも影響

Struts1なんて星の数ほどのJava開発者がソースコード読んでたはずなのに、よく今まで誰も気づかなかったものだと思う。「目玉の数さえ十分あれば、どんなバグも深刻ではない」という言葉は多くの人が思っていたほど堅牢なものではなかったようだ。

なぜ、わざわざこのようなエントリを書き起こそうかと思ったかというと、同じ問題がJSONICでも起こるのではないかと一瞬冷や汗が出たからである。が、結論から言うと問題はない。

まず、今回の問題だが、 aaa.bbb.ccc といった式言語的な表現を aaa.getBbb().setCcc(value) のような Java プロパティアクセスに展開するような仕組みが用意されていると、すべてのオブジェクトのベースとなる java.lang.Object が持つ getClass().getClassLoader().setXxx(value) にアクセス出来てしまうという点にある。

このような仕組み自体は Servlet系のフレームワークで持たないものなど存在しないと思われるので、本当に問題が起きないのか検証が必須と思われる。すでに SAStruts については問題ないとの報告が出ているが、他のフレームワークも検証した方がよい気がする。

さて、JSONICではなぜ問題が発生していなかったかと言うと、JSONICでは ignore や limit メソッド内で java.lang.Object のプロパティやメソッドを無視するよう書かれていたりするのである。なので、問題はない、とは言ったものの、ignoreやlimitをオーバーライドして無視する処理をスキップしてれば問題は発生する可能性が出てくる。が、それは明示的に書かれた処理なのであるから、開発者の意図した動作と考えるべきだろう。

まぁ、ともかく（私は）ひと安心したということで。

[追記] 前述の通り普通の利用方法をする分には問題はないのではあるが、やはりより安全側に倒した方がよい気がしてきたので以下の対応を入れたいと思う。

  • BeanInfo で以下の条件に当てはまるクラスのメソッドはプロパティ扱いしない（メソッドとしては認識される）
    • java.lang.Object で定義されている（＝getClass()はプロパティ扱いされない）
    • java.lang.Class で定義されている
    • java.lang.ClassLoader およびその子クラスで定義されている

[さらに追記] 対応を入れて 1.3.4 としてリリースしてみた。

なお、いろいろ考えた結果「java.lang.ClassLoader およびその子クラスで定義されている部分」については対応を止めることにした。ClassLoaderがダメなら、ThreadやらSecurityManagerやらRuntimeやらいろいろ除外する必要があるし、config系だと必要な場面もあるだろうと考えた末の結論である。外部から値を受け取る Bean にシステム系のオブジェクトを設定した場合、脆弱性が発生するがそれは開発者側で意識すべき問題だろう。

履歴管理されているマスタの空白期間を埋めるSQL

開発

履歴管理されているマスタ（キー項目に対し適用開始日、適用終了日を持ち行が複数発生するマスタ）を使うようなシステムで帳票出力時に名称が出てこないといった問題がしばしば起こる。原理的にはトランザクション上にある日付データで参照すれば、入力時点では存在していたはずなのだから、そのようなことは起こりえないはずだが、次のような場合を考えればそうとも言えない。

  • 該当のマスタやトランザクションの発生元が異なるシステムであり、整合性を前提とできない
  • 移行の時点で整合性が取れていないが、システム側の都合で日付を修正できない
  • 運用の関係上、データの適用期間を手作業でメンテナンスが発生する

たしかに入力チェックには適用期間付きのマスタは有効である（特に異なる会計期間での入力が重複するような場合など）が、表示するという用途ではデメリットも大きい。

例えば、月次の残高データと履歴管理されているマスタを結合することを考える。さて、１月のデータはどのマスタと結合すればよいのだろうか。月初のマスタと結合すると月中に生まれたマスタとは結合できない。月末のマスタと結合しても、月中に廃止となったマスタとはやはり結合できない。

このような場合、単に名称を出したいだけであれば、履歴の空白期間を前後のデータで埋めたマスタを作り出せれば解決できる。

というわけで本日の話題は、「履歴管理されているマスタの空白期間を埋めるSQL」の作り方について説明しよう（前置きが長すぎ）。

空白期間は本来存在しないわけであるから、何らかの基準を設ける必要がある。

  • 原則として、適用終了日を伸ばして次の適用開始日にくっつける
  • 最初の適用開始日より前のレコードは存在しないので、最初の適用開始日は十分に昔、例えば 1900年1月1日とする
  • 最後の適用終了日は十分に先の日付、例えば 3999年12月31日にする

さてこのような結果を得るにはどのようなSQLを組めばよいだろうか。実は分析関数を使うと比較的簡単に書ける。

SELECT
    社員コード,
    (CASE WHEN (ROW_NUMBER() OVER (
        PARTITION BY
           社員コード
        ORDER BY
           適用開始日
    ) = 1) THEN '19000101' ELSE 適用開始日 END) AS 適用開始日,
    NVL(TO_CHAR(TO_DATE(LEAD(適用開始日) OVER (
        PARTITION BY
            社員コード
        ORDER BY
            適用開始日
    ), 'YYYYMMDD') -1, 'YYYYMMDD'), '39991231') AS 適用終了日,
    社員名称
FROM
    社員マスタ
ORDER BY
    社員コード,
    適用開始日

ね？　簡単でしょ？（ボブの絵画教室風）

[03/15追記] 誤記載を修正しました。

主キー問題を整理する

開発

だいぶん前から延々とくすぶっているIDキー問題あるいはサロゲートキー問題なのですが、一時期下火になっていたのですが、また再燃している感もあります。それだけ奥深い問題なのかもしれません。

ただ、読んでみると同じような話題のように思えても意外に論点が錯綜しており、交通整理が必要なのではないかと感じます。私が見たところ

  • 有意キー vs 無意キー
  • グローバルキー vs ローカルキー
  • 公開キー vs 非公開キー
  • 複合主キーあり vs 複合主キーなし

というところが論点かな、と感じています。あまり一般的な用語法ではないところもありますが、個々人で微妙に定義が異なる気もするので、一旦この名前で呼ぶことにします。

まず、第一の論点「有意キー vs 無意キー」です。ここで言う有意キーとはキー自体が意味を持っている（キーの先頭３桁が品種を表すなど）というつもりで書いています。

第二の論点は「グローバルキー vs ローカルキー」です。これは、キーが全世界に通用するものなのか、システム内（あるいは社内）に閉じられたものなのかです。よくあるグローバルキーの例としてはメールアドレス、ISBN、郵便番号などが挙げられます。それに対しローカルキーは概ねどこかのシステムで採番されます。

第三の論点は「公開キー vs 非公開キー」です。非公開キー＝システム内部IDと考えていただければよいかと思います。システム内部で採番され、画面上に情報として表示されたり、画面から検索条件として指定されず業務的な役割を持たないキーが非公開キーです（URLに含めるなど、システム的な用途で公開されても業務的な役割を持たないのであれば非公開キーとみなします）。これに対し公開キーはデータを特定するために社内で流通しているコードです。例えば社員番号、取引先コードなどがそれに当たります。

第四の論点は「複合主キーあり vs 複合主キーなし」です。IDの使用と複合主キーなしはセットで語られがちですが、IDを使用しつつ複合主キーを使うという考えも取り得ます。

しばしば、自然キーという呼び方が出てきますが、1、2、4 の話が混在してしまい混乱を招く原因になっているのでは、と思っています。また、ID を使う場合でも、1、3、4 の議論が混在しているように思います。

[追記] なお、筆者の立場は「無意キー」「ローカルキー」「公開キー」「複合主キーあり」です。

HTML でリッチテキストエディタを実現する方法

開発

そんなの contenteditable でできんじゃん、と思っていたら全然そんなことなかったのでメモ。
まず編集可能な HTML 領域を作るには次の二種類の方法がある。

  • contenteditable="true"
  • designMode="on"

違いは contenteditable がある特定の要素の中だけを編集可能にするのに対し、designMode はドキュメント全体を編集可能にする。前者の方が使いやすいように見えるが、世にあるリッチテキストエディタは iframe 内のドキュメントに対し designMode="on" にする方法を使っている。

なぜか。それは選択範囲の扱いが難しいからだ。選択範囲は、IEであれば document.selection、その他のモダンブラウザであれば window.getSelection() で取得できるが、基本的にドキュメント全体が対象になるため、特定の要素内の選択範囲のスタイルだけを変更するといったことがとても難しくなる（ただし、ここまでは選択範囲の親要素が編集可能範囲の子要素であるか調べることで実現可能である）。

次に選択範囲を編集することを考えると、単に選択範囲内のノードを書き換えればよいだけのように思われる。しかし、例えば太字にするだけでも、現在選択中のテキストが bold か否かを判定して bold でなければ boldを指定したタグで囲む、そうでなければ解除するという面倒な処理をする必要があるうえに、現在選択中のテキストが bold であるかを判断することはとても難しい（text-weight: bold が指定されている直近の親要素を探すしかない）。

しかし、実は、IEにもその他のブラウザにも execCommand というコマンド発行のメソッドがあり、このメソッドを使うことでHTMLの加工ができる。例えば、document.execCommand('bold', false) を実行すると、スタイルエディタBボタンを押したような効果が得られる。

iframe と designMode と execCommand の三つを組み合わせることで、リッチテキストエディタの実現が可能になることがご理解いただけるだろう。

ここまで来れば、ほとんど完成したも同然であるが、実はこの方法だけでは IE で困った現象に出くわす。文字の範囲を選択して何らかのボタンを押してその中でコマンドを実行しようとすると、クリックした時点でフォーカスが移動してしまい、未選択の状態になってしまう。この問題へは、該当のボタンに unselectable="on" を付ければよいようだ。選択できないので、フォーカスが移らず想定通りの動きとなる。