exlapの追加サンプル
rubyによるExcelの操縦 〜 exlap関連の追加サンプル 〜
最終更新日： 2010/01/19
- 1. csvファイルの処理
- (1) 単純に１つのcsvファイルを１つのExcelファイルとして出力
- (2) 数字や日付スタイルを含め総て文字列としてワークシートに記録
- (3) 数値の表示形式を設定しながらcsvのデータを処理
- (4) 複数のワークシート各々をcsvファイルとして書き出す
- 2. 他のファイル形式での保存
- 3. ハイパーリンクに関する補足
1. csvファイルの処理
csvファイルは、Excelで直接読み込むことができます。
ただし、読み込んだ後で、「001」が単なる「1」と表示されるようになってしまったり、「123.0」が「123」になったりと、多少不満の残る点があります。
以下、そうした問題への対処方法について記します。
(1) 単純に１つのcsvファイルを１つのExcelファイルとして出力
「001」問題などに目をつむって、とりあえず単純に、１つのcsvファイルを１つのExcelファイルとして出力するためのサンプル 「csv01.rb」 を掲げます。
−−−− csv01.rb ここから
#! ruby -KS
# csv→xls変換。１つのcsvファイルを１つのExcelファイルとして出力
require "exlap"
# テスト用CSVファイルの書き出し
csv1 = <<'EOS1'
,csv01.rb用データ#1,単純なcsv→xls
001,"小数点以下が０の場合",89.0,"←元は89.0"
002,"負の小数点数",-3.00,"元は-3.00"
003,"分数","1/3","元は1/3"
004,"パーセント",50%,"実態は0.5"
EOS1
File.open("test1.csv", "w") {|fp| fp.print csv1}
csv2 = <<'EOS2'
,csv01.rb用データ#2,単純なcsv→xls
001,"元号形式の日付","平成２１年１０月３日","実態は2009/10/03"
002,"西暦の年月日","2010/1/7"
003,"年を省略した日付","3/10","元は3/10"
EOS2
File.open("test2.csv", "w") {|fp| fp.print csv2}
## main
csv_names = Dir.glob("*.csv") # カレントフォルダの総てのcsvファイルの名前
Exlap.new(*csv_names) {|wb|
ss = wb.select_sheet(1)
ss.range_autofit
outfile = wb.filename.sub(/\.csv$/i, ".xls")
wb.save outfile
wb.close
}
−−−− ここまで
上記スクリプトでは、まずテスト用のcsvファイル test1.csv と test2.csv を書き出します。
そして、mainの部分では、カレントフォルダにあるcsvファイルを１つづつExcelで読み込んで、そのファイル名の拡張子「.csv」を「.xls」に変更して、ファイルセーブしています。
この方法だと「001」問題は解決できませんが、とりあえずxlsファイルにすることができますから、あとは書式設定等で対応できるようになります。
(2) 数字や日付スタイルを含め総て文字列としてワークシートに記録
「001」問題に対処する簡単な方法は、csvファイルに書かれているデータを総て文字列としてExcelのワークシートに書き込む方法です。
下に、それを行うためのスクリプト 「csv02.rb」 を掲げます。
−−−− csv02.rb ここから
#! ruby -KS
# csv→xls変換。１つのcsvファイルを１つのワークシートに記録。
# 数字や日付スタイルを含め、総て文字列としてワークシートに記録
require "csv"
require "exlap"
# テスト用CSVファイルの書き出し
csv1 = <<'EOS1'
,csv02.rb用データ#1,総て文字列として書き出す
001,"小数点以下が０でも省略しない",89.0
002,"負の小数点数",-3.00
003,"分数","1/3","←Excelお任せだと１月３日になる"
004,"パーセント",50%,"←Excelお任せだと実態は0.5になる"
EOS1
File.open("test1.csv", "w") {|fp| fp.print csv1}
csv2 = <<'EOS2'
,csv02.rb用データ#2,総て文字列として書き出す
001,"元号形式の日付","平成２１年１０月３日","←Excelお任せだと実態は2009/10/03になる"
002,"西暦の年月日","2010/1/7"
003,"年を省略した日付","3/10","←Excelお任せだと３月10日になる"
EOS2
File.open("test2.csv", "w") {|fp| fp.print csv2}
## main
xl = Exlap.new
wb = xl.book_open "test.xls"
csv_names = Dir.glob("*.csv") # カレントディレクトリのCSVファイルの名前群
csv_names.each {|filename|
data = CSV.readlines(filename)
ss = wb.first_empty_sheet
ss.Name = "csv02_" + File.basename(filename, ".*")
for y in 1..data.size
row = data[y-1]
for x in 1..row.size
v = row[x-1].to_s
val = v == '' ? nil : "'" + v
ss.cell(y,x).Value = val
end
end
ss.range_autofit
}
wb.save
xl.quit
−−−− ここまで
上は、rubyの標準ライブラリcsvを利用して、csvファイルを処理しています。通常の方法でExcelによりcsvファイルを読み込むと、Excelの変換処理が行われてしまうのでそうしています。
ここでのポイントは、データの頭に半角のアポストロフィ「’」(\x27)を置くと、それが文字列としてExcelのワークシートに記録されるということです。
ss.Range("A1").Value = "'001"
とすれば、A1欄に「001」という文字列がセットされます。
ただし、これは文字列であって数値ではないので、後から数値として扱いたいと思った時に困るかもしれません。
日付についても、例えば、「平成２２年１月１４日」というデータをExcelにお任せでセットすると、実態は「2010/01/14」という日付データになります。表示形式は元号形式が自動的に設定されるので、Excelを起動して表示させると「平成２２年１月１４日」のままですが、実態は日付データです。なので、曜日を付加したいと思った時は簡単に付加できます。
しかし、「’」を付けて文字列としてワークシートに書き込むと、日付データでなく文字列になるため、曜日の付加などが行えなくなります。
(3) 数値の表示形式を設定しながらcsvのデータを処理
「001」を文字列でなく数値としてワークシートに書き込みつつ、表示形式(NumberFormatLocalプロパティ)を適当に設定することで、単に「1」でなく「001」と表示させるようにする、というやり方を採れます。
それを実現するためのスクリプト 「csv03.rb」 を下に掲げます。
−−−− csv03.rb ここから
#! ruby -KS
# csv→xls変換。１つのcsvファイルを１つのワークシートに記録。
# 数値の表示形式を設定しながらcsvのデータを処理
require "csv"
require "exlap"
# テスト用CSVファイルの書き出し
csv1 = <<'EOS1'
,csv03.rb用データ#1,表示形式の調整
001,"小数点以下が０でも省略しない",89.0
002,"負の小数点数",-3.00
003,"分数","0 1/3","実態は0.333……"
004,"帯分数","1 1/3","実態は1.333……"
005,"パーセント",50%,"実態は0.5"
EOS1
File.open("test1.csv", "w") {|fp| fp.print csv1}
csv2 = <<'EOS2'
,csv03.rb用データ#2,表示形式の調整
001,"元号形式の日付","平成２１年１０月３日","実態は2009/10/03"
002,"西暦の年月日","2010/1/7"
003,"年を省略した日付","3/10"
EOS2
File.open("test2.csv", "w") {|fp| fp.print csv2}
# 数値の表示形式を設定しながらワークシートにデータを書き込む
def set_data(cell, val)
fmt = nil # NumberFormatLocal の設定文字列をこれにセット
ii = ff = nil # ii:整数部, ff:小数部
val = val.to_s
if val =~ /^-?(\d[0-9,]*)\.(\d+)$/ # 小数点数
ii = $1
ff = $2
elsif val =~ /^-?(\d[0-9,]*)$/ # 整数
ii = $1
end
if ii
if ii =~ /^0/
fmt = ii.gsub(/\d/, "0")
else
fmt = ii.gsub(/\d/, "#")
fmt = fmt.sub(/\#$/, "0")
end
if fmt =~ /^[0#,]+([0#],[0#]{3})$/
fmt = $1
end
if ff
fmt = fmt + '.' + ff.gsub(/\d/, "0")
end
cell.NumberFormatLocal = fmt
end
cell.Value = val
end
## main
xl = Exlap.new
wb = xl.book_open "test.xls"
csv_names = Dir.glob("*.csv") # カレントディレクトリのCSVファイルの名前群
csv_names.each {|filename|
data = CSV.readlines(filename)
ss = wb.first_empty_sheet
ss.Name = "csv03_" + File.basename(filename, ".*")
for y in 1..data.size
row = data[y-1]
for x in 1..row.size
set_data(ss.cell(y,x), row[x-1])
end
end
ss.range_autofit
}
wb.save
xl.quit
−−−− ここまで
ここでのポイントは、set_dataメソッドです。このメソッドの中で、ワークシートに書き込むデータに応じて NumberFormatLocal を設定しています。
NumberFormatLocalが "000" ならば、３桁に満たない数値は、０からはじまる形式で表示されます。
"###.00" ならば、小数点以下が０であっても２桁までは表示します。
こうしたルールを利用して「001」問題を解決するようにしているのが set_data メソッドです。もっと効率のいい書き方があるはずと思いながら、とりあえずベタな感じで書いてあります。
なお、csvデータをExcelの変換ルールに合わせる上でポイントになる点をいくつか上げておきます。
- 分数： 「1/3」だと「１月３日」になってしまうので、分数にするためには「0 1/3」とする必要があります。0の後に半角スペースを置きます。こうすると、日付でなく数値(0.333……)になります。
- パーセント： 「50%」だと、表示は「50%」のままですが、実態として数値の 0.5 がセットされます。それを避けるには「'50%」などとして文字列にします。
- 日付： 「平成２２年１月１４日」あるいは「2010/1/14」だと、日付データとしてセットされます。両方とも実態は同じ「2010/01/14」ですが、前者は表示形式として元号形式が設定されるので、表示は「平成２２年１月１４日」のままです。
- 年を省略した日付： 「1/3」だと、日付データとしてセットされます。表示は「１月３日」となりますが、実態は「2010/01/03」です。年は現在の年が補われます。
「1/3」を日付でなく分数として扱うことが多い場合は、set_dataメソッドを書き換えて、分数と思われる記述パターンに「0 」を補ってやる必要があります。サンプルでは、分数に関する変換は行っていません。
上記サンプルでは、csvファイルの読込みにrubyの標準ライブラリのcsvを使っていますが、速度を気にする場合は、例えば LightCsv のようなライブラリを使うといいかもしれません。
LightCsvを利用する場合は、
require "csv" → require "lightcsv"
CSV.readlines → LightCsv.readlines
と書き換えればＯｋです。
(4) 複数のワークシート各々をcsvファイルとして書き出す
この項の最後に、複数のワークシート各々をcsvファイルとして書き出すためのサンプル
「csv04.rb」
を掲げます。
サンプルは、test.xlsを開いて処理するようになっていますが、適当なxlsファイルがなければ、csv02.rb または csv03.rb で作られる test.xls を用いて下さい。
−−−− csv04.rb ここから
#! ruby -KS
# 複数のワークシート各々をcsvファイルとして書き出す
require "exlap"
Exlap.new("test.xls") {|wb|
snames = wb.sheet_names - wb.empty_sheet_names # 空でないシート
snames.each {|sn|
ss = wb.select_sheet(sn)
ss.SaveAs({'Filename'=>"#{wb.filename}_#{ss.Name}.csv",
'FileFormat'=>XlCSV, 'CreateBackup'=>false})
}
}
−−−− ここまで
「csvファイルの処理」おわり。
2. 他のファイル形式での保存
ここでは、Excelワークブックをタブ区切りのテキストファイル、htmlファイル、xml-ssファイルとして保存する例を示します。
(1) タブ区切りテキストとして保存
csv形式での保存については前述しました(csv04.rb参照)。それと同じように、各ワークシートごとに SaveAs メソッドを実行すればいいように思いますが、残念ながら、テキスト形式の場合は、ワークシートに対して SaveAs を実行しようとしてもエラーになります。
なぜそのような仕様になっているのか理解に苦しみますが、とりあえず次のようにすると、各々のワークシートをタブ区切りテキストの形で保存できます。
「save01.rb」から保存に関する箇所を抜き出して掲げます。
−−−− save01.rbの抜粋ここから
Exlap.new(filename) {|wb|
snames = wb.sheet_names - wb.empty_sheet_names # 空でないシート
snames.each {|sn|
ss = wb.select_sheet(sn)
ss.Activate
wb.obj.SaveAs({'Filename'=>"#{wb.filename}_#{ss.Name}.txt",
'FileFormat'=>XlText, 'CreateBackup'=>false})
}
}
−−−− ここまで
SaveAs メソッドは、ワークブックオブジェクトについて実行しています。
save01.rbを実行した結果をみていただくと分かりますが、データ中にタブコードがあると、そのデータがダブルクォーテーションで囲まれます。
なぜかデータ中にカンマがある場合も、ダブルクォーテーションで囲まれます。
(2) html形式での保存
ワークブックをhtml形式で保存する場合は、複数のワークシートが１つのhtmlファイルに収まるので、ワークシートの数に関係なく SaveAs メソッドを１回だけ実行すれば大丈夫です。
そのサンプルが 「save02.rb」 です。その中から保存に関する箇所を抜き出して掲げます。
−−−− save02.rbの抜粋ここから
Exlap.new(filename) {|wb|
wb.obj.SaveAs({'Filename'=>"#{wb.filename}.htm",
'FileFormat'=>XlHtml, 'CreateBackup'=>false})
}
−−−− ここまで
save02.rbを実行すると、htmlファイルだけでなく、関連するファイル一式が作られます。
(3) xml-ss形式での保存
xml-ss形式は、xml spread sheet 形式のことで、ファイルの拡張子を .xml とします。
Excel2003 や Excel2007 の場合、マイコンピュータなどでこの xml-ss 形式のファイルに焦点を当ててエンターキーを押すと、Excelが起動してそのxmlファイルを処理する状態になります。
Excel2003よりも前のバージョンでは、残念ながら、そのようにExcelが起動してくれませんが、OfficeXP版のExcelの場合、xml-ss形式のファイルを出力すること自体は可能です。ただし、そのxml-ssファイルには Excelを起動させる仕掛けが施されません。Excelを起動しておいて読み込むことはできます。
ところで、ワークブックをxml-ss形式で保存する場合も、複数のワークシートが１つのxmlファイルに収まるので、ワークシートの数に関係なく SaveAs メソッドを１回だけ実行すれば大丈夫です。
そのサンプルが 「save03.rb」 です。その中から保存に関する箇所を抜き出して掲げます。
−−−− save03.rbの抜粋ここから
Exlap.new(filename) {|wb|
wb.obj.SaveAs({'Filename'=>"#{wb.filename}.xml",
'FileFormat'=>XlXMLSpreadsheet, 'CreateBackup'=>false})
}
−−−− ここまで
save03.rbを実行すると、test.xls.xmlというファイルが作られます。htmlの場合のように他の関連ファイルも設けられるということはありません。
ここで取り上げたテキストファイル、htmlファイル、xmlファイル以外にも様々な形式のファイルがありますが、それについてはVBAの解説サイトなどを参照して下さい。
「他のファイル形式での保存」おわり。
3. ハイパーリンクに関する補足
ハイパーリンクを設定する場合は sheet.Hyperlinks.Add(……) で行いますが、exlap.rbをrequireしていれば、hyperlinkメソッドによって行うこともできます。
それでは、ワークシート内で設定されているハイパーリンクをどのように見つけ出せるのか、また、ハイパーリンクを開くにはどうしたらいいのか、そうした点について簡単に述べます。
(1) ハイパーリンクの存在確認と関連情報の取得
まずハイパーリンクがあるかどうかの確認方法ですが、ssがワークシートオブジェクトである場合、そのワークシート内に存在するハイパーリンクの数は
ss.Hyperlinks.Count
で得ることができます。この値が０なら、そのワークシートにハイパーリンクが存在しないことになります。
ワークシート内にある各々のハイパーリンクの情報を出力するスクリプトは、下のように書けます。
−−−− ここから
for i in 1..ss.Hyperlinks.Count
p ss.Hyperlinks(i).Range.Address # セルの絶対番地。"$A$1"など
p ss.Hyperlinks(i).Range.Address({'RowAbsolute'=>false,
'ColumnAbsolute'=>false}) # セルの相対番地。"A1"など
p ss.Hyperlinks(i).Address # リンク先アドレス。URIなど
p ss.Hyperlinks(i).Name # 割り当てられているテキスト
print "\n"
end
−−−− ここまで
ハイパーリンクに関する情報は他にもありますが、多くの場合、上のスクリプトで得られる情報があれば足りるのでは？と思います。
(2) ハイパーリンクのオープンと削除
ハイパーリンクにURIが設定されている場合、それを開くと、ブラウザが起動してWebを閲覧できます。このハイパーリンクのオープンをスクリプトで実行するには Follow メソッドを用います。
ワークシート内において何番目のハイパーリンクを開くのか、その番号が分かっている場合は次のようにします。
ss.Hyperlinks(1).Follow # シート内１番目のハイパーリンクを開く
ハイパーリンクが設定されているセルの番地が分かっている場合は下のようにします。
ss.Range("A1").Hyperlinks(1).Follow # セル番地指定でハイパーリンクを開く
それから、上の Follow を Delete に変更して実行すれば、ハイパーリンクを削除できます。
ハイパーリンクを削除すると、URIなどのリンク先アドレスが消去されます。割り当てられているテキストは、通常のデータとして残ります。なので表示上は変わりません。
割り当てられているテキストがない時は、リンク先アドレスが代替として表示されますが、ハイパーリンクを削除した場合もその点は同じです。
以上の (1) と (2) で取り上げた事柄を盛り込んだスクリプトが 「hyperlink.rb」 です。
「ハイパーリンクに関する補足」おわり。
以上。
Keyword(s):[ruby] [excel] [win32ole] [csv] [SaveAs] [Hyperlink]
References: