Ruby:Excelを操作するAPIの紹介
言叶というものはやはり用事があって书く必要があるので、仕事で使うシナリオ言叶をルビーに変えようと思っています.
プロジェクトの必要性、先日書いたばかりのC#API:Excelファイルの生成と読み込み
ついでにRubyがどのようにExcelを操作しているかも見てみましょう.
ネットで検索して、excelの使い方を示す例として、
このリンクは私の大好きなリンクの一つです:win 32 oleでExcel-2を操作する
この例は簡単です.sourceだけで、次の操作を説明します.
(本明細書の最後の他のリソースセクションでは、より詳細で豊富なコンテンツへのリンクを示します)
■sheetの名前を変更し、名前でsheetをインデックスする
■セルの内容をExcel式に設定するなど、セルの内容を変更する
■セルのフォントのサイズと色を変更する
■セルの背景色を変更する
■セルのその他の書式を変更する(日時の表示方法、表示方法は左か右かなど)
■セルにコメントを追加
■セルへのハイパーリンクの指定
■セルのマージ
■セルのグリッド線の変更
■ウィンドウロックの設定方法
■......
これらの機能は私のニーズを満たすのに十分で、読みやすくて使いやすいExcelドキュメントを生成します.
ただ元のリンクはsourceしか提供していないので、検索エンジンで彼を検索する人は少ないです.
コード転送は以下の通りです.
あまり話さないで、最後まで見たいので、次のコードを書いてDebugを始めましょう.
実行すると以下のコードの中で@@TestcaseName=@@aの@@aが定義されていないことがわかります.
@@aを任意に文字列に置き換えるだけでいいので、自分でできると信じています.
その他のリソース
リソース1
Programming Ruby: Ruby and Microsoft Windows
電子書籍のダウンロードも可能で、この章ではWindows Automationについての議論があります
リソース2
The Ruby Spreadsheet
spreadsheet @rubygems.org
How To Generate Excel Files with Ruby
もう一つのapiは、excel、open officeなどを操作できます.
リソース3
RubyGarden Archives: Scripting Excel@Ruby on Windows
私はこの文章が大好きです.彼は一番前の例とは違って、完全な例ではありません.
一つの質問と対応する答えです(もしあなたがこのことをしたいなら、そうします...)
私が一番好きなのは次のコードです.
Excelを設定する際に使われる数字はいくつかありますが、
私が望む効果は、対応する設定数字がいくらなのか、この問題はずっと私を困惑させています.
このスクリプトを使用すると、Excelで定義されている定数とその値を調べることができます.
たとえば、Centerパラメータを使用して、次のスクリプトを実行します.
ruby search_excel_consts.rb Center
私たちはこのような結果を得ることができます.
XlCenterAcrossSelection => 7
XlVAlignCenter => -4108
XlCenter => -4108
XlLabelPositionCenter => -4108
XlPhoneticAlignCenter => 2
XlHAlignCetner => -4108
XlHAlignCenterAcrossSelection => 7
さらに素晴らしいことに、すべての定数をクラスにインポートすると、
これらの定数を使用して、幻の数字の代わりにExcelを設定し、操作することができます.
例えばこのように(ExcelConst::XlColorIndexNoneの使用に注意)...
リソース4
Color Palette and the 56 Excel ColorIndex Colors
Excel Color Palette and Color Index change using VBA
色を設定する場合は、Colorを直接設定するか、対応するColorIndexを設定できます.
私はColorIndexをもっと使いたいです.ColorIndexと対応する色の色表については、上記の2つの接続を参考にすることができます.
最初の接続は特に詳細で、
2番目の接続では、VBAを使用してExcelファイルでこれらのカラーテーブルを生成する方法と、xls形式のカラーテーブルファイルのダウンロード方法について説明します.
リソース5
Excelに対してすべて読み取り操作であれば、このgemを試してみてください.
http://roo.rubyforge.org/
http://roo.rubyforge.org/rdoc/index.html
彼は次の4つのファイルフォーマットの読み取り操作をサポートしています.
■Open-office spreadsheets (.ods) ■Excel spreadsheets (.xls) ■Google (online) spreadsheets ■Excel’s new file format .xlsx
プロジェクトの必要性、先日書いたばかりのC#API:Excelファイルの生成と読み込み
ついでにRubyがどのようにExcelを操作しているかも見てみましょう.
ネットで検索して、excelの使い方を示す例として、
このリンクは私の大好きなリンクの一つです:win 32 oleでExcel-2を操作する
この例は簡単です.sourceだけで、次の操作を説明します.
(本明細書の最後の他のリソースセクションでは、より詳細で豊富なコンテンツへのリンクを示します)
■sheetの名前を変更し、名前でsheetをインデックスする
■セルの内容をExcel式に設定するなど、セルの内容を変更する
■セルのフォントのサイズと色を変更する
■セルの背景色を変更する
■セルのその他の書式を変更する(日時の表示方法、表示方法は左か右かなど)
■セルにコメントを追加
■セルへのハイパーリンクの指定
■セルのマージ
■セルのグリッド線の変更
■ウィンドウロックの設定方法
■......
これらの機能は私のニーズを満たすのに十分で、読みやすくて使いやすいExcelドキュメントを生成します.
ただ元のリンクはsourceしか提供していないので、検索エンジンで彼を検索する人は少ないです.
コード転送は以下の通りです.
require 'win32ole'
class Engines
def time_now
initialize
@t =Time.now
a = @t.to_s.split(" ")
s = a[-1].to_s+'-'+"#{@t.mon}"+'-'+a[2].to_s
@time_now= s+' '+a[3].to_s
#@[email protected]+''[email protected]+''[email protected]
@time_now_hsm =a[3].to_s
end
def initialize
@OnlyInitOnce = true
@t
@objSheet
@excel
@sStatus
@sStepName
@sStepName
@sStepName
@sDetails
@@TestcaseName=@@a
end
def excel_new(encoding="utf-8")
initialize
@worksheets_name =[]
@excel = WIN32OLE::new("EXCEL.APPLICATION")
@excel.visible=true
@workbook = @excel.Workbooks.Add()
@encoding = encoding
end
def excelsheet_name(name)
while @@worksheets_name.include?(name)
name +="1"
end
@@worksheets_name << name
worksheet = @workbook.Worksheets.Add()
worksheet.Activate
worksheet.name = name
end
def excel_quit
@excel.Quit # Excel
# @workbook.close sheet
# exec('taskkill /f /im Excel.exe ') Excel
end
def CreateResultFile(filepath)
excel_new
@excel.DisplayAlerts = false
@objSheet = @excel.Sheets.Item(1)
@excel.Sheets.Item(1).Select
@objSheet.Name = " "
@objSheet.Range("B1").Value = " "
#
@objSheet.Range("B1:E1").Merge
# -4108
@objSheet.Range("B1:E1").HorizontalAlignment = -4108
@objSheet.Range("B1:E1").Interior.ColorIndex = 53
@objSheet.Range("B1:E1").Font.ColorIndex = 5
@objSheet.Range("B1:E1").Font.Bold = true
@objSheet.Range("B1:E1").Font.Size =24
@objSheet.Range("B2:E2").Merge
@objSheet.Rows(2).RowHeight = 20
rowNum = [3,4,5,6,7,8]
rowNum.each {|re| @objSheet.Range("C#{re}:E#{re}").Merge}
@objSheet.Range("B9:E9").Merge
@objSheet.Rows(9).RowHeight = 30
#Set the Date and time of Execution
@objSheet.Range("B3").Value = " : "
@objSheet.Range("B4").Value = " : "
@objSheet.Range("B5").Value = " : "
@objSheet.Range("B6").Value = " : "
#@objSheet.Range("C3").Value = Date
@objSheet.Range("C4").Value = time_now
@objSheet.Range("C5").Value = time_now
@objSheet.Range("C6").Value = "=R[-1]C-R[-2]C"
@objSheet.Range("C6").NumberFormat ="[h]:mm:ss;@"
#Set the Borders for the Date & Time Cells
@objSheet.Range("B3:E8").Borders(1).LineStyle = 1
@objSheet.Range("B3:E8").Borders(2).LineStyle = 1
@objSheet.Range("B3:E8").Borders(3).LineStyle = 1
@objSheet.Range("B3:E8").Borders(4).LineStyle = 1
#Format the Date and Time Cells
@objSheet.Range("B3:E8").Interior.ColorIndex = 40
@objSheet.Range("B3:E8").Font.ColorIndex = 12
@objSheet.Range("B3:A8").Font.Bold = true
#Track the Row Count and insrtuct the viewer not to disturb this
@objSheet.Range("C7").AddComment
@objSheet.Range("C7").Comment.Visible = false
@objSheet.Range("C7").Comment.Text " "
@objSheet.Range("C7").Value = "0"
@objSheet.Range("B7").Value = " :"
#Track the Testcase Count Count and insrtuct the viewer not to disturb this
@objSheet.Range("C8").AddComment
@objSheet.Range("C8").Comment.Visible = false
@objSheet.Range("C8").Comment.Text " "
@objSheet.Range("C8").Value = "0"
@objSheet.Range("B8").Value = " :"
@objSheet.Range("B10").Value = " "
@objSheet.Range("D10").Value = " "
@objSheet.Range("E10").Value = " "
@objSheet.Hyperlinks.Add(@objSheet.Range("B9"), ""," !A1")
@objSheet.Range("B9").Value = " ."
# @objSheet.Hyperlinks.Add(@objSheet.Range("B9"), "http://www.163.com")
#Format the Heading for the Result Summery
@objSheet.Range("B10:C10").Merge
@objSheet.Range("B10:E10").Interior.ColorIndex = 53
@objSheet.Range("B10:E10").Font.ColorIndex = 19
@objSheet.Range("B10:E10").Font.Bold = true
#Set the Borders for the Result Summery
@objSheet.Range("B10:E10").Borders(1).LineStyle = 1
@objSheet.Range("B10:E10").Borders(2).LineStyle = 1
@objSheet.Range("B10:E10").Borders(3).LineStyle = 1
@objSheet.Range("B10:E10").Borders(4).LineStyle = 1
#Set Column width
@objSheet.Columns("B:E").Select
#@objSheet.Columns("B:D").Autofit
@objSheet.Range("B11").Select
@objSheet.Range("B11").ColumnWidth=12
@objSheet.Range("C11").ColumnWidth=50
@objSheet.Range("D11").ColumnWidth=15
@objSheet.Range("E11").ColumnWidth=15
#Freez pane
@excel.ActiveWindow.FreezePanes = true
#Get the object of the first sheet in the workbook
@objSheet = @excel.Sheets.Item(2)
@excel.Sheets.Item(1).Select
#Rename the first sheet to "Test_Result"
@objSheet.Name = " "
#Set the Column widths
@objSheet.Columns("A:A").ColumnWidth = 30
@objSheet.Columns("B:B").ColumnWidth = 8
@objSheet.Columns("C:D").ColumnWidth = 35
@objSheet.Columns("E:E").ColumnWidth = 35
@objSheet.Columns("A:E").HorizontalAlignment = -4131
@objSheet.Columns("A:E").WrapText = true
#Set the Heading for the Result Columns
@objSheet.Range("A1").Value = " "
@objSheet.Range("B1").Value = " "
@objSheet.Range("C1").Value = " "
@objSheet.Range("D1").Value = " "
@objSheet.Range("E1").Value = " "
#Format the Heading for the Result Columns
@objSheet.Range("A1:E1").Interior.ColorIndex = 53
@objSheet.Range("A1:E1").Font.ColorIndex = 19
@objSheet.Range("A1:E1").Font.Bold = true
#Set the Borders for the Result Header
@objSheet.Range("A1:E1").Borders(1).LineStyle = 1
@objSheet.Range("A1:E1").Borders(2).LineStyle = 1
@objSheet.Range("A1:E1").Borders(3).LineStyle = 1
@objSheet.Range("A1:E1").Borders(4).LineStyle = 1
# .Range("A2").Select
#Freez pane
@excel.ActiveWindow.FreezePanes = true
@objSheet = @excel.Sheets.Item(3)
@excel.Sheets.Item(1).Select
@objSheet.Name = " "
@objSheet.Columns("A:A").ColumnWidth = 100
@objSheet.Rows("2:2").RowHeight = 150
@objSheet.Range("A1:A1").Font.Bold = true
@objSheet.Range("A1").Value = " "
@objSheet.Range("A2").Value = " "
@excel.ActiveWindow.FreezePanes = true
#Save the Workbook at the specified Path with the Specified Name
@excel.ActiveWorkbook.saveas "#{filepath}"
@workbook.close
end
def reporter (sStatus, sStepName,sExpected,sActual, sDetails)
#path = File.join(File.dirname(__FILE__))
# excel , excel : .xls
#data_source = File.join(path,' .xls')
#@@TestcaseName =@@a #__FILE__
@WorkBookopen= @excel.Workbooks.Open("D:\\test.xls")
@objSheet = @excel.Sheets(" ")
@excel.Sheets(" ").Select
@Row = (@objSheet.Range("C8").Value + 2*@objSheet.Range("C7").Value + 2).to_i
@TCRow = (@objSheet.Range("C7").Value + 11).to_i
@NewTC = false
@objSheet.Range("B#{@Row+9}:C#{@Row+9}").Merge
#Check if it is a new Tetstcase
if @objSheet.Cells(@TCRow-1, 2).Value != @@TestcaseName
@objSheet.Range("B#{@Row+9}:C#{@Row+9}").Merge
@objSheet.Cells(@TCRow, 2).Value = @@TestcaseName
@objSheet.Hyperlinks.Add @objSheet.Cells(@TCRow, 2), "", " !A#{ @Row+1}", @@TestcaseName
@objSheet.Cells(@TCRow,4).Value = sStatus
case sStatus
when "Fail"
@objSheet.Range("D#{@TCRow}").Font.ColorIndex = 3
when "Pass"
@objSheet.Range("D#{@TCRow}").Font.ColorIndex = 50
when "Warning"
@objSheet.Range("D#{@TCRow}").Font.ColorIndex = 46
else
puts " : Fail or Pass or Warning "
end
@objSheet.Cells(@TCRow, 5).Value = 1
@NewTC = true
@objSheet.Range("C7").Value = @objSheet.Range("C7").Value + 1
#Set the Borders for the Result Header
@objSheet.Range("B#{@TCRow}:E#{@TCRow}").Borders(1).LineStyle = 1
@objSheet.Range("B#{@TCRow}:E#{@TCRow}").Borders(2).LineStyle = 1
@objSheet.Range("B#{@TCRow}:E#{@TCRow}").Borders(3).LineStyle = 1
@objSheet.Range("B#{@TCRow}:E#{@TCRow}").Borders(4).LineStyle = 1
#Set color and Fonts for the Header
@objSheet.Range("B#{@TCRow}:E#{@TCRow}").Interior.ColorIndex = 19
@objSheet.Range("B#{@TCRow}").Font.ColorIndex = 53
@objSheet.Range("B#{@TCRow}:E#{@TCRow}").Font.Bold = true
else
@objSheet.Range("E#{@TCRow-1}").Value = (@objSheet.Range("E#{@TCRow-1}").Value) + 1
end
if (@NewTC!=true) and (sStatus == "Fail")
@objSheet.Cells(@TCRow-1, 4).Value = "Fail"
@objSheet.Range("D#{@TCRow-1}").Font.ColorIndex = 3
end
if (@NewTC!=true) and (sStatus == "Warning")
if @objSheet.Cells(@TCRow-1, 4).Value == "Pass"
@objSheet.Cells(@TCRow-1, 4).Value = "Warning"
@objSheet.Range("D#{@TCRow-1}").Font.ColorIndex = 46
end
end
@objSheet.Range("C8").Value = @objSheet.Range("C8").Value + 1
#Update the End Time
@objSheet.Range("C5").Value = time_now
#Set Column width
@objSheet.Columns("B:E").Select
@objSheet.Columns("B:E").Autofit
#Select the Result Sheet
@objSheet = @excel.Sheets(" ")
@excel.Sheets(" ").Select
#Enter the Result
if @NewTC
@objSheet.Range("A#{@Row}:E#{@Row}").Interior.ColorIndex = 15
@objSheet.Range("A#{@Row}:E#{@Row}").Merge
@Row = @Row + 1
@objSheet.Range("A#{@Row}:E#{@Row}").Merge
@objSheet.Range("A#{@Row}").Value = @@TestcaseName
#Set color and Fonts for the Header
@objSheet.Range("A#{@Row}:E#{@Row}").Interior.ColorIndex = 19
@objSheet.Range("A#{@Row}:E#{@Row}").Font.ColorIndex = 53
@objSheet.Range("A#{@Row}:E#{@Row}").Font.Bold = true
@Row = @Row + 1
end
@objSheet.Range("A#{@Row}").Value = sStepName
@objSheet.Range("B#{@Row}").Value = sStatus
@objSheet.Range("B#{@Row}").Font.Bold = true
case sStatus
when "Pass"
@objSheet.Range("B#{@Row}").Font.ColorIndex = 50
@objSheet.Range("B#{@Row}").Font.Bold = true
when "Fail"
@objSheet.Range("A#{@Row}:E#{@Row}").Font.ColorIndex = 3
when "Warning"
@objSheet.Range("A#{@Row}:E#{@Row}").Font.ColorIndex = 46
else
puts " : Fail or Pass or Warning "
end
@objSheet.Range("B#{@Row}").Font.Bold = true
@objSheet.Range("C#{@Row}").Value = sExpected
@objSheet.Range("D#{@Row}").Value = sActual
@objSheet.Range("E#{@Row}").Value = sDetails
#Set the Borders
@objSheet.Range("A#{@Row}:E#{@Row}").Borders(1).LineStyle = 1
@objSheet.Range("A#{@Row}:E#{@Row}").Borders(2).LineStyle = 1
@objSheet.Range("A#{@Row}:E#{@Row}").Borders(3).LineStyle = 1
@objSheet.Range("A#{@Row}:E#{@Row}").Borders(4).LineStyle = 1
@objSheet.Range("A#{@Row}:E#{@Row}").VerticalAlignment = -4160
@excel.Sheets(" ").Select
@excel.Sheets(" ").Range("B1").Select
#Save the Workbook
@WorkBookopen.save
end
end
あまり話さないで、最後まで見たいので、次のコードを書いてDebugを始めましょう.
実行すると以下のコードの中で@@TestcaseName=@@aの@@aが定義されていないことがわかります.
@@aを任意に文字列に置き換えるだけでいいので、自分でできると信じています.
engines = Engines.new()
engines.CreateResultFile("c:\\test.xls")
その他のリソース
リソース1
Programming Ruby: Ruby and Microsoft Windows
電子書籍のダウンロードも可能で、この章ではWindows Automationについての議論があります
リソース2
The Ruby Spreadsheet
spreadsheet @rubygems.org
How To Generate Excel Files with Ruby
もう一つのapiは、excel、open officeなどを操作できます.
リソース3
RubyGarden Archives: Scripting Excel@Ruby on Windows
私はこの文章が大好きです.彼は一番前の例とは違って、完全な例ではありません.
一つの質問と対応する答えです(もしあなたがこのことをしたいなら、そうします...)
私が一番好きなのは次のコードです.
Excelを設定する際に使われる数字はいくつかありますが、
私が望む効果は、対応する設定数字がいくらなのか、この問題はずっと私を困惑させています.
このスクリプトを使用すると、Excelで定義されている定数とその値を調べることができます.
require 'win32ole'
module ExcelConsts
end
excel = WIN32OLE.new("Excel.Application")
WIN32OLE.const_load(excel, ExcelConsts)
excel.quit()
puts 'Matches for: ' + ARGV[0]
ExcelConsts.constants.each {|const|
match = const.match(/#{ARGV[0]}/)
value = eval("ExcelConsts::#{const}")
puts ' '*4 + const + ' => ' + value.to_s unless match.nil?
}
たとえば、Centerパラメータを使用して、次のスクリプトを実行します.
ruby search_excel_consts.rb Center
私たちはこのような結果を得ることができます.
XlCenterAcrossSelection => 7
XlVAlignCenter => -4108
XlCenter => -4108
XlLabelPositionCenter => -4108
XlPhoneticAlignCenter => 2
XlHAlignCetner => -4108
XlHAlignCenterAcrossSelection => 7
さらに素晴らしいことに、すべての定数をクラスにインポートすると、
これらの定数を使用して、幻の数字の代わりにExcelを設定し、操作することができます.
例えばこのように(ExcelConst::XlColorIndexNoneの使用に注意)...
worksheet.Range('a3:f5').Interior['ColorIndex'] = 36 #pale yellow
# Set background color back to uncoloured (rnicz)
worksheet.Range('a3:f5').Interior['ColorIndex'] = -4142 # XlColorIndexNone constant
# or use Excel constant to set background color back to uncoloured
worksheet.Range('a3:f5').Interior['ColorIndex'] = ExcelConst::XlColorIndexNone
リソース4
Color Palette and the 56 Excel ColorIndex Colors
Excel Color Palette and Color Index change using VBA
色を設定する場合は、Colorを直接設定するか、対応するColorIndexを設定できます.
私はColorIndexをもっと使いたいです.ColorIndexと対応する色の色表については、上記の2つの接続を参考にすることができます.
最初の接続は特に詳細で、
2番目の接続では、VBAを使用してExcelファイルでこれらのカラーテーブルを生成する方法と、xls形式のカラーテーブルファイルのダウンロード方法について説明します.
@objSheet.Range("B1:E1").Interior.Color = 255 # red
@objSheet.Range("B1:E1").Interior.ColorIndex = 3 # red
リソース5
Excelに対してすべて読み取り操作であれば、このgemを試してみてください.
http://roo.rubyforge.org/
http://roo.rubyforge.org/rdoc/index.html
彼は次の4つのファイルフォーマットの読み取り操作をサポートしています.
■Open-office spreadsheets (.ods) ■Excel spreadsheets (.xls) ■Google (online) spreadsheets ■Excel’s new file format .xlsx