PHP で Excel

PHP 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.php



<?php

    $cn = new COM("ADODB.Connection");

    $cn->Provider         = "Microsoft.Jet.OLEDB.4.0";

    $cn->ConnectionString = "Data Source=".$argv[1].";Extended Properties=Excel 8.0;";

    $cn->CursorLocation   = 3; # adUseClient

    $cn->Open;
    # 追加

    $sql    = "INSERT INTO "

            . "    [".$argv[2]."$] "

            . "( "

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

            . ") "

            . "VALUES "

            . "( " 

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

            . ")";

    $cn->Execute($sql);
    $rs = new COM("ADODB.Recordset");

    $rs->ActiveConnection = $cn;

    $rs->CursorType       = 0; # adOpenForwardOnly

    $rs->LockType         = 1; # adLockReadOnly
    $sql = "SELECT * FROM [".$argv[2]."$]";

    $rs->Open($sql);
    while (!$rs->EOF)

    {

        $rec = array();

        foreach ($rs->Fields as $col)

        {

            array_push($rec, $col->Value);

        }

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

    }
    $rs->Close;

    $cn->Close;

?>
実行結果


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



<?php

    $cn = new COM("ADODB.Connection");

    $cn->Provider         = "Microsoft.Jet.OLEDB.4.0";

    $cn->ConnectionString = "Data Source=".$argv[1].";Extended Properties=Excel 8.0;";

    $cn->CursorLocation   = 3; # adUseClient

    $cn->Open;
    # 変更

    $sql    = "UPDATE "

            . "    [".$argv[2]."$] "

            . "SET "

            . "     A1 = 'AZ'"

            . ",    B1 = 'BZ'"

            . ",    C1 = 'CZ'"

            . ",    D1 = 'DZ'"

            . ",    E1 = 'EZ'"

            . ",    F1 = 'FZ'"

            . ",    G1 = 'GZ'"

            . ",    H1 = 'HZ'"

            . "WHERE "

            . "     A1 = 'A7'";

    $cn->Execute($sql);
    $rs = new COM("ADODB.Recordset");

    $rs->ActiveConnection = $cn;

    $rs->CursorType       = 0; # adOpenForwardOnly

    $rs->LockType         = 1; # adLockReadOnly
    $sql = "SELECT * FROM [".$argv[2]."$]";

    $rs->Open($sql);
    while (!$rs->EOF)

    {

        $rec = array();

        foreach ($rs->Fields as $col)

        {

            array_push($rec, $col->Value);

        }

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

    }
    $rs->Close;

    $cn->Close;

?>
実行結果


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



<?php

    $cn = new COM("ADODB.Connection");

    $cn->Provider         = "Microsoft.Jet.OLEDB.4.0";

    $cn->ConnectionString = "Data Source=".$argv[1].";Extended Properties=Excel 8.0;";

    $cn->CursorLocation   = 3; # adUseClient

    $cn->Open;
    # 削除

    $sql    = "DELETE FROM "

            . "    [".$argv[2]."$] "

            . "WHERE "

            . "     A1 = 'A2'";

    $cn->Execute($sql);
    $rs = new COM("ADODB.Recordset");

    $rs->ActiveConnection = $cn;

    $rs->CursorType       = 0; # adOpenForwardOnly

    $rs->LockType         = 1; # adLockReadOnly
    $sql = "SELECT * FROM [".$argv[2]."$]";

    $rs->Open($sql);
    while (!$rs->EOF)

    {

        $rec = array();

        foreach ($rs->Fields as $col)

        {

            array_push($rec, $col->Value);

        }

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

    }
    $rs->Close;

    $cn->Close;

?>
実行結果


C:\>php c:\study\php\chapter004\lesson011.php c:\study\Book1.xls Sheet1
Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft

JET Database Engine

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

。' in C:\study\php\chapter004\lesson011.php:13

Stack trace:

#0 C:\study\php\chapter004\lesson011.php(13): com->Execute('DELETE FROM    ...')
#1 {main}

  thrown in C:\study\php\chapter004\lesson011.php on line 13