exlap_cのサンプル集

exlap_c(xmlss作成用rubyライブラリ)サンプル集

最終更新日：　2011/06/30

exlap_cは、Excelで扱えるxmlスプレッドシート(xmlss)作成用のrubyライブラリです。

ここではexlap_cライブラリ(ver 1.3以降)を利用したサンプルを掲げます。

ライブラリに関する解説は、「exlap_cの使い方」を参照して下さい。

各サンプルでは、カレントディレクトリに Shift_JIS で書かれた exlap_s.rb があること、また、スクリプト本体も同じ Shift_JIS で書かれていることを仮定しています。

• 1.　csvデータの取込み
• 2.　２次元配列の取込み
• 3.　font情報の付加
• 4.　罫線・始点の設定
• 5.　文字の配置
• 6.　数値の表示形式
• 7.　表示形式あれこれ
• 8.　関数の設定
• 9.　表単位でのfont情報設定
• 10.　列単位でのfont情報設定
• 11.　行単位でのfont情報設定
• 12.　部分的なfont情報の付加
• 13.　ふりがな
• 14.　非表示属性
• 15.　ハイパーリンク
• 16.　別シートのコピーと外枠罫線
• 17.　列の幅と行の高さの指定
• 18.　選択入力欄(ドロップダウンリスト)の設定その１
• 19.　選択入力欄(ドロップダウンリスト)の設定その２
• 20.　条件付き書式
• 番外編　xmlssをクライアント側のExcelで開かせるためのcgi

文字列のcsvデータを取り込んで、xmlssを生成。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   csv_str = "awk,perl\n" + "python,ruby\n"
   wb.push(csv_str)
   wb.sheets.last.name = "csvデータの取込み"  # ワークシート名
   wb.output_xml "test01.xml"
   −−−− ここまで

rubyの「配列の配列」(２次元配列)を取り込んでxmlssを生成。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [
       ['awk',    'perl'],
       ['python', 'ruby']]
   wb.push(ary)
   wb.sheets.last.name = "２次元配列の取込み"
   wb.output_xml "test02.xml"
   −−−− ここまで

セルごとに文字色を変える。背景色の指定も行う。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ss = ExlSheet.new
   ss.name = "font情報の付加"
   ary = [%w(snow White), %w(sky Blue), %w(earth), %w(sun Red)]
   rng = ss.range(0,0, 1,1)  # A1:B2の2×2の領域をRangeとして設定
   rng.each {|cell|
     data, color = ary.shift
     cell.Data = data
     cell.Font.Color = color  if color
     cell.Interior.Color = "Black"  if color == "White"
   }
   wb.push(ss)
   wb.output_xml "test03.xml"
   −−−− ここまで

罫線を引く。また、始点(表の左上端の位置)を指定。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [%w(desk chair), %w(pencil note)]
   ss = ExlSheet.new(ary)
   ss.name = "罫線・始点の設定"
   ss.start_rc = [1,1]  # 始点をB2にする
   ss.used_range.allbox()  # データのある領域のセルを総て罫線で囲む
   wb.push(ss)
   wb.output_xml "test04.xml"
   −−−− ここまで

結合セル(3×3)の中で、文字の位置を様々に指定。3×3の結合セルを４つ設ける。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ss = ExlSheet.new
   ss.name = "文字の配置"
   ary = [%w(dog Left Top), %w(cat Center Center),
       %w(cow Left Bottom), %w(horse Right Bottom)]
   height = 3  # 縦の結合幅
   width = 3  # 横の結合幅
   [0, height].each {|y|
     [0, width].each {|x|
       rng = ss.range(y,x, y+height-1, x+width-1)
       cell = rng.cell(0,0)  # Rangeの左上端のセル
       data, horizontal, vertical = ary.shift
       cell.Data = data
       cell.Alignment.Horizontal = horizontal
       cell.Alignment.Vertical = vertical
       rng.merge_cells = true  # セル結合
     }
   }
   wb.push(ss)
   wb.output_xml "test05.xml"
   −−−− ここまで

数値を小数点２桁まで表示するように設定。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [
       [56, 12.4, 35.42],
       [234.8, 94, 39.1]]
   ss = ExlSheet.new(ary)
   ss.name = "数値の表示形式"
   ss.used_range.each {|cell|
     cell.NumberFormat = "##0.00"
   }
   wb.push(ss)
   wb.output_xml "test06.xml"
   −−−− ここまで

001問題への対応、パーセントや日付の表示形式への対応の例。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   csv_str = <<EOS1
   001,"1,234",123,12
   002,50%,3.14%,"1,234%"
   003,2010/10/07,2010年10月８日,平成２２年１０月９日
   004,01:14,15:32:08
   EOS1
   wb.push(csv_str)
   wb.sheets.last.name = "表示形式あれこれ"
   wb.output_xml "test07.xml"
   −−−− ここまで

Excelの関数 SUM, LOWER を設定する例。それら関数の代替rubyスクリプトも定義。代替スクリプトは、なくてもかまわない。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   formula1 = "=SUM(RC[-3]:RC[-1])"
   $formula[formula1] = <<SCR1
     total = 0
     aa[rn][cn-3..cn-1].each do |cell|
       total += getData(cell).to_i
     end
     data = total
   SCR1
   formula2 = "=LOWER(RC[-1])"
   $formula[formula2] = "data = getData(aa[rn][cn-1]).to_s.tr('A-Z', 'a-z')"
   ss = ExlSheet.new
   ss.name = "関数の設定"
   ss.range(0,0, 0,3).Data = [5, 6, 7, 5+6+7]
   ss.cell(0,3).Formula = formula1
   ss.range(1,0, 1,1).Data = ["ABC", "abc"]
   ss.cell(1,1).Formula = formula2
   wb.push(ss)
   wb.output_xml "test08.xml"
   −−−− ここまで

表全体について、文字を緑色にし、総てのセルに罫線を引く。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [%w(grass leaf), %w(tree forest)]
   ss = ExlSheet.new(ary)
   ss.name = "表単位でのfont情報設定"
   ss.tbl = eFont("Green").mrg( eBorder() )
   wb.push(ss)
   wb.output_xml "test09.xml"
   −−−− ここまで

第１列目を総て茶色、第２列目を総て水色に設定。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [%w(linux FreeBSD), %w(Windows android)]
   ss = ExlSheet.new(ary)
   ss.name = "列単位でのfont情報設定"
   ss.col = [eFont("Brown"), eFont("Cyan")]
   wb.push(ss)
   wb.output_xml "test10.xml"
   −−−− ここまで

第１行目を総てピンク、第２行目を総て白黒反転に設定。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [%w(bicycle car), %w(ship train)]
   ss = ExlSheet.new(ary)
   ss.name = "行単位でのfont情報設定"
   ss.row[0] = eFont("Magenta")
   ss.row[1] = eFont("White")
   ss.row[1].Interior.Color = "Black"
   wb.push(ss)
   wb.output_xml "test11.xml"
   −−−− ここまで

セル内の文字列の一部に色を付けたりする。html記述で実現。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ss = ExlSheet.new
   ss.name = "部分的なfont情報の付加"
   ss.col = [eWidth(36), eWidth(36)]  # 列幅の設定
   rng = ss.range(0,0, 1,1)
   rng.Data = [
     'キーワードは<I>xmlss</I><Font>(イタリック)です．</Font>',
     'キーワードは<B>ruby</B><Font>(太字)です．</Font>',
     'キーワードは<U>SpreadSheet</U><Font>(下線)です．</Font>',
     'キーワードは<Font html:Color="Red">Excel</Font><Font>(赤色)です．</Font>']
   rng.each {|cell|
     cell.Type = 'String@html'
   }
   wb.push(ss)
   wb.output_xml "test12.xml"
   −−−− ここまで

ふりがなを付ける。第１行目と２行目は、ふりがな表示off。３行目と４行目はon。第２列目には、ふりがなのみ表示。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [
     %w(虚心坦懐 キョシンタンカイ),
     %w(君子豹変 クンシヒョウヘン),
     %w(羽化登仙 ウカトウセン),
     %w(我田引水 ガデンインスイ)]
   ss = ExlSheet.new
   ss.name = "ふりがな"
   rng = ss.range(0,0, 3,1)  # ４行・２列
   rng.each_with_yx {|cell, y, x|
     case x
     when 0  # 第１列目
       data, phonetic = ary.shift
       cell.Data = data
       cell.PhoneticText.Text = phonetic
       if y >= 2  # 第３行目と４行目は、ふりがな表示on
         cell.PhoneticText.Visible = 1
       end
     when 1  # 第２列目
       cell.Data = 'x'  # 適当な文字列(なんでもよい)
       cell.Formula = '=PHONETIC(RC[-1])'  # 左隣セルのふりがなを得る
     end
   }
   ss.col[1] = eWidth(16)  # 第２列目の幅を16桁に
   wb.push(ss)
   wb.output_xml "test13.xml"
   −−−− ここまで

特定の列または行に非表示属性を付ける。3行のうちの第2行目、3列のうちの第2列目を非表示にする。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [%w(1 awk perl),
       %w(2 fortran cobol),
       %w(3 python ruby)]
   ss = ExlSheet.new(ary, "非表示属性")
   ss.col[1] = eHidden(1)  # 第２列目を非表示に
   ss.row[1] = eHidden(1)  # 第２行目を非表示に
   wb.push(ss)
   wb.output_xml "test14.xml"
   −−−− ここまで

ハイパーリンクを設定。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [
     %w(ルビマ http://jp.rubyist.net/magazine/),
     %w(経済産業省 http://www.meti.go.jp/)]
   ss = ExlSheet.new
   ss.name = "ハイパーリンク"
   ss.range(0,0, 1,0).each {|cell|
     data, href = ary.shift
     cell.Data = data
     cell.HRef = href
   }
   wb.push(ss)
   wb.output_xml "test15.xml"
   −−−− ここまで

別のワークシートの内容を第２行目以降(A2欄以降)にコピーし、それを外枠罫線で囲む。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   csv_str = "awk,perl\n" + "python,ruby\n"
   wb.push(csv_str)
   ss = ExlSheet.new
   ss.name = "別シートのコピーと外枠罫線"
   rng = ss.copy_from(1,0, wb.sheets.last)
   rng.framebox
   wb.push(ss)
   wb.output_xml "test16.xml"
   −−−− ここまで

関東地区の視聴率調査結果をwebから取得。データが総て見えるよう列の幅と行の高さを調整。

このスクリプトが動くためには、htmlパーサの hpricot または nokogiri (どちらもruby用ライブラリ)が予めインストールされている必要がある。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   url = 'http://www.videor.co.jp/data/ratedata/top10.htm'
   wb.push(url)
   puts '列の幅と行の高さを指定する例(視聴率調査データ)'
   puts '各列の最大幅を標準出力に出力.'
   scr = 'str = str.gsub(/\n[ \t]+/, "\n")'
   wb.sheets.map! {|ss|
     ss.array_convert(scr)
     mc = ss.mloc
     p mc
     over_flag = false
     for i in 0...mc.size
       if mc[i] > 40
         mc[i] = 40
         over_flag = true
       end
       ss.col[i] = eWidth(mc[i])
     end
     ss.row[1] = eHeight(2)  # 第2行目は必ず2行分の高さ
     if over_flag  # 桁数オーバーのものがあった
       for i in 2...ss.max_row  # 3行目以降の高さを2行分に
         ss.row[i] = eHeight(2)
       end
     end
     ss.array_update(eAlignment(nil, nil, 1))
   }
   wb.output_xml "test17.xml"
   −−−− ここまで

選択入力欄(ドロップダウンリスト)の設定。選択肢を文字列で与える。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   ary = [
     %w(質問1 ？),
     %w(質問2 ？),
     %w(質問3 ？)]
   ss = ExlSheet.new(ary, "アンケート")
   input_area = "R1C2:R#{ary.size}C2"  # 選択入力欄の番地
   list = "◎,△,×"  # 選択候補(カンマ区切り)
   ss.other['DataValidation'] = <<EOS1
   <DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
   <Range>#{input_area}</Range>
   <Type>List</Type>
   <CellRangeList/>
   <Value>&quot;#{list}&quot;</Value>
   </DataValidation>
   EOS1
   wb.push(ss)
   wb.output_xml "test18.xml"
   −−−− ここまで

選択入力欄(ドロップダウンリスト)の設定。選択肢を別のシートに書いておく。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
   range_name = "部署名"  # 名前付き範囲の名称
   sheet_name2 = "候補リスト"  # 入力候補を記した次ワークシートの名前
       ##
   ary = [
     %w(氏名 所属),
     %w(高橋一郎 ？),
     %w(鈴木次郎 ？),
     %w(佐藤三郎 ？)]
   ss = ExlSheet.new(ary, "社員")
   input_area = "R2C2:R#{ary.size}C2"  # 選択入力欄の番地
   ss.other['DataValidation'] = <<EOS1
   <DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
   <Range>#{input_area}</Range>
   <Type>List</Type>
   <Value>#{range_name}</Value>
   </DataValidation>
   EOS1
   wb.push(ss)
       # ↓ 選択候補を次シートに設定
   ary = [%w(総務課 営業課 人事課 研修課 開発課)]
   ss = ExlSheet.new(ary, sheet_name2)
     # ss.row[0] = eHidden(1)  # 第１行目を非表示にするならコメントアウト
   ss.array[0].map! {|cell|
     cell = eData(cell, eNamedCell(range_name))
   }
   wb.push(ss)
       ##
   option_area = "R1C1:R1C#{ary[0].size}"  # 名前付き範囲(選択候補)の番地
   wb.other['Names'] = <<EOS2
   <Names>
   <NamedRange ss:Name="#{range_name}" ss:RefersTo="=#{sheet_name2}!#{option_area}"/>
   </Names>
   EOS2
   wb.output_xml "test19.xml"
   −−−− ここまで

1〜6の乱数を10回発生させてセルに書き込み、3以下なら文字を青色にする。

   −−−− ここから
   #! ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   include Exl
   wb = ExlBook.new
       # 1〜6の乱数を10回発生させて記録
   row = []
   10.times do
     n = rand(6) + 1
     row << n
   end
   ary = [row]
   ss = ExlSheet.new(ary, "条件付き書式")
       # 条件設定のための情報をセット
       # セルのテータが3以下なら青文字にする
   area = "R1C1:R1C#{ary[0].size}"  # 書式設定エリア
   opr = "LessOrEqual"  # ＜＝
   val = "3"
   clr = "blue"
   ss.other['ConditionalFormatting'] = <<EOS1
   <ConditionalFormatting xmlns="urn:schemas-microsoft-com:office:excel">
     <Range>#{area}</Range>
     <Condition>
     <Qualifier>#{opr}</Qualifier>
     <Value1>#{val}</Value1>
     <Format Style='color:#{clr}'/>
     </Condition>
   </ConditionalFormatting>
   EOS1
       ##
   wb.push(ss)
   wb.output_xml "test20.xml"
   −−−− ここまで

［補足］　上の例で「3以下」を設定していますが、数値としての比較でなく文字列での比較になるようです。なので、「7」と「10」を比較したとするなら「7」の方が大きいと判断されるみたいです。

数値として比較する方法を含め、条件付き書式に関するxmlss記述にはいろいろなバリエーションがあるようですが、まだ把握していません。

とりあえず最も簡単な例を掲げてみました。

xmlssをクライアント側のExcelで開かせるためのcgi

rubyのcgiライブラリを使って書いた１例を掲げます。

   −−−− ここから
   #!/usr/local/bin/ruby -Ks
     # coding: Shift_JIS
   require "./exlap_s"
   require "cgi"
   include Exl
       # クライアントにExcelファイルを送るメソッド
   def send_excel(filename, body)
     header = {
       'status'=>"OK",
       'Pragma'=>"no-cache",
       'Expires'=>"0",
       'Content-type'=>"application/vnd.ms-excel",
       'Content-Disposition'=>"attachment; filename=\"#{filename}\""
     }
     cgi = CGI.new("html4")
     cgi.out(header) {body}
   end
       ## main: xmlss生成＆クライアントに送信
   wb = ExlBook.new
   ss = ExlSheet.new
   ss.name = "font情報の付加"
   ary = [%w(snow White), %w(sky Blue), %w(earth), %w(sun Red)]
   rng = ss.range(0,0, 1,1)  # A1:B2の2×2の領域をRangeとして設定
   rng.each {|cell|
     data, color = ary.shift
     cell.Data = data
     cell.Font.Color = color  if color
     cell.Interior.Color = "Black"  if color == "White"
   }
   wb.push(ss)
   body = wb.xml_string  # xmlssを文字列で得る
   filename = "test03.xml"
   send_excel(filename, body)
   −−−− ここまで

上のcgiスクリプトを test03.cgi として、当方のサーバ(FreeBSD 7.2, ruby 1.8.7)に置いてみました。

このcgiにブラウザでアクセスすると、開くか保存するかキャンセルするかを選ぶダイアログボックスが出ます(その前にセキュリティの警告が出るかもしれません)。そこで「開く」を選ぶと、Excelが起動します。

ただし、クライアント側のExcelがExcel2002(Office XP版)だと、「開く」を選んでもExcelが起動しないようです。「保存」は可能です。

cgiスクリプト内のfilenameの値 test03.xml を test03.xls にすれば、Excel2002でも開けるようになりますが、そうすると逆に、Excel2007などでは無用な警告メッセージが出るようになります。「拡張子と中身が違っていますが、開きますか？」といったメッセージです。

Excelのバージョン、あるいは、ブラウザの種類やバージョンにも左右されるかもしれない中途半端なcgiではありますが、１つの簡単な参考例として掲げてみました。

−　以上　−