2015年08月12日

EXCEL NPOI の GetFormat のパラメータに設定可能なフォーマット文字列は Excel VBAでいうところの NumberFormat であって NumberFormatLocal じゃなかったという件と書式文字列についてわかったことをだらだらとまとめてみた

NumberFormatLocalとNumberFormatの違い:エクセルマクロ・Excel VBAの使い方

VBAで数値の書式設定を行うNumberFormatLocalプロパティ ってものがあるんですが、これ 見たことがある人はいると思いますが以下の設定です。
nf2015-08-12_223449.png

セルにはそれぞれ
123456
-1236456
0
と記入し、以下の書式文字列を設定すると 上記のように表示されます。
\#,##0_);[赤](\#,##0);"ゼロ"

書式文字列が長くなると、もはや手に負えなかったのですが 最近分かったのは書式文字列は
プラスの数値の書式;マイナスの数値の書式;ゼロの書式
という風にセミコロンで区切って最大3つ書けます。
つまり 上記例でいうと
123456 → プラスの書式 → \#,##0_) → \123,456
-1236456 → マイナス書式 → [赤](\#,##0) → (\ 1,236,456)
0 → ゼロ書式 → "ゼロ" → ゼロ

まずマイナスの書式から分析すると、
・赤字で
・3桁区切りで
・先頭に¥記号を付けて
・数値の両側を()で囲う
という書式です。

次にプラスの書式
・3桁区切りで
・先頭に¥記号を付けて
・マイナスの時につけた 閉じかっこ)の分 の位置をずらすために 閉じかっこ分の位置を空白に

最後にゼロの書式
これ、無くてもいのですが たとえば固定文字列の ”ゼロ” と書けば その文字がそのまま表示されます。

この辺のことを最近ようやく知ってエクセルすげーと思うようになりました。いまさらですが。


閑話休題。

というように書式はVBAでみるとNumberFormatというプロパティに格納されています。
最近NPOIも使ってエクセルファイルを作っていますが 書式設定にもこだわるようになった結果、いったいどんな書式を設定すれば思い通りになるのかをわかりあぐねていたところ
こういうライブラリーは英語圏のソフトウェアなのでNumberFormatLocalではなく NumberFormat の値をまねすればよい結果になるというところまでわかってきました。これらのプロパティは片方に突っ込めばもう片方は自動評価されるようです。
なので英語版で作って日本語の書式を気にする必要もないということです。

つまり、
range("G14:G16").NumberFormatLocal で得られる \#,##0_);[赤](\ #,##0);"ゼロ" という文字じゃなく

range("G14:G16").NumberFormat で得られる $#,##0_);[Red]($ #,##0);"ゼロ" がいいということです。特に色の指定については。
そこで NPOI で .GetFormat( "$#,##0_);[Red]($ #,##0);""ゼロ""" ) するわけですが どうしたことか
作ったファイルを実際に開くと通貨記号が $ のまま表示されます。色も出ない。
これはおそらく EXCEL VBA では勝手に色指定や通貨記号の変換を NumberFormat と NumberFormatLocal の間でやっているのでしょうね。
対してNPOIでは正しいものを設定しないといけないわけか。

結局 NPOI では \#,##0_);[赤](\ #,##0);"ゼロ" を実現するために
.GetFormat( """\""#,##0_);[Color3](""\"" #,##0);""ゼロ""" ) としました。
これをNPOIで設定したセルの書式をエクセルにて 見直すと [Color3]が[色3] に置き換わって見えます。ここらあたりがEXCELのローカライズの凄さかな(ローカライズが微妙なところはいくつもあるけどそれはさておき)。


本の虫: Lenovoのファームウェアがファイルシステムを改ざんするクソ仕様なので絶対に使ってはいけない
→HDDを暗号化していればBIOSからそんな真似はできないね。


そして、書式設定をもっと詳しく掘り下げたい場合はこちらがお勧め
Custom Number Formats, Date & Time Formats in Excel & VBA; NumberFormat property
ココを見て初めて知ったのが Number Scaling の コンマ, の役割。
書式の最後に,を重ねることによって1000単位で割り算した結果を表示してくれる。
123456という数字に 0, という書式を付けると 123 と表示される。0.####,, だと 0.1235 と表示される。

さらに / (分数書式)
0.5 という数字に #/# という書式を付けると 1/2 (最も近い分数 、この場合ジャストの分数) で表示される。

セルに文字を充填する*【アスタリスク】
これは正直使いどころが分からないが 0.5 という数値に 0.0*= という初期を付けると 0.5======= とセル幅いっぱいに = が充填される。

4番目の書式は文字列だった!
サイトからの抜粋になるけども以下のようにすると
"[Blue]#,###.00; [Red](0.0#); [Green]0.00; [Magenta]@"
文字列はマゼンタ色(Magenta)で表示される。

これらはあくまでも数値そのものをいじらずにセル上での表示の方法だけを制御しているってこと。
ここまでできると、「条件付き書式」でなくても簡単な色付け、書式わけができてしまうね・・・・

表示書式が微妙に高機能なせいで条件付き書式でできるところもカバーしてしまいどっちで設定するのがいいのか悩ましい場面もありますが知っておいて損はないでしょう。
この記事へのトラックバックURL
http://blog.sakura.ne.jp/tb/161349885
※言及リンクのないトラックバックは受信されません。

この記事へのトラックバック
この記事へのコメント
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。

・おすすめ楽天ショップ1:trendyimpact楽天市場店
・おすすめサプリショップ:iHerb.com
・おすすめ楽天ショップ2:上海問屋
Powered by さくらのブログ