Ruby で Excel

Ruby Excel COM 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

Excel シートの内容を 変更する (追加)

lesson009.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
# 追加

sql  = "INSERT INTO "

sql += "    [" +ARGV[1] + "$] "

sql += "( "

sql += "     A1,   B1,   C1,   D1,   E1,   F1,   G1,   H1  "

sql += ") "

sql += "VALUES "

sql += "( " 

sql += "    'AX', 'BX', 'CX', 'DX', 'EX', 'FX', 'GX', 'HX' "

sql += ")"

cn.Execute(sql)
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\lesson009.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

AX,BX,CX,DX,EX,FX,GX,HX,,

Excel シートの内容を 変更する (変更)

lesson010.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
# 変更

sql  = "UPDATE "

sql += "    [" + ARGV[1] + "$] "

sql += "SET "

sql += "     A1 = 'AZ'"

sql += ",    B1 = 'BZ'"

sql += ",    C1 = 'CZ'"

sql += ",    D1 = 'DZ'"

sql += ",    E1 = 'EZ'"

sql += ",    F1 = 'FZ'"

sql += ",    G1 = 'GZ'"

sql += ",    H1 = 'HZ'"

sql += "WHERE "

sql += "     A1 = 'A7'"

cn.Execute(sql)
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\lesson010.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

AZ,BZ,CZ,DZ,EZ,FZ,GZ,HZ,I7,J7

AX,BX,CX,DX,EX,FX,GX,HX,,

Excel シートの内容を 変更する (削除)

lesson011.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
# 削除

sql  = "DELETE FROM "

sql += "    [" + ARGV[1] + "$] "

sql += "WHERE "

sql += "     A1 = 'A2'"

cn.Execute(sql)
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\lesson011.rb c:\study\Book1.xls Sheet1

c:/study/ruby/chapter004/lesson011.rb:14:in `method_missing': Execute (WIN32OLER

untimeError)

    OLE error code:80004005 in Microsoft JET Database Engine

      この ISAM では、リンク テーブル内のデータを削除することはできません。

    HRESULT error code:0x80020009

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