JScript で Excel

JScript 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.js



var cn = WScript.CreateObject("ADODB.Connection");
cn.Provider         = "Microsoft.Jet.OLEDB.4.0";

cn.ConnectionString = "Data Source=" + WScript.Arguments(0) + ";Extended Properties=Excel 8.0;";

cn.CursorLocation   = 3; // adUseClient

cn.Open;
// 追加

var sql = "INSERT INTO "

        + "    [" + WScript.Arguments(1) + "$] "

        + "( "

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

        + ") "

        + "VALUES "

        + "( " 

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

        + ")"

cn.Execute(sql);
var rs              = WScript.CreateObject("ADODB.Recordset");

rs.Source           = "SELECT * FROM [" + WScript.Arguments(1) + "$]";

rs.Activeconnection = cn;

rs.CursorType       = 0; // adOpenForwardOnly

rs.LockType         = 1; // adLockReadOnly

rs.Open
while (!rs.EOF)

{

    rec = new Array();

    for (var i = 0; i < rs.Fields.Count; i++)

    {

        rec.push(rs.Fields(i).Value);

    }

    WScript.Echo(rec.join(","));

    rs.MoveNext;

}
rs.Close;

cn.Close;
実行結果


C:\>cscript c:\study\jscript\chapter004\lesson009.js c:\study\Book1.xls Sheet1 /

/nologo

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.js



var cn = WScript.CreateObject("ADODB.Connection");
cn.Provider         = "Microsoft.Jet.OLEDB.4.0";

cn.ConnectionString = "Data Source=" + WScript.Arguments(0) + ";Extended Properties=Excel 8.0;";

cn.CursorLocation   = 3; // adUseClient

cn.Open;
// 変更

var sql = "UPDATE "

        + "    [" + WScript.Arguments(1) + "$] "

        + "SET "

        + "     A1 = 'AZ'"

        + ",    B1 = 'BZ'"

        + ",    C1 = 'CZ'"

        + ",    D1 = 'DZ'"

        + ",    E1 = 'EZ'"

        + ",    F1 = 'FZ'"

        + ",    G1 = 'GZ'"

        + ",    H1 = 'HZ'"

        + "WHERE "

        + "     A1 = 'A7'"

cn.Execute(sql);
var rs              = WScript.CreateObject("ADODB.Recordset");

rs.Source           = "SELECT * FROM [" + WScript.Arguments(1) + "$]";

rs.Activeconnection = cn;

rs.CursorType       = 0; // adOpenForwardOnly

rs.LockType         = 1; // adLockReadOnly

rs.Open
while (!rs.EOF)

{

    rec = new Array();

    for (var i = 0; i < rs.Fields.Count; i++)

    {

        rec.push(rs.Fields(i).Value);

    }

    WScript.Echo(rec.join(","));

    rs.MoveNext;

}
rs.Close;

cn.Close;
実行結果


C:\>cscript c:\study\jscript\chapter004\lesson010.js c:\study\Book1.xls Sheet1 /

/nologo

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.js



var cn = WScript.CreateObject("ADODB.Connection");
cn.Provider         = "Microsoft.Jet.OLEDB.4.0";

cn.ConnectionString = "Data Source=" + WScript.Arguments(0) + ";Extended Properties=Excel 8.0;";

cn.CursorLocation   = 3; // adUseClient

cn.Open;
// 削除

var sql = "DELETE FROM "

        + "    [" + WScript.Arguments(1) + "$] "

        + "WHERE "

        + "     A1 = 'A2'"

cn.Execute(sql);
var rs              = WScript.CreateObject("ADODB.Recordset");

rs.Source           = "SELECT * FROM [" + WScript.Arguments(1) + "$]";

rs.Activeconnection = cn;

rs.CursorType       = 0; // adOpenForwardOnly

rs.LockType         = 1; // adLockReadOnly

rs.Open
while (!rs.EOF)

{

    rec = new Array();

    for (var i = 0; i < rs.Fields.Count; i++)

    {

        rec.push(rs.Fields(i).Value);

    }

    WScript.Echo(rec.join(","));

    rs.MoveNext;

}
rs.Close;

cn.Close;
実行結果


C:\>cscript c:\study\jscript\chapter004\lesson011.js c:\study\Book1.xls Sheet1 /

/nologo

c:\study\jscript\chapter004\lesson011.js(13, 1) Microsoft JET Database Engine:

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