PHP で Excel

PHP Excel COM ADO

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.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;
    $rs = new COM("ADODB.Recordset");

    $rs->Source           = "SELECT * FROM [".$argv[2]."$]";

    $rs->ActiveConnection = $cn;

    $rs->CursorType       = 0; # adOpenForwardOnly

    $rs->LockType         = 1; # adLockReadOnly

    $rs->Open;
    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\lesson008.php c:\study\Book1.xls Sheet1
Fatal error: Uncaught exception 'com_exception' with message 'Source: Provider

Description: 種類が一致しません。' in C:\study\php\chapter004\lesson008.php:9

Stack trace:

#0 C:\study\php\chapter004\lesson008.php(9): unknown()

#1 {main}

  thrown in C:\study\php\chapter004\lesson008.php on line 9
何故か、エラーになるので、ちょいと変更


<?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;
    $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\lesson008.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