ForNext
Only Do What Only You Can Do
Excel ファイルを 作成する
VBScript
更新日 : 2008.05.23
Dim objExcel Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False '警告メッセージをOFF 'ブックを読み取り専用で開く Dim bookIn Set bookIn = objExcel.Workbooks.Open(WScript.Arguments(0)) 'ブックを書き込み用で開く Dim bookOut Set bookOut = objExcel.Workbooks.Add 'いったん、シートを1枚だけにする Dim iSheet For iSheet = bookOut.WorkSheets.Count To 2 Step -1 bookOut.WorkSheets(iSheet).Delete Next For iSheet = 1 To bookIn.WorkSheets.Count '必要なら、シートを追加する If iSheet > bookOut.WorkSheets.Count Then bookOut.Sheets.Add , bookOut.WorkSheets(iSheet - 1) End If Dim sheetIn: Set sheetIn = bookIn.WorkSheets(iSheet) Dim sheetOut: Set sheetOut = bookOut.WorkSheets(iSheet) 'シート名を変更する If sheetOut.Name <> sheetIn.Name Then sheetOut.Name = sheetIn.Name End If 'データをコピーする Dim iRow For iRow = 1 To sheetIn.Cells.CurrentRegion.Rows.Count Dim iCol For iCol = 1 To sheetIn.Cells.CurrentRegion.Columns.Count sheetOut.Cells(iRow, iCol).Value = sheetIn.Cells(iRow, iCol).Value Next Next Next 'ブックを保存する bookOut.SaveAs(WScript.Arguments(1)) objExcel.Quit Set objExcel = Nothing
■ 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 sql sql = "" sql = sql & "INSERT INTO " sql = sql & " [" + WScript.Arguments(1) + "$] " sql = sql & "( " sql = sql & " A1, B1, C1, D1, E1, F1, G1, H1 " sql = sql & ") " sql = sql & "VALUES " sql = sql & "( " sql = sql & " 'AX', 'BX', 'CX', 'DX', 'EX', 'FX', 'GX', 'HX' " sql = sql & ")" cn.Execute(sql) cn.Close
■ 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 sql sql = "" sql = sql & "UPDATE " sql = sql & " [" + WScript.Arguments(1) + "$] " sql = sql & "SET " sql = sql & " A1 = 'AZ'" sql = sql & ", B1 = 'BZ'" sql = sql & ", C1 = 'CZ'" sql = sql & ", D1 = 'DZ'" sql = sql & ", E1 = 'EZ'" sql = sql & ", F1 = 'FZ'" sql = sql & ", G1 = 'GZ'" sql = sql & ", H1 = 'HZ'" sql = sql & "WHERE " sql = sql & " A1 = 'A7'" cn.Execute(sql) cn.Close
■ 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 sql sql = "" sql = sql & "DELETE FROM " sql = sql & " [" + WScript.Arguments(1) + "$] " sql = sql & "WHERE " sql = sql & " A1 = 'A2'" cn.Execute(sql) cn.Close
S:\>cscript //nologo s:\lesson011.vbs s:\Book1.xls Sheet1 s:\lesson011.vbs(17, 1) Microsoft JET Database Engine: この ISAM では、リンク テーブル内のデータを削除することはできません。
JScript
更新日 : 2008.05.23
var objExcel = WScript.CreateObject("Excel.Application"); objExcel.Visible = true; objExcel.DisplayAlerts = false; //警告メッセージをOFF //ブックを読み取り専用で開く var bookIn = objExcel.Workbooks.Open(WScript.Arguments(0)); //ブックを書き込み用で開く var bookOut = objExcel.Workbooks.Add; //いったん、シートを1枚だけにする for (var iSheet = bookOut.WorkSheets.Count; iSheet > 1; iSheet--) { bookOut.WorkSheets(iSheet).Delete; } for (var iSheet = 1; iSheet <= bookIn.WorkSheets.Count; iSheet++) { //必要なら、シートを追加する if (iSheet > bookOut.WorkSheets.Count) bookOut.Sheets.Add(null, bookOut.WorkSheets(iSheet - 1)); var sheetIn = bookIn.WorkSheets(iSheet); var sheetOut = bookOut.WorkSheets(iSheet); //シート名を変更する if (sheetOut.Name != sheetIn.Name) sheetOut.Name = sheetIn.Name; //データをコピーする for (var iRow = 1; iRow <= sheetIn.Cells.CurrentRegion.Rows.Count; iRow++) { for (var iCol = 1; iCol <= sheetIn.Cells.CurrentRegion.Columns.Count; iCol++) { sheetOut.Cells(iRow, iCol).Value = sheetIn.Cells(iRow, iCol).Value; } } } //ブックを保存する bookOut.SaveAs(WScript.Arguments(1)); objExcel.Quit(); objExcel = null;
■ 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 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); cn.Close;
■ 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 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); cn.Close;
Perl
更新日 : 2008.05.23
use Win32::OLE; Win32::OLE::CreateObject("Excel.Application", $objExcel) || die "fail! : $!"; $objExcel->{Visible} = 1; $objExcel->{DisplayAlerts} = 0; #警告メッセージをOFF $bookIn = $objExcel->WorkBooks->Open($ARGV[0]); $bookOut = $objExcel->Workbooks->Add; #いったん、シートを1枚だけにする for ($iSheet = $bookOut->WorkSheets->Count; $iSheet > 1; $iSheet--) { $bookOut->WorkSheets($iSheet)->Delete; } for $iSheet (1..$bookIn->WorkSheets->Count) { #必要なら、シートを追加する $bookOut->Sheets->Add(undef, $bookOut->WorkSheets($iSheet - 1)) if ($iSheet > $bookOut->WorkSheets->Count); $sheetIn = $bookIn->WorkSheets($iSheet); $sheetOut = $bookOut->WorkSheets($iSheet); #シート名を変更する $sheetOut->{Name} = $sheetIn->Name if ($sheetOut->Name != $sheetIn->Name); #データをコピーする for $iRow (1..$sheetIn->Cells->CurrentRegion->Rows->Count) { for $iCol (1..$sheetIn->Cells->CurrentRegion->Columns->Count) { $sheetOut->Cells($iRow, $iCol)->{Value} = $sheetIn->Cells($iRow, $iCol)->Value; } } } #ブックを保存する $bookOut->SaveAs($argv[1]); $bookIn->Close(); $objExcel->Quit();
■ ADOを使用して データを追加する場合
更新日 : 2008.05.23
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); $cn->Close;
■ ADOを使用して データを更新する場合
更新日 : 2008.05.23
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); $cn->Close;
PHP
更新日 : 2008.05.23
<?php // EXCELのインスタンス作成 $excel = new COM("Excel.Application") or die; $excel->Visible = 1; $excel->DisplayAlerts = 0; $bookIn = $excel->Workbooks->Open($argv[1]); $bookOut = $excel->Workbooks->Add; #いったん、シートを1枚だけにする for ($iSheet = $bookOut->WorkSheets->Count; $iSheet > 1; $iSheet--) { $bookOut->WorkSheets($iSheet)->Delete; } for ($iSheet = 1; $iSheet <= $bookIn->WorkSheets->Count; $iSheet++) { #必要なら、シートを追加する if ($iSheet > $bookOut->WorkSheets->Count) $bookOut->Sheets->Add(NULL, $bookOut->WorkSheets($iSheet - 1)); $sheetIn = $bookIn->WorkSheets($iSheet); $sheetOut = $bookOut->WorkSheets($iSheet); #シート名を変更する if ($sheetOut->Name != $sheetIn->Name) $sheetOut->Name = $sheetIn->Name; #データをコピーする for ($iRow = 1; $iRow <= $sheetIn->Cells->CurrentRegion->Rows->Count; $iRow++) { for ($iCol = 1; $iCol <= $sheetIn->Cells->CurrentRegion->Columns->Count; $iCol++) { $sheetOut->Cells($iRow, $iCol)->Value = $sheetIn->Cells($iRow, $iCol)->Value; } } } #ブックを保存する $bookOut->SaveAs($argv[2]); $bookIn->Close(); $excel->Quit(); unset($excel); ?>
■ 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; # 追加 $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); $cn->Close; ?>
■ 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; # 変更 $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); $cn->Close; ?>
Python
Ruby
更新日 : 2008.05.23
require 'win32ole' objExcel = WIN32OLE.new('Excel.Application') objExcel.Visible = 1 objExcel.DisplayAlerts = 0 #警告メッセージをOFF begin #ブックを読み取り専用で開く bookIn = objExcel.WorkBooks.Open(ARGV[0]) begin #ブックを書き込み用で開く bookOut = objExcel.Workbooks.Add #いったん、シートを1枚だけにする iSheet = bookOut.WorkSheets.Count while iSheet > 1 bookOut.WorkSheets(iSheet).Delete iSheet -= 1 end for iSheet in 1..bookIn.WorkSheets.Count do #必要なら、シートを追加する bookOut.Sheets.Add(nil, bookOut.WorkSheets(iSheet - 1)) if (iSheet > bookOut.WorkSheets.Count) sheetIn = bookIn.WorkSheets(iSheet) sheetOut = bookOut.WorkSheets(iSheet) #シート名を変更する sheetOut.Name = sheetIn.Name if (sheetOut.Name != sheetIn.Name) #データをコピーする for iRow in 1..sheetIn.Cells.CurrentRegion.Rows.Count do for iCol in 1..sheetIn.Cells.CurrentRegion.Columns.Count do sheetOut.Cells(iRow, iCol).Value = sheetIn.Cells(iRow, iCol).Value end end end #ブックを保存する bookOut.SaveAs(ARGV[1]) ensure bookOut.Close end ensure bookIn.Close objExcel.Quit end
■ 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 # 追加 sql = "INSERT INTO " sql += " [" +ARGV[1] + "$] " sql += "( " sql += " A1, B1, C1, D1, E1, F1, G1, H1 " sql += ") " sql += "VALUES " sql += "( " sql += " 'AX', 'BX', 'CX', 'DX', 'EX', 'FX', 'GX', 'HX' " sql += ")" cn.Execute(sql) cn.Close
■ 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 # 変更 sql = "UPDATE " sql += " [" + ARGV[1] + "$] " sql += "SET " sql += " A1 = 'AZ'" sql += ", B1 = 'BZ'" sql += ", C1 = 'CZ'" sql += ", D1 = 'DZ'" sql += ", E1 = 'EZ'" sql += ", F1 = 'FZ'" sql += ", G1 = 'GZ'" sql += ", H1 = 'HZ'" sql += "WHERE " sql += " A1 = 'A7'" cn.Execute(sql) cn.Close