PowerShell SQL Select => Excel

This function runs an SQL query and displays results in a new Excel sheet:

function SQLtoXLS {
    param([String]$sql, [String]$server, [String]$db)

    $tbl = Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $sql

    $xl = New-Object -comobject Excel.Application
    $xl.Visible = $False
    $xl.DisplayAlerts = $False

    $wb = $xl.WorkBooks.Add()
    $ws = $wb.Worksheets.Item(1)
    $ws.Name = "SELECT results"

    $r = 0

    if($tbl.Count -gt 0)
    {
        $rowCount = $tbl.Count
        $colCount = $tbl[0].ItemArray.Count

        $grid = New-Object 'string[,]' ($rowCount + 1), $colCount

        $c = 0
        foreach($col in $tbl[0].Table.Columns)
        {
            $grid[$r, $c] = $col.ColumnName
            $c++
        }
        $r++

        foreach($row in $tbl)
        {
            $c = 0
            foreach($i in $row.ItemArray)
            {
                $grid[$r, $c] = [String]$i
                $c++
            }
            $r++
        }

        $ws.Range($ws.Cells.Item(1,1), $ws.Cells.Item($rowCount + 1, $colCount)).Value = $grid
        $ws.Range($ws.Cells.Item(1,1), $ws.Cells.Item(1, $colCount)).Font.FontStyle = "Bold"
    }
    else
    {
        $ws.Cells.Item(1, 1).Value2 = "No results"
    }

    $ws.Columns.AutoFit() | Out-Null
    $ws.Rows.AutoFit() | Out-Null
    $xl.Visible = $True
}

Usage example:

SQLtoXLS -sql "SELECT * FROM sql_table" -server "localhost" -db "my_db"

Parsing Excel document using PowerShell

$xlsFilePath = "C:\Excel_file.xlsx"

$xl = New-Object -comobject Excel.Application
$xl.Visible = $False
$xl.DisplayAlerts = $False
$wb = $xl.WorkBooks.Open($xlsFilePath)
$ws = $wb.Worksheets.Item(1)

$value = $ws.Cells.Item(1, "A").Value2
Write-Host $value
# ...

$wb.Close($false)
$xl.Quit()

[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null