RubyでExcelの操作を行います。実行確認した環境は以下となります。
RubyによるExcel操作の例を下記に示します。実行には、「excelUtil.rb」、「wincons.rb」が必要です。
Excelユーティリティ「excelUtil.rb」(excelUtil_v100.7z)のダウンロード
「wincons.rb」の説明及びダウンロード
# coding: utf-8
# excel操作の例
require 'win32ole'
require "./excelUtil"
require "./wincons"
arfruits = [ [ "りんご", 3500 ],
[ "なし", 3120 ],
[ "みかん", 2950 ],
[ "バナナ", 2800 ]
]
cons = Console.new(__ENCODING__)
exlUtil = ExcelUtilt.new()
excel = WIN32OLE.new('Excel.Application')
module ExlConst; end # excelの定数をロード
WIN32OLE.const_load(excel, ExlConst)
excel.visible = true # 表示状態とする
# ------- Excelファイルを読み込む場合
#sxlsfile = __dir__.encode("utf-8") + "/excel例1.xls" # or xlsxファイル
#begin
# workbook = excel.workbooks.Open(sxlsfile)
#rescue
# print "excelファイル読み込みエラー!¥r¥n"
# exit
#end
# ------- 新規でワークブックを追加する場合
workbook = excel.workbooks.add
sheets = workbook.Sheets
nshnm = "果物" # 新シート名
bz = false
sheets.each{|sht|
if nshnm == sht.Name.encode("utf-8") # 新シート名存在するか?
bz = true
break
end
}
if bz == false
sheets.Add.Name = nshnm # 新シート追加
end
sheet = workbook.sheets(nshnm) # 「果物」シート選択
sheet.Activate # シートをアクティブに 前面表示
print "シート名:" + sheet.Name.encode("utf-8") + "¥r¥n" # シート名取得
ix = 'B'.ord - 'A'.ord + 1; iy = 3 # B3
sheet.Cells(iy, ix).Value = "品名"
sheet.Cells(iy, ix + 1).Value = "金額"
oRange = sheet.range(sheet.Cells(iy, ix), sheet.Cells(iy, ix + 1)) # B3:C3
oRange.Interior.Color = exlUtil.rgb(0xff, 0, 0) # バック色
oRange.Font.Color = exlUtil.rgb(0xff, 0xff, 0xff) # 文字色
oRange.HorizontalAlignment = ExlConst::XlCenter # 文字横方向中央
oRange.VerticalAlignment = ExlConst::XlCenter # 文字縦方向中央
border = oRange.Borders(ExlConst::XlEdgeBottom) # 罫線下端
border.LineStyle = ExlConst::XlContinuous # 実線
border.Weight = ExlConst::XlMedium # 太さ中
border.ColorIndex = 1 # 色コード 1 黒
iy += 1
ssum = (ix + 'A'.ord).chr + iy.to_s
arfruits.each{|sf, ik|
oCell = sheet.Cells(iy, ix)
oCell.Value = sf
oCell.Interior.Color = exlUtil.rgb(0xaa, 0xff, 0xff) # バック色
oCell = sheet.Cells(iy, ix + 1)
oCell.NumberFormatLocal = "#,##0" # セルの表示形式
oCell.Value = ik
iy += 1
}
ssum += ":" + (ix + 'A'.ord).chr + (iy - 1).to_s
oCell = sheet.Cells(iy, ix) # B8
oCell.Value = "合計"
border = sheet.range(sheet.Cells(iy, ix), sheet.Cells(iy, ix + 1)).Borders(ExlConst::XlEdgeTop) # 罫線上端
border.LineStyle = ExlConst::XlContinuous
border.Weight = ExlConst::XlMedium
border.ColorIndex = 5 # 色コード 5 青
oCell.Interior.Color = exlUtil.rgb(0, 0x80, 0) # バック色
oCell.Font.Color = exlUtil.rgb(0xff, 0xff, 0xff) # 文字色
oCell = sheet.Cells(iy, ix + 1) # C8
oCell.formula = "=sum(" + ssum + ")" # 合計関数
oCell.NumberFormatLocal = "¥¥#,##0" # 表示形式 ¥付き
isum = oCell.Value
print "合計=" + isum.to_s + "¥r¥n"
iprn = 0
print "印刷しますか？(y/n)¥r¥n"
while true
sleep 0.01
ca = cons.inkey
if ca[0] == nil; next; end
if ca[0] == "y" || ca[0] == "Y"
iprn = 1
end
break;
end
if iprn == 1 # 印刷 印刷部数:1 開始ページ:1 終了ページ:1
sheet.PrintOut(:Copies => 1, :From => 1, :To => 2)
end
print "qキーで終了¥r¥n"
while true
ca = cons.inkey
if ca[0] == "q" || ca[0] == "Q"
break
end
sleep 0.01
end
sxlsfile = __dir__.encode("utf-8") + "/excel例3.xlsx" # or xlsファイル
print sxlsfile + "¥r¥n"
if File.exist?(sxlsfile)
File.delete(sxlsfile)
end
workbook.SaveAs(sxlsfile.sub("/", "¥¥")) # 名前を付けて保存
excel.quit
下記プログラム例における オブジェクトexcel, workbook, sheet については、上記プログラムを参照してください。
module ExlConst; end # excelの定数をロード
WIN32OLE.const_load(excel, ExlConst)
excel.visible = true # 表示状態で実行 falseで非表示実行 false:デフォルト値
excel.DisplayAlerts = false # 確認メッセージを非表示 trueで表示
workbook.Saved = true # 終了時の保存確認メッセージを非表示
EXCEL定数を使用する場合は、先頭文字を大文字にします。 例 xlCenter の場合 ExlConst::XlCenter
sheet = workbook.ActiveSheet # アクティブなシートを選択
sheet = workbook.sheets("Sheet1") # シート名でシート選択
sheet = workbook.sheets[1] # インデックスNO.でシート選択 1～
sheet.Activate # 選択シートをアクティブに 前面に表示
sheet.Name = "新シート名" # 選択シートのシート名変更
icu = sheets.Count # シート数取得
# 全シート名取得
arshnm = [] # シート名を入れる配列
sheets.each{|sht|
arshnm << sht.Name.encode("utf-8")
}
p arshnm
sheet = sheets.add # シートを追加 戻り値が追加シート
sheet = sheets.add(:after => workbook.sheets(sheets.count)) # 最後尾にシート追加
workbook.sheets("Sheet3").Move(:after => workbook.sheets("Sheet1")) # sheet3をsheet1の後ろに移動
workbook.sheets("Sheet3").Move(:before => workbook.sheets("Sheet1")) # sheet3をsheet1の前に移動
workbook.sheets("Sheet3").Delete # Sheet3削除
sheet.Visible = false # 選択シートを非表示 trueで表示
# セルに値セット
oCell = sheet.Cells(11, 2) # セル"B11"を指定 行,列で指定 1～の数字
oCell.Value = "千葉県"
oRange = sheet.range("B12") # セル"B12"をRangeで指定
oRange.Value = "日本"
# セルの値取得
va = sheet.Cells(2, 2).Value
va = sheet.Cells(3, 2).Value2 # 通貨型、日付型の場合 Flota型 日付型の場合はシリアル値
va = sheet.Cells(4, 2).Text # 表示した状態を取得
Value での取得データは、数字:Float型、文字列:String型、日付:Time型、通貨:String型 となります。
Value2 での取得データは、数字:Float型、文字列:String型、日付:Float型、通貨:Float型 となります。
Text での取得データは、すべてString型となります。
値のないセルを Value、Value2 で読み込んだ場合は、nil となり、Text で読み込んだ場合は、"" となります。
# B3:B4の内容を配列で読込
arrdd = sheet.range("B3:E4").Value
p arrdd
# B6:F7へ配列で書込
ardata = [["品名", "りんご", "みかん", "バナナ", "なし"],
["個数", 50, 80, 65, 45]]
sheet.range("B6:F7").Value = ardata
# コピーペースト B2:E3 の内容を B5 へコピー
sheet.range("B2:E3").Copy
#sheet.range("B5").PasteSpecial # すべて貼り付け
sheet.range("B5").PasteSpecial(:Paste => ExlConst::XlPasteValues) # 値のみ貼り付け
# コピー状態を解除
excel.cutCopyMode = false
# B2:B3 の内容を B5 へコピー
sheet.range("B2:B3").Copy(:Destination => sheet.range("B5"))
sheet.range("B2:B3").Copy(sheet.range("B5")) # Destinationを省略した場合
oRange.Clear # セル内容をすべてクリア
oRange.ClearContents # 数式、文字列クリア
oRange.ClearFormats # 書式クリア
# セルの書式例
oCell.NumberFormatLocal = "@" # 表示形式を文字列に
oCell.NumberFormatLocal = "¥¥#,##0;[赤]¥¥-#,##0" # 通貨形式 マイナスで赤表示
oCell.NumberFormatLocal = "YYYY年MM月DD日" # 日付形式 シリアル値を入力
oRange.Font.Name = "ＭＳ 明朝" # フォント指定
oRange.Font.Italic = true # 斜体指定 falseで解除
oRange.Font.Bold = true # 太字指定 falseで解除
oRange.Font.Size = 15 # フォントサイズ指定
oRange.Font.Underline = ExlConst::XlUnderlineStyleDouble # 文字下線 二重線
oRange.Font.Strikethrough = true # 取り消し線
sheet.range("B2:D2").MergeCells = true # B2:D2をセル結合
sheet.range("B2:D2").MergeCells = false # B2:D2のセル結合を解除
参考）
・セルの値を文字で読み込んだ場合、文字コードは、「Windows-31J」です。また、値の存在しないセルは、「nil」となります。
・セル内で改行がある場合は、改行コード0x0A(LF)がセットされます。
・セルへ代入する文字コードは、「utf-8」でも可能です。
・貼り付け内容 XlPasteType 列挙 (Excel)
# セルの幅取得
iw = sheet.Columns("B").Width # Width ポイント単位
iw = sheet.Columns(2).Width
iw = sheet.Columns("B").ColumnWidth # ColumnWidth 標準フォントの1文字単位
iw = sheet.Columns(2).ColumnWidth
iw = sheet.Range("B1").EntireColumn.Width
iw = sheet.Range("B1").EntireColumn.ColumnWidth # ColumnWidth 標準フォントの1文字単位
iw = sheet.Range("C2").Width
iw = sheet.Range("D3").ColumnWidth
# セルの幅設定 Width での設定は不可
sheet.Columns(1).ColumnWidth = 10
sheet.Columns("B").ColumnWidth = 20
sheet.Range("C2").ColumnWidth = 40
# セルの高さ取得
ih = sheet.Rows(2).Height # 2行目 Height ポイント単位
ih = sheet.Rows(2).RowHeight # RowHeight ポイント単位
ih = sheet.Range("A2").EntireRow.Height # 2行目
ih = sheet.Range("B2").EntireRow.RowHeight # 2行目
ih = sheet.Range("C3").Height # 3行目
ih = sheet.Range("D4").RowHeight # 4行目
# セルの高さ設定 Height での設定は不可
sheet.Rows(3).RowHeight = 30 # 3行目 30ポイント
sheet.Range("A4").EntireRow.RowHeight = 35 # 4行目 35ポイント
sheet.Range("A5").RowHeight = 40 # 5行目 40ポイント
色指定のプロパティは、ColorとColorIndexの二通りあります。Colorは、RGB値で指定します。
ColorIndexは、1～56の色コードに対応した数字で指定します。
Colorは、Excel2007以降で使用出来ます。上記例におけるrgb関数は、excelUtil.rbで定義されています。
# ColorIndex出力例
ic = 1; iy = 2
ibc = 'A'.ord - 1
loop{
'B'.upto('I'){|sx|
ix = sx.ord - ibc
oCell = sheet.Cells(iy, ix)
oCell.Interior.ColorIndex = ic
oCell.Value = ic
oCell.Font.Bold = true
oCell.HorizontalAlignment = ExlConst::XlCenter
ic += 1
}
if ic > 56; break; end
iy += 1
}
Excelのワークシート関数を使います。すべての関数が使えるわけではありません。
# Max関数を使用 C4:C7での最大値を取得します。
imax = excel.WorksheetFunction.Max(sheet.Range("C4:C7"))
p imax
# Sum関数を使用 C4:C7での合計値を取得します。
isum = excel.WorksheetFunction.Sum(sheet.Range("C4:C7"))
p isum
# COUNTIF関数使用 C4:C7で「>=3000」の個数を取得します。
icu = excel.WorksheetFunction.COUNTIF(sheet.Range("C4:C7"), ">=3000")
p icu
クリップボード経由のデータは、行の区切りが、¥r¥n(CRLF)でセルデータの区切りが¥t(タブ）となっています。また、 文字コードは、マジックコメントに従います。(utf-8または、Windows31-J) 下記プログラム例では、utf-8です。 プログラムの実行には、clipboard.rbが必要です。
# coding: utf-8
require 'win32ole'
require "./clipboard"
CBd = ClipBoard.new(__ENCODING__)
excel = WIN32OLE.new('Excel.Application')
excel.visible = true # 表示状態とする
sxlsfile = __dir__.encode("utf-8") + "/exlテスト用.xlsx"
workbook = excel.workbooks.Open(sxlsfile)
sheet = workbook.sheets("Sheet1")
sheet.Activate
sheet.Range("A1:D3").Copy # A1:D3の内容をクリップボード経由で取得
sr = CBd.getText
print sr
sd = "品名¥tりんご¥tみかん¥tバナナ¥tなし¥r¥n" +
"個数¥t50¥t80¥t65¥t45¥r¥n"
CBd.setText(sd)
sheet.Range("A5").PasteSpecial # クリップボード経由でA5:E6へ書き込み
workbook.Saved = true # 終了時の保存確認メッセージを非表示
excel.quit
sheet.PageSetup.PrintArea = sheet.range("A1:D12").Address # 印刷範囲設定
sheet.PageSetup.PrintArea = "" # 印刷範囲解除
sheet.range("A6").PageBreak = ExlConst::XlPageBreakManual # 改ページ設定 指定セルの上
sheet.range("A6").PageBreak = ExlConst::XlNone # 改ページ解除
sheet.ResetAllPageBreaks # 全改ページ解除
# 印刷例
sheet.PrintOut(:From => 1, # 開始ページ
:To => 2, # 終了ページ
:Copies =>1, # 部数
:Preview => true, # プレビュー true:有り false:無し
:ActivePrinter => "Microsoft XPS Document Writer") # 印刷プリンタを指定
印刷ダイアログを表示します。ダイアログ表示参照
excel.Dialogs(ExlConst::XlDialogPrint).Show
パソコン内のプリンタ一覧を取得します。PrintOutで指定するプリンタ名は、下記プログラムで取得出来ます。
# coding: utf-8
require 'win32ole'
appShell = WIN32OLE.new('Shell.Application')
prnObj = appShell.NameSpace(4).Items
ik = prnObj.count; # プリンタ数
for ia in 0..(ik - 1) do
p prnObj.item(ia).Name
end
アクティブシートをPDFファイル出力します。
sheet.PageSetup.PrintArea = sheet.range("A1:I30").Address # 出力範囲設定
sheet.range("A20").PageBreak = ExlConst::XlPageBreakManual # 改ページ位置
workbook.ActiveSheet.ExportAsFixedFormat(:Type => ExlConst::XlTypePDF, # TYPE PDF形式指定
:Filename => "d:¥¥pdffile¥¥pdfテスト.pdf", # 出力するPDFファイル名
:Quality => ExlConst::XlQualityStandard, # ファイルの品質 or XlQualityMinimum
:IncludeDocProperties => true, # ドキュメント プロパティを含める場合はtrue、それ以外はfalse
:IgnorePrintAreas => false, # 印刷範囲を無視する場合はtrue、それ以外はfalse
:OpenAfterPublish => false, # 出力後にpdf表示を行う場合はtrue、それ以外はfalse
:From => 1, # 開始ページ 省略時は、先頭ページ
:To => 2) # 終了ページ 省略時は、最終ページ
# 簡易出力
workbook.ActiveSheet.ExportAsFixedFormat(:Type => ExlConst::XlTypePDF, :Filename => "d:¥¥pdffile¥¥pdfテスト.pdf")
ExcelのメソッドInputBoxを使用します。
# Prompt(省略不可), Title(省略可) 指定
sr = excel.InputBox("名前を入力して下さい。", "入力")
sr = excel.InputBox("名前を入力して下さい。", :Title => "名前入力") # 引数名Title指定
sr = excel.InputBox("住所を入力して下さい。¥r¥n都道府県名から入力して下さい。", "入力") # Promptに改行ありの場合
if sr != false # 「キャンセル」ボタンが押された場合は、戻り値がfalseとなります。
print sr + "¥r¥n"
end
# キー入力制限をTypeで指定
dr = excel.InputBox("個数を入力して下さい。", "入力", :Type => 1) # 数字入力限定 2:文字列
if dr != false
print dr.to_s + "¥r¥n" # 戻り値は、float
end
・引数XPos(Left),YPos(Top),Defaultは、使用出来ません。
Excelのダイアログを表示します。
# [名前を付けて保存] ダイアログ ボックス
excel.Dialogs(ExlConst::XlDialogSaveAs).Show
# [ページ設定 (ページ)] ダイアログ ボックス
excel.Dialogs(ExlConst::XlDialogPageSetup).Show
# [印刷] ダイアログ ボックス
excel.Dialogs(ExlConst::XlDialogPrint).Show
# [プリンターの設定] ダイアログ ボックス
excel.Dialogs(ExlConst::XlDialogPrinterSetup).Show
# [印刷プレビュー] ダイアログ ボックス
excel.Dialogs(ExlConst::XlDialogPrintPreview).Show
ダイアログは、上記以外にもあります。ダイアログ一覧(XlBuiltInDialog 列挙) すべてのダイアログの動作を確認していませんので、使用時は確認を行い使用して下さい。
RubyからExcelのマクロを実行します。Excelファイルは、「マクロテスト.xlsm」とします。
' Rubyから実行するExcelマクロの例
Sub test()
msgdisp ("マクロのテスト")
End Sub
Sub msgdisp(ByVal smsg As String)
MsgBox smsg
End Sub
Function add(ByVal ix As Integer, ByVal iy As Integer)
add = ix + iy
End Function
Function inputStr(ByVal smsg As String)
inputStr = InputBox(smsg)
End Function
上記ExcelマクロをRubyから実行します。
# coding: utf-8
# excelのマクロ実行例
require 'win32ole'
excel = WIN32OLE.new('Excel.Application')
excel.visible = true
sxlsfile = __dir__.encode("utf-8") + "/マクロテスト.xlsm"
begin
workbook = excel.workbooks.Open(sxlsfile)
rescue
puts "excelファイル読み込みエラー!"
exit
end
excel.Run("test") # 引数なし
excel.Run("msgdisp", "rubyからExcelマクロ実行") # 引数文字列
ia = excel.Run("add", 3, 7) # 引数整数 戻り値整数
puts "加算値=" + ia.to_s
sa = excel.Run("inputStr", "名前を入力して下さい。") # 引数文字列 戻り値文字列
puts "名前:" + sa.encode("utf-8")
excel.quit
履歴(excelUtil.rb)