FrontPage  Index  Search  Changes  Login

exlapの追加サンプル

1. csvファイルの処理

 csvファイルは、Excelで直接読み込むことができます。

 ただし、読み込んだ後で、「001」が単なる「1」と表示されるようになってしまったり、「123.0」が「123」になったりと、多少不満の残る点があります。

 以下、そうした問題への対処方法について記します。

(1) 単純に1つのcsvファイルを1つのExcelファイルとして出力

 「001」問題などに目をつむって、とりあえず単純に、1つのcsvファイルを1つのExcelファイルとして出力するためのサンプル 「csv01.rb」 を掲げます。

 −−−− csv01.rb ここから
 #! ruby -KS
   # csv→xls変換。1つのcsvファイルを1つのExcelファイルとして出力
 require "exlap"
 
 # テスト用CSVファイルの書き出し
 csv1 = <<'EOS1'
 ,csv01.rb用データ#1,単純なcsv→xls
 001,"小数点以下が0の場合",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,"元号形式の日付","平成21年10月3日","実態は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ファイルを1つづつExcelで読み込んで、そのファイル名の拡張子「.csv」を「.xls」に変更して、ファイルセーブしています。

 この方法だと「001」問題は解決できませんが、とりあえずxlsファイルにすることができますから、あとは書式設定等で対応できるようになります。


(2) 数字や日付スタイルを含め総て文字列としてワークシートに記録

 「001」問題に対処する簡単な方法は、csvファイルに書かれているデータを総て文字列としてExcelのワークシートに書き込む方法です。

 下に、それを行うためのスクリプト 「csv02.rb」 を掲げます。

 −−−− csv02.rb ここから
 #! ruby -KS
   # csv→xls変換。1つのcsvファイルを1つのワークシートに記録。
   # 数字や日付スタイルを含め、総て文字列としてワークシートに記録
 require "csv"
 require "exlap"
 
 # テスト用CSVファイルの書き出し
 csv1 = <<'EOS1'
 ,csv02.rb用データ#1,総て文字列として書き出す
 001,"小数点以下が0でも省略しない",89.0
 002,"負の小数点数",-3.00
 003,"分数","1/3","←Excelお任せだと1月3日になる"
 004,"パーセント",50%,"←Excelお任せだと実態は0.5になる"
 EOS1
     File.open("test1.csv", "w") {|fp|  fp.print csv1}
 
 csv2 = <<'EOS2'
 ,csv02.rb用データ#2,総て文字列として書き出す
 001,"元号形式の日付","平成21年10月3日","←Excelお任せだと実態は2009/10/03になる"
 002,"西暦の年月日","2010/1/7"
 003,"年を省略した日付","3/10","←Excelお任せだと3月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」という文字列がセットされます。

 ただし、これは文字列であって数値ではないので、後から数値として扱いたいと思った時に困るかもしれません。

 日付についても、例えば、「平成22年1月14日」というデータをExcelにお任せでセットすると、実態は「2010/01/14」という日付データになります。表示形式は元号形式が自動的に設定されるので、Excelを起動して表示させると「平成22年1月14日」のままですが、実態は日付データです。なので、曜日を付加したいと思った時は簡単に付加できます。

 しかし、「’」を付けて文字列としてワークシートに書き込むと、日付データでなく文字列になるため、曜日の付加などが行えなくなります。


(3) 数値の表示形式を設定しながらcsvのデータを処理

 「001」を文字列でなく数値としてワークシートに書き込みつつ、表示形式(NumberFormatLocalプロパティ)を適当に設定することで、単に「1」でなく「001」と表示させるようにする、というやり方を採れます。

 それを実現するためのスクリプト 「csv03.rb」 を下に掲げます。

 −−−− csv03.rb ここから
 #! ruby -KS
   # csv→xls変換。1つのcsvファイルを1つのワークシートに記録。
   # 数値の表示形式を設定しながらcsvのデータを処理
 require "csv"
 require "exlap"
 
 # テスト用CSVファイルの書き出し
 csv1 = <<'EOS1'
 ,csv03.rb用データ#1,表示形式の調整
 001,"小数点以下が0でも省略しない",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,"元号形式の日付","平成21年10月3日","実態は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" ならば、3桁に満たない数値は、0からはじまる形式で表示されます。

 "###.00" ならば、小数点以下が0であっても2桁までは表示します。

 こうしたルールを利用して「001」問題を解決するようにしているのが set_data メソッドです。もっと効率のいい書き方があるはずと思いながら、とりあえずベタな感じで書いてあります。

 なお、csvデータをExcelの変換ルールに合わせる上でポイントになる点をいくつか上げておきます。

  • 分数: 「1/3」だと「1月3日」になってしまうので、分数にするためには「0 1/3」とする必要があります。0の後に半角スペースを置きます。こうすると、日付でなく数値(0.333……)になります。
  • パーセント: 「50%」だと、表示は「50%」のままですが、実態として数値の 0.5 がセットされます。それを避けるには「'50%」などとして文字列にします。
  • 日付: 「平成22年1月14日」あるいは「2010/1/14」だと、日付データとしてセットされます。両方とも実態は同じ「2010/01/14」ですが、前者は表示形式として元号形式が設定されるので、表示は「平成22年1月14日」のままです。
  • 年を省略した日付: 「1/3」だと、日付データとしてセットされます。表示は「1月3日」となりますが、実態は「2010/01/03」です。年は現在の年が補われます。

 「1/3」を日付でなく分数として扱うことが多い場合は、set_dataメソッドを書き換えて、分数と思われる記述パターンに「0 」を補ってやる必要があります。サンプルでは、分数に関する変換は行っていません。


 上記サンプルでは、csvファイルの読込みにrubyの標準ライブラリのcsvを使っていますが、速度を気にする場合は、例えば LightCsv のようなライブラリを使うといいかもしれません。

LightCsvのダウンロードサイト

 LightCsvを利用する場合は、
 require "csv" → require "lightcsv"
 CSV.readlines → LightCsv.readlines
と書き換えればOkです。


(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形式で保存する場合は、複数のワークシートが1つのhtmlファイルに収まるので、ワークシートの数に関係なく SaveAs メソッドを1回だけ実行すれば大丈夫です。

 そのサンプルが 「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形式で保存する場合も、複数のワークシートが1つのxmlファイルに収まるので、ワークシートの数に関係なく SaveAs メソッドを1回だけ実行すれば大丈夫です。

 そのサンプルが 「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
で得ることができます。この値が0なら、そのワークシートにハイパーリンクが存在しないことになります。

 ワークシート内にある各々のハイパーリンクの情報を出力するスクリプトは、下のように書けます。

 −−−− ここから
 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  # シート内1番目のハイパーリンクを開く

 ハイパーリンクが設定されているセルの番地が分かっている場合は下のようにします。

 ss.Range("A1").Hyperlinks(1).Follow  # セル番地指定でハイパーリンクを開く

 それから、上の Follow を Delete に変更して実行すれば、ハイパーリンクを削除できます。

 ハイパーリンクを削除すると、URIなどのリンク先アドレスが消去されます。割り当てられているテキストは、通常のデータとして残ります。なので表示上は変わりません。
 割り当てられているテキストがない時は、リンク先アドレスが代替として表示されますが、ハイパーリンクを削除した場合もその点は同じです。

 以上の (1) と (2) で取り上げた事柄を盛り込んだスクリプトが 「hyperlink.rb」 です。

「ハイパーリンクに関する補足」おわり。

以上。

Last modified:2010/01/19 23:12:43
Keyword(s):[ruby] [excel] [win32ole] [csv] [SaveAs] [Hyperlink]
References: