Ruby で Excel

Excel シートの内容を 取得する (ADO)

↓ こんな Excel ファイルを読む

	A	B	C	D	E	F	G	H	I	J
1	A1	B1	C1	D1	E1	F1	G1	H1		J1
2	A2	B2	C2	D2	E2	F2	G2	H2		J2
3	A3	B3	C3	D3	E3	F3	G3	H3		J3
4	A4	B4	C4	D4	E4	F4	G4	H4		J4
5	A5	B5	C5	D5	E5	F5	G5	H5		J5
6										J6
7	A7	B7	C7	D7	E7	F7	G7	H7	I7	J7

lesson008.rb

require 'win32ole'
cn = WIN32OLE.new("ADODB.Connection")

cn.Provider         = "Microsoft.Jet.OLEDB.4.0"

cn.ConnectionString = "Data Source=" + ARGV[0] + ";Extended Properties=Excel 8.0;"

cn.CursorLocation   = 3 # adUseClient

cn.Open
rs = WIN32OLE.new("ADODB.Recordset")

rs.Source           = "SELECT * FROM [" + ARGV[1] + "$]"

rs.Activeconnection = cn

rs.CursorType       = 0 # adOpenForwardOnly

rs.LockType         = 1 # adLockReadOnly

rs.Open
while !rs.EOF

    rec = []

    rs.Fields.each do |col|

        rec.push(col.Value)

    end

    puts rec.join(",")
    rs.MoveNext

end
rs.Close

cn.Close

実行結果

C:\>ruby c:\study\ruby\chapter004\lesson008.rb c:\study\Book1.xls Sheet1

c:/study/ruby/chapter004/lesson008.rb:10:in `method_missing':  (WIN32OLERuntimeE

rror)

    OLE error code:80020005 in Provider

      種類が一致しません。

    HRESULT error code:0x80020009

      例外が発生しました。      from c:/study/ruby/chapter004/lesson008.rb:10

何故か、エラーになるので、ちょいと変更

require 'win32ole'
cn = WIN32OLE.new("ADODB.Connection")

cn.Provider         = "Microsoft.Jet.OLEDB.4.0"

cn.ConnectionString = "Data Source=" + ARGV[0] + ";Extended Properties=Excel 8.0;"

cn.CursorLocation   = 3 # adUseClient

cn.Open
rs = WIN32OLE.new("ADODB.Recordset")

rs.Activeconnection = cn

rs.CursorType       = 0 # adOpenForwardOnly

rs.LockType         = 1 # adLockReadOnly
sql = "SELECT * FROM [" + ARGV[1] + "$]"

rs.Open sql
while !rs.EOF

    rec = []

    rs.Fields.each do |col|

        rec.push(col.Value)

    end

    puts rec.join(",")
    rs.MoveNext

end
rs.Close

cn.Close

実行結果

C:\>ruby c:\study\ruby\chapter004\lesson008.rb c:\study\Book1.xls Sheet1

A2,B2,C2,D2,E2,F2,G2,H2,,J2

A3,B3,C3,D3,E3,F3,G3,H3,,J3

A4,B4,C4,D4,E4,F4,G4,H4,,J4

A5,B5,C5,D5,E5,F5,G5,H5,,J5

,,,,,,,,,J6

A7,B7,C7,D7,E7,F7,G7,H7,I7,J7