Edited at

RubyXLで日付や通貨などのフォーマット(書式)を使いたい。

More than 1 year has passed since last update.

RubyXLはRubyでExcel(.xlsx)を扱うためのgem。


セルの書式の指定方法

公式 README


Modifying Cell Format

cell = worksheet[0][0]

cell.set_number_format '0.0000%' # For formats, see https://support.office.com/en-us/article/5026bbd6-04bc-48cd-bf33-80f18b4eae68
cell.change_text_wrap(true) # Makes the text in the cell to wrap.
cell.change_text_indent(1) # Indents the text in the cell by 1 level

0.0000%のところに好きな書式記号(下記)を書くだけですね。

シートにセルを追加しながら、書式を指定する場合はこちら:

cell = worksheet.add_cell(0, 0, 30)

cell.set_number_format('0.0000%') # 結果: 30.0000%

なお、代表的な書式記号は以下 (日付は後述)

書式記号
入力
表示
意味

#.##
1234.567
1234.57
第3小数点を四捨五入

#.#
1234.56
1234.6
第2小数点を四捨五入

#
1234.56
1235
小数点を四捨五入

#,###
#,##0
#,##
1234.56
1,235
どれも同じ効果
3桁区切り + 小数点を四捨五入

000000
1234.56
001234
6桁目まで表示

0.000
1234.56
1234.560
第3小数点まで表示

# "hoge"
1234.56
1235 hoge
好きな文字列を表示

書式記号
入力
表示
意味

0,
12345678
100
12345
0
単位:千

0,,
12345678
100
12
0
単位:百万

0.00,
12345678
100

12345.68
0.10
単位:千 + 第3小数点まで表示

#,
12345678
100
12345
 
単位:千 (0の場合非表示)

#,,
12345678
100
12
 
単位:百万 (0の場合非表示)

Excel書式記号の書き方はこちら


日付だとちゃんと動かない

# 動かないコード

cell = worksheet.add_cell(0, 0, Date.today)
cell.set_number_format('yyyy/m/d')

これだと、なぜかセルに日付が入らず空っぽのままになる


日付の設定の仕方

https://stackoverflow.com/questions/13104034/how-do-i-create-a-date-formatted-cell-using-rubyxl を参考にして解決しました。

このコードがちゃんと動きます。

cell = worksheet.add_cell(i, 0)   # 戻り値としてセルを返すみたい

cell.set_number_format('yyyy/m/d') # 結果例: 2018/7/15
cell.change_contents(Date.today)

なお、代表的な書式記号は以下

書式記号
意味

yy

00~99

yyyy

1900~9999

m

1~12

d

1~31

aaa
曜日
日~土

aaaa
曜日
日曜日〜土曜日

元号を使う場合は以下

書式記号
意味

g
元号
M、T、S、H

gg
元号
明、大、昭、平

ggg
元号
明治、大正、昭和、平成

e

1~

時間は以下

書式記号
意味

h

0~23

m

0~59

s

0~59

h:mm AM/PM
時刻
12:00 AM 〜 11:59 PM

月も分も小文字のmなんですね。

なお、m、d、h、m、s、eは、mm、dd、hh、mm、ss、ee (2文字)とすると

1桁の場合、先頭に0がつく。つまり、「1」が「01」と表示される。


他の記号は、Excel書式記号の書き方はこちら


通貨

セルの左端に¥を表示します。

cell = worksheet.add_cell(i, 2, 1080)

cell.set_number_format('¥* #,###') # 結果: ¥ 1,080

書式記号
通貨

¥* #,###
日本円、中国元

$* #,###
アメリカドル

€* #,###
ユーロ

£* #,###
イギリスポンド

₩* #,###
韓国ウォン

アスタリスク(*)は通貨記号を左寄せにしてくれます。(画像のように)

別に左寄せでなくていい場合はこれを除く。


他の書式

Excel書式記号の書き方はこちら」に載っていない書式コードを探す方法:


  1. Excelを開いて、適当なセルを右クリック

  2. 「セルの書式設定...」

  3. 「表示形式」タブにて、「分類:」から目当ての書式を選択

  4. 他のものをクリックせずに、「ユーザ定義」を選択

  5. 「種類:」の入力欄に表示されたコードをコピー

_ ¥* #,##0_ ;_ ¥* -#,##0_ ;_ ¥* "-"_ ;_ @_

これが目当てのコードです。

...なんか長くね? 

よく見ると4つのコードがセミコロン(;)で区切られています。

実は、値が正か負かなどで書式を切り替えることができるらしく...



  1. 正の値

  2. 負の値

  3. 値がゼロ

  4. 文字列

と、各場合に分けて書式記号を分けます。

例えばこんなことができます。

書式記号
入力
表示

¥ #,###
-1080
-¥ 1,080

¥ #,### ; ¥ -#,###
-1080
¥ -1,080

▲ #,### ; ▼ -#,###
-1080
▼ 1,080

別に表示を切り替えたくない場合は1つ目だけ使いましょう。

_ ¥* #,##0_ ;_ ¥* -#,##0_ ;_ ¥* "-"_ ;_ @_  

なら、

¥* #,##0 
これです。

多分ですが、アンダーバー(_)は、区切りを見やすくしているだけだと思います。

1つにするときはこれらを取り除かないとエラーになります。

以上です。


まとめ

RubyXLの記事を書くはずが、Excelの書式記号講座になってしまった。