PowerShell(Out-GridView)

# アセンブリの読み込み
Add-Type -Assembly System.Windows.Forms
# グリッドデータを保持するDataSetとDataTable
$dataSet1 = New-Object System.Data.DataSet
$dataTable1 = New-Object System.Data.DataTable

$dataRow = $dataTable1.NewRow()
$dataSet1.Tables.Add($dataTable1)

$wsobj = new-object -comobject wscript.shell
Function Get-ExcelBook($BookName)
{

    #=== 稼働中の Excel を捕まえる
    $ex = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")

    if ($ex -eq $null) { return $null }

    #=== Open中のWorkBookから目的のBookを見つける
    foreach ($bk in $ex.WorkBooks)
    {
        if ($bk.Name -eq $BookName)# 見つかった！
        {
            Write-Host $bk.name;
            return $bk
        }
    }
    Write-Host "ない";
    return $null
}
function Get-ClipBoard
{
    Add-Type -AssemblyName System.Windows.Forms
    $tb = New-Object System.Windows.Forms.TextBox
    $tb.Multiline = $true
    $tb.Paste()
    $tb.Text -replace "`r`n","`n" -replace "`r","`n"  -split "`n"
}

#稼働中のエクセルを捕まえる
$ex = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
#$bk = Get-ExcelBook("個人的アドインツール.xlam");
$sheet = $ex.Workbooks.Item("個人的アドインツール.xlam").Worksheets.Item("PowerShell")
$conStr = $sheet.Range("Connect").Text
$sqlStr = $sheet.Range("Sql").Text
$SelectDB = $sheet.Range("SelectDB").Text
#メッセージボックス
#$result = $wsobj.popup($conStr)
#$result = $wsobj.popup($sqlStr)
#$result = $wsobj.popup($SelectDB)
$sqlCountStr = "SELECT COUNT(*) AS MAXCNT FROM (" + $sqlStr + ") DATA"

switch($SelectDB)
 {
     #PowerShell + ADO.NET + Oracle
     "Oracle" {
         [void][Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
         #$conStr = "Data Source=ORCL;User ID=scott;Password=tiger"
         $con = New-Object System.Data.OracleClient.OracleConnection($conStr)
         $cmd = New-Object System.Data.OracleClient.OracleCommand($sqlStr, $con)
         $cmdCnt = New-Object System.Data.OracleClient.OracleCommand($sqlCountStr, $con);
     }
     #PowerShell + ADO.NET + SQL Server
     "SQLServer" {
         #$conStr = "SERVER=(local);DATABASE=master;UID=sa;PWD=P@ssW0rd"
         $con = New-Object System.Data.SqlClient.SqlConnection($conStr);
         $cmd = New-Object System.Data.SqlClient.SqlCommand($sqlStr, $con);
         $cmdCnt = New-Object System.Data.SqlClient.SqlCommand($sqlCountStr, $con);
     }
     #PowerShell + ADO.NET + OLE DB + Jet データベース
    "mdb" {
         #$conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hello.mdb;"
         $con = New-Object System.Data.OleDb.OleDbConnection($conStr)
         $cmd = New-Object System.Data.OleDb.OleDbCommand($sqlStr, $con)
         $cmdCnt = New-Object System.Data.OleDb.OleDbCommand($sqlCountStr, $con);

    }
     #PowerShell + ADO.NET + OLE DB + ACE データベース
    "accdb" {
         #$conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=hello.accdb;"
         $con = New-Object System.Data.OleDb.OleDbConnection($conStr)
         $cmd = New-Object System.Data.OleDb.OleDbCommand($sqlStr, $con)
         $cmdCnt = New-Object System.Data.OleDb.OleDbCommand($sqlCountStr, $con);
     }
     default {Write-Output ("登録されている以外の名前が入力されました。")}
 }

 $ex.StatusBar = "SQL実行中"
 $con.Open()
 $readerCnt = $cmdCnt.ExecuteReader()
 $readerCnt.read()
 $Count = $readerCnt[0]
 $result = $wsobj.popup($readerCnt.FieldCount)
 $readerCnt.Close()
 $reader = $cmd.ExecuteReader()
 #$result = $wsobj.popup($reader.FieldCount)
 $Head = ""
 $Data = ""
 $Flg = ''
 $Cnt = 0
 #フィールド名出力
 for ( $i = 0; $i -lt $reader.FieldCount; $i++ )
{
    $dataTable1.Columns.Add($reader.getName($i), [string])
}
$Cnt = 1
 #データ出力
 while ( $reader.read() )
 {
    $dataRow = $dataTable1.NewRow()
    for ( $i = 0; $i -lt $reader.FieldCount; $i++ )
    {
        $dataRow.Item($i) = $reader[$i].ToString()

    }
    $Cnt =  $Cnt + 1
    $ex.StatusBar = [string]($Cnt+1) +" / " + [string]$Count + "件処理中"
    $dataTable1.Rows.Add($dataRow)
 }

 $ex.StatusBar = "抽出完了"
 $con.Close()
 $dataView =  New-Object System.Data.DataView($dataTable1)
 $dataSet1.Tables | Select-Object -ExpandProperty Rows | Out-GridView