home > OLE オートメーション > Excel >

ForNext

Only Do What Only You Can Do
home | index | 前へ | 次へ

Excel シートの内容を 取得する

VBScript

更新日 : 2008.05.23
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible       = True
objExcel.DisplayAlerts = False '警告メッセージをOFF

'ブックを読み取り専用で開く
Dim book
Set book = objExcel.Workbooks.Open(WScript.Arguments(0))

Dim sheet
Set sheet = book.WorkSheets.Item(WScript.Arguments(1))

For Each row In sheet.Cells.CurrentRegion.Rows
    Dim rec: rec = Array()
    Dim idx: idx = 0

    For Each col In row.Columns
        ReDim Preserve rec(idx)
        rec(idx) = col.Value
        idx = idx + 1
    Next

    WScript.Echo Join(rec, ",")
Next

objExcel.Quit 
Set objExcel = Nothing

S:\>cscript //nologo s:\lesson004.vbs s:\Book1.xls Sheet1
A1,B1,C1,D1,E1,F1,G1,H1
A2,B2,C2,D2,E2,F2,G2,H2
A3,B3,C3,D3,E3,F3,G3,H3
A4,B4,C4,D4,E4,F4,G4,H4
A5,B5,C5,D5,E5,F5,G5,H5
■ ADOを使用した場合
更新日 : 2008.05.23
Dim cn' as ADODB.Connection
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & WScript.Arguments(0) & ";" & "Extended Properties=Excel 8.0;"
    .CursorLocation = 3' adUseClient
    .Open
End With

Dim rs' As ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
With rs
    .Source           = "SELECT * FROM [" + WScript.Arguments(1) + "$]"
    .Activeconnection =  cn
    .CursorType       =  0' adOpenForwardOnly
    .LockType         =  1' adLockReadOnly
    .Open
End With

Do Until rs.EOF
    Dim s: s = ""
    For i = 0 To rs.Fields.Count - 1
        s = s & rs.Fields(i).Value & ","
    Next
    WScript.Echo Left(s, Len(s) - 1)
    rs.MoveNext
Loop

rs.Close
cn.Close

S:\>cscript //nologo s:\lesson008.vbs s:\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
TOP

JScript

更新日 : 2008.05.23
var objExcel = WScript.CreateObject("Excel.Application");
objExcel.Visible       = true;
objExcel.DisplayAlerts = false; //警告メッセージをOFF

//ブックを読み取り専用で開く
var book  = objExcel.Workbooks.Open(WScript.Arguments(0));
var sheet = book.WorkSheets.Item(WScript.Arguments(1));

for (var iRow = 1; iRow <= sheet.Cells.CurrentRegion.Rows.Count; iRow++)
{
    rec = new Array();
    for (var iCol = 1; iCol <= sheet.Cells.CurrentRegion.Columns.Count; iCol++)
    {
        rec.push(sheet.Cells(iRow, iCol).Value);
    }
    WScript.Echo(rec.join(","));
}

objExcel.Quit(); 
objExcel = null;

S:\>cscript //nologo s:\lesson004.js s:\Book1.xls Sheet1
A1,B1,C1,D1,E1,F1,G1,H1
A2,B2,C2,D2,E2,F2,G2,H2
A3,B3,C3,D3,E3,F3,G3,H3
A4,B4,C4,D4,E4,F4,G4,H4
A5,B5,C5,D5,E5,F5,G5,H5
■ ADOを使用した場合
更新日 : 2008.05.23
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 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;

S:\>cscript //nologo s:\lesson008.js s:\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
TOP

Perl

更新日 : 2008.05.23
use Win32::OLE;

Win32::OLE::CreateObject("Excel.Application", $objExcel) || die "fail! : $!";
$objExcel->{Visible}       = 1;
$objExcel->{DisplayAlerts} = 0; #警告メッセージをOFF

$objBook  = $objExcel->WorkBooks->Open($ARGV[0]);
$objSheet = $objBook->WorkSheets->Item($ARGV[1]);

foreach $iRow (1..$objSheet->Cells->CurrentRegion->Rows->Count)
{
    @row = ();
    foreach $iCol (1..$objSheet->Cells->CurrentRegion->Columns->Count)
    {
        push @row, $objSheet->Cells($iRow, $iCol)->Value;
    }
    print join(",", @row), "\n";
}

$objBook->Close();
$objExcel->Quit();

S:\>perl s:\lesson004.pl s:\Book1.xls Sheet1
A1,B1,C1,D1,E1,F1,G1,H1
A2,B2,C2,D2,E2,F2,G2,H2
A3,B3,C3,D3,E3,F3,G3,H3
A4,B4,C4,D4,E4,F4,G4,H4
A5,B5,C5,D5,E5,F5,G5,H5
■ ADOを使用した場合
更新日 : 2008.05.23
use Win32::OLE;

Win32::OLE::CreateObject("ADODB.Connection", $cn);
$cn->{Provider}         = "Microsoft.Jet.OLEDB.4.0";
$cn->{ConnectionString} = "Data Source=".$ARGV[0].";Extended Properties=Excel 8.0;";
$cn->{CursorLocation}   = 3; # adUseClient
$cn->Open;

Win32::OLE::CreateObject("ADODB.Recordset", $rs);
$rs->{Source}           = "SELECT * FROM [".$ARGV[1].'$]';
$rs->{ActiveConnection} = $cn;
$rs->{CursorType}       = 0; # adOpenForwardOnly
$rs->{LockType}         = 1; # adLockReadOnly
$rs->Open;

while (!$rs->EOF)
{
    @rec = ();
    foreach $col ($rs->Fields)
    {
        push @rec, $col->Value;
    }
    print join(",", @rec), "\n";

    $rs->MoveNext();
}

$rs->Close;
$cn->Close;

S:\>perl s:\lesson008.pl s:\Book1.xls Sheet1

エラーには、ならないが、何も出力されないので、ちょいと変更

use Win32::OLE;

Win32::OLE::CreateObject("ADODB.Connection", $cn);
$cn->{Provider}         = "Microsoft.Jet.OLEDB.4.0";
$cn->{ConnectionString} = "Data Source=".$ARGV[0].";Extended Properties=Excel 8.0;";
$cn->{CursorLocation}   = 3; # adUseClient
$cn->Open;

Win32::OLE::CreateObject("ADODB.Recordset", $rs);
$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;

S:\>perl s:\lesson008.pl s:\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
TOP

PHP

更新日 : 2008.05.23
<?php
// EXCELのインスタンス作成
$excel = new COM("Excel.Application") or die;
$excel->Visible       = 1;
$excel->DisplayAlerts = 0;

// ファイルオープン & 書込み
$wkb   = $excel->Workbooks->Open($argv[1]);
$sheet = $wkb->WorkSheets->Item($argv[2]);

for ($iRow = 1; $iRow <= $sheet->Cells->CurrentRegion->Rows->Count; $iRow++)
{
    $row = array();
    for ($iCol = 1; $iCol <= $sheet->Cells->CurrentRegion->Columns->Count; $iCol++)
    {
        array_push($row, $sheet->Cells($iRow, $iCol)->Value);
    }
    echo join(",", $row), "\n";
}

$wkb->Close();
$excel->Quit();
unset($excel);
?>

S:\>php s:\lesson004.php s:\Book1.xls Sheet1
A1,B1,C1,D1,E1,F1,G1,H1
A2,B2,C2,D2,E2,F2,G2,H2
A3,B3,C3,D3,E3,F3,G3,H3
A4,B4,C4,D4,E4,F4,G4,H4
A5,B5,C5,D5,E5,F5,G5,H5
■ ADOを使用した場合
更新日 : 2008.05.23
<?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;
?>

S:\>php s:\lesson008.php s:\Book1.xls Sheet1

Fatal error: Uncaught exception 'com_exception' with message 'Source: Provider
Description: 種類が一致しません。' in S:\lesson008.php:9
Stack trace:
#0 S:\lesson008.php(9): unknown()
#1 {main}
  thrown in S:\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;
?>

S:\>php s:\lesson008.php s:\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
TOP

Python

TOP

Ruby

更新日 : 2008.05.23
require 'win32ole'

objExcel = WIN32OLE.new('Excel.Application')
objExcel.Visible =       1
objExcel.DisplayAlerts = 0 #警告メッセージをOFF

objBook = objExcel.WorkBooks.Open(ARGV[0])
begin 
    objSheet = objBook.WorkSheets.Item(ARGV[1])

    objSheet.Cells.CurrentRegion.Rows.each do |row|
        rec = []
        row.Columns.each do |col|
            rec.push(col.Value)
        end
        puts rec.join(",")
    end
ensure
    objBook.Close
    objExcel.Quit
end

S:\>ruby s:\lesson004.rb s:\Book1.xls Sheet1
A1,B1,C1,D1,E1,F1,G1,H1
A2,B2,C2,D2,E2,F2,G2,H2
A3,B3,C3,D3,E3,F3,G3,H3
A4,B4,C4,D4,E4,F4,G4,H4
A5,B5,C5,D5,E5,F5,G5,H5
■ ADOを使用した場合
更新日 : 2008.05.23
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

rs = WIN32OLE.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 = []
    rs.Fields.each do |col|
        rec.push(col.Value)
    end
    puts rec.join(",")

    rs.MoveNext
end

rs.Close
cn.Close

S:\>ruby s:\lesson008.rb s:\Book1.xls Sheet1
s:/lesson008.rb:10:in `method_missing':  (WIN32OLERuntimeError)
    OLE error code:80020005 in Provider
      種類が一致しません。
    HRESULT error code:0x80020009
      例外が発生しました。      from s:/lesson008.rb:10

何故か、エラーになるので、ちょいと変更

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

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

S:\lesson008.rb s:\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
TOP

PowerShell

TOP

Scala

TOP

F#

TOP

C

TOP

C++

TOP

C++Builder

TOP

VC++

TOP

C#

TOP

Java

TOP

Objective-C

TOP

D

TOP

VB

TOP

VB.NET

TOP

Delphi

TOP

Ada

TOP

PL/SQL

TOP

T-SQL

TOP

スクリプト系

関数型

C, Java 系

Pascal, Basic 系

inserted by FC2 system