Perl で Excel

Perl 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.pl



use Win32::OLE;
$cn = Win32::OLE->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 "

        . "    [".$ARGV[1].'$] '

        . "( "

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

        . ") "

        . "VALUES "

        . "( " 

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

        . ")";

$cn->Execute($sql);
$rs = Win32::OLE->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 = ();

    foreach $iCol (0..$rs->Fields->Count - 1)

    {

        push @rec, $rs->Fields($iCol)->Value;

    }

    print join(",", @rec), "\n";
    $rs->MoveNext();

}
$rs->Close;

$cn->Close;
実行結果


C:\>perl c:\study\perl\chapter004\lesson009.pl 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.pl



use Win32::OLE;
$cn = Win32::OLE->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 "

        . "    [".$ARGV[1].'$] '

        . "SET "

        . "     A1 = 'AZ'"

        . ",    B1 = 'BZ'"

        . ",    C1 = 'CZ'"

        . ",    D1 = 'DZ'"

        . ",    E1 = 'EZ'"

        . ",    F1 = 'FZ'"

        . ",    G1 = 'GZ'"

        . ",    H1 = 'HZ'"

        . "WHERE "

        . "     A1 = 'A7'";

$cn->Execute($sql);
$rs = Win32::OLE->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 = ();

    foreach $iCol (0..$rs->Fields->Count - 1)

    {

        push @rec, $rs->Fields($iCol)->Value;

    }

    print join(",", @rec), "\n";
    $rs->MoveNext();

}
$rs->Close;

$cn->Close;
実行結果


C:\>perl c:\study\perl\chapter004\lesson010.pl 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.pl



use Win32::OLE;
$cn = Win32::OLE->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 "

        . "    [".$ARGV[1].'$] '

        . "WHERE "

        . "     A1 = 'A2'";

$cn->Execute($sql);
$rs = Win32::OLE->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 = ();

    foreach $iCol (0..$rs->Fields->Count - 1)

    {

        push @rec, $rs->Fields($iCol)->Value;

    }

    print join(",", @rec), "\n";
    $rs->MoveNext();

}
$rs->Close;

$cn->Close;
実行結果


C:\>perl c:\study\perl\chapter004\lesson011.pl 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,,
特にエラーは出ないが、削除されていないので、エラーを表示してみる


use Win32::OLE;
$cn = Win32::OLE->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 "

        . "    [".$ARGV[1].'$] '

        . "WHERE "

        . "     A1 = 'A2'";

$cn->Execute($sql);

if (Win32::OLE->LastError())

{

    print Win32::OLE->LastError();

    exit;

}
$rs = Win32::OLE->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 = ();

    foreach $iCol (0..$rs->Fields->Count - 1)

    {

        push @rec, $rs->Fields($iCol)->Value;

    }

    print join(",", @rec), "\n";
    $rs->MoveNext();

}
$rs->Close;

$cn->Close;
実行結果


C:\>perl c:\study\perl\chapter004\lesson011.pl c:\study\Book1.xls Sheet1

OLE exception from "Microsoft JET Database Engine":
この ISAM では、リンク テーブル内のデータを削除することはできません。
Win32::OLE(0.1707) error 0x80004005: "エラー"

    in METHOD/PROPERTYGET "Execute"