Powershell 2.0 code to execute SQL Server query and populate a new Excel xlsx document with the returned SQL dataset.

get-SQL.ps1

# Created by Fergus Stevens 
# V1 - 22/01/14 - Initial Version
# V2 - 04/02/14 - Much *MUCH* faster import of data to excel using multi dimensional array import technique. 
#                 http://powertoe.wordpress.com/2010/11/05/how-to-get-data-into-an-excel-spreadsheet-very-quickly-with-powershell-contd/
#                 Improved console output and use of stnadard Support functions library

[CmdletBinding()]
Param()

# Create stopwatch
$sw = New-Object System.Diagnostics.Stopwatch
$sw.Start()

# Load the support functions library
. ".\supportFunctions.ps1" 


#######################################
# Function Definitions

function Format-Elapsed
(
    $ts
)
{
    $elapsedTime = "";
    # if ( $ts.Minutes -gt 0 )
    # {
        $elapsedTime = [string]::Format( "{0:00}:{1:00}.{2:00}", $ts.Minutes, $ts.Seconds, $ts.Milliseconds / 10 );
    # }
    # else
    # {
    #     $elapsedTime = [string]::Format( "{0:00}.{1:00}", $ts.Seconds, $ts.Milliseconds / 10 );
    # }
    return $elapsedTime;

}

function Start-Elapsed
(
    $sw,
    $message
)
{
    $swLap = $sw.Elapsed;
    Write-Host -NoNewLine "$(Format-Elapsed $swLap) $message - ";
    Write-Host -NoNewLine "Working... " -ForegroundColor "Green";
    return $swLap;
}

function End-Elapsed
(
    $sw,
    $swLap
)
{
    Write-Host " Complete" -ForegroundColor "Green" -NoNewLine;
    Write-Host " ($(Format-Elapsed $($sw.Elapsed - $swLap)))";
}


# SQL Query Variables
$dataSource = "SQLSERVER.yourdomain.com"
$dataBase = "I3_IC"
$query = @"
    SELECT  [CallId], 
            [CallType],
            [CallDirection],
            [LocalUserId],
            [RemoteName],
            [InitiatedDate],
            [ConnectedDate],
            [TerminatedDate],
            [CallDurationSeconds],
            [HoldDurationSeconds],
            [LineDurationSeconds],
            [CallEventLog],
            [CallNote],
            [I3TimeStampGMT],
            [WrapUpCode]
    FROM [I3_IC].[dbo].[CallDetail]
    WHERE [CallNote] IS NOT NULL 
      AND [CallNote] != '' 
      AND YEAR([InitiatedDate]) = 2014 AND MONTH([InitiatedDate]) = 1
    ORDER BY [InitiatedDate] DESC
"@


$trustedConnection = $True
$userName = "NotRequired"
$PassWord = "PahNotTelling"

#Excel Variables
$version = "V2 - 04/02/14"
$bodyFont = "Verdana"
$headingFont = "Arial Rounded MT Bold"
$headingColour = Convert-RGBtoLong("0,160,175")

$excelVisible = $False
#$excelVisible = $True
$OutputFile = Generate-Filename ([Environment]::GetFolderPath("Desktop"))  "yyyyMM"  "xlsx" "SQL-Export"

#Output Introductory Text
Clear-Host;

Write-Host "SQL to Excel Export Script" -ForegroundColor "Green" -BackgroundColor "Black";
Write-Host "$version" -ForegroundColor "Green";
Write-Host "";
Write-Host "Developed by Fergus Stevens for Teachers Mutual Bank";
Write-Host "";
Write-Host -NoNewLine "Creating file ";
Write-Host "$OutputFile" -ForegroundColor "Green";

Query-Environment;

#create excel object 
Write-Verbose "Creating Excel Object"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $excelVisible
$excel.DisplayAlerts = $false
$ActiveWorkbook = $excel.Workbooks.add()
$ActiveWorkbook.Title = "TMB Contact Center Data Generated " + $timestamp
$ActiveWorkbook.Author = "Fergus Stevens"

#####################################################
# Record the Query Parameters
$ActiveWorksheet = $ActiveWorkbook.worksheets.Item(2)
$ActiveWorksheet.Name = "SQL_Query"
$ActiveWorksheet.cells.item(1, 1) = "Server"
$ActiveWorksheet.cells.item(1, 2) = "DB"
$ActiveWorksheet.cells.item(1, 3) = "Query"
$wholeRange = $ActiveWorksheet.UsedRange
$wholeRange.Interior.Color = $headingColour
$wholeRange.Font.Name = $headingFont
$ActiveWorksheet.cells.item(2, 1) = $dataSource
$ActiveWorksheet.cells.item(2, 2) = $dataBase
$ActiveWorksheet.cells.item(2, 3) = $query
$wholeRange = $ActiveWorksheet.UsedRange
$wholeRange.EntireColumn.AutoFit() | Out-Null

#####################################################
# Gather the Data
$ActiveWorksheet = $ActiveWorkbook.worksheets.Item(1)
$ActiveWorksheet.Name = "SQL_Data"
# Open the Connection to the SQL Server
Write-Host "$(Format-Elapsed $sw.Elapsed) Opening Connection to the SQL Server " -NoNewLine;
Write-Host "$dataSource" -ForegroundColor "Green";
$connection = New-SqlConnection -Server $dataSource -Database $dataBase

#Execute the SQL commands and place the results in dataset
if ($connection.State -eq 'Open')
{   
    $swLap = Start-Elapsed $sw "Executing SQL Query"
    Write-Verbose "$query";
    $dataSet = Invoke-SQLQuery -connection $connection -query $query
    $connection.Close();
    End-Elapsed $sw $swLap    
} ELSE {
    Write-Error "$($(Format-Elapsed $swLap)) SQL Connection Not Open - Exiting...";
    exit;
}

$dataTable = new-object "System.Data.DataTable" "Results"
$dataTable = $dataSet.Tables[0]

$rowDT = $dataTable.Rows.Count;
$colDT = $dataTable.Columns.Count;
Write-Host -NoNewLine "$(Format-Elapsed $sw.Elapsed) Rows: ";
Write-Host -NoNewLine "$($rowDT+1)" -ForegroundColor "Green";
Write-Host -NoNewLine " Columns: "
Write-Host -NoNewLine "$($colDT+1)" -ForegroundColor "Green";
Write-Host -NoNewLine " Cells: "
Write-Host "$( ($colDT+1)*($rowDT+1) )" -ForegroundColor "Green";


#Create a 2D Array of the DataTable
# http://stackoverflow.com/questions/13184191/fastest-way-to-drop-a-dataset-into-a-worksheet
$tableArray = New-Object 'object[,]' $rowDT, $colDT;
$swLap = Start-Elapsed $sw "DataTable transformation"
# i = row and j = column
for ($i=0;$i -lt $rowDT; $i++) 
{
    #Write-Progress -Activity "Transforming DataTable" -status "Row $i" -percentComplete ($i / $rowDT*100)
    for ($j=0;$j -lt $colDT; $j++) 
    {
        $tableArray[$i,$j] = $dataTable.Rows[$i].Item($j).ToString();
    }    
}
End-Elapsed $sw $swLap 

$rowOffset = 1; $colOffset = 1;# 1,1 = "A1"

# Write out the header column names
for ($j=0;$j -lt $colDT; $j++) 
{
    $ActiveWorksheet.cells.item($rowOffset, $j+1) = $dataTable.Columns[$j].ColumnName;
}
$headerRange = $ActiveWorksheet.Range($ActiveWorksheet.cells.item($rowOffset, $colOffset), $ActiveWorksheet.cells.item($rowOffset, $colDT+$colOffset-1)); 
$headerRange.Font.Bold = $false
$headerRange.Interior.Color = $headingColour
$headerRange.Font.Name = $headingFont
$rowOffset++;

# Extract the data to Excel
$tableRange = $ActiveWorksheet.Range($ActiveWorksheet.cells.item($rowOffset, $colOffset), $ActiveWorksheet.cells.item($rowDT+$rowOffset-1, $colDT+$colOffset-1));
$tableRange.Cells.Value2 = $tableArray;

# Resize the columns in Excel
$swLap = Start-Elapsed $sw "Resize Excel Worksheet"
$wholeRange = $ActiveWorksheet.UsedRange
$wholeRange.EntireColumn.AutoFit() | Out-Null
$wholeRange.RowHeight = 50
End-Elapsed $sw $swLap 

# Save Excel workbook
$ActiveWorkbook.SaveAs("$OutputFile ") 
$ActiveWorkbook.Close() 

#Clean Up
Remove-COMObject $excel
Remove-Variable excel

$sw.Stop()
Write-Host "";
Write-Host "$(Format-Elapsed $($sw.Elapsed))" -NoNewLine;
Write-Host " Finished" -ForegroundColor "Green";

supportFunctions.ps1

#Support Functions for Powershell Custom Reporting

function Remove-COMObject
    (
        $COM
    )
{
    # Get rid of the COM object
    # http://technet.microsoft.com/en-us/library/ff730962.aspx
    $COM.quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($COM) | Out-Null
    
}

function Invoke-SQLQuery
(
    [System.Data.SqlClient.SqlConnection] $Connection,
    [string] $Query, 
    [int] $ExecutionTimeout = 300
)
{
    Write-Verbose "Executing SQL Query: $query";
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand($query, $Connection);
    Write-Verbose "SQL Query Timeout: $ExecutionTimeout seconds";
    $SqlCmd.CommandTimeout = $ExecutionTimeout  # 300 = 5 minutes, (max) 65535 ~ 18 hours

    $dataSet = new-object System.Data.DataSet
    $dataAdapter=New-Object system.Data.SqlClient.SqlDataAdapter($SqlCmd)

    Write-Verbose "Filling Dataset"
    try {
         $dataAdapter.Fill($dataSet) | Out-Null 
     } catch [Exception] {
         Write-Error $_.Exception.ToString();
         Write-Host  "Filling the Dataset has failed. Please Retry." -ForegroundColor "Black" -BackgroundColor "Red";
         Remove-COMObject $excel;
         exit;
     }

    return $dataSet;
}



# Environment Query
function Query-Environment ()
{
    if ($PSVersionTable) {
        $psversion = $PSVersionTable.PSVersion.ToString()
    } else {
        $psversion = 1.0
    }
    

    Write-Verbose "Powershell Version: $psversion"
    Write-Verbose "Culture: $psculture"
    Write-Verbose "Current User: $(whoami)"
    Write-Verbose "Machine Name: $([Environment]::MachineName)"
}




function New-SqlConnection(
    [string] $Server = ".", 
    [string] $Database = "master", 
    [System.Management.Automation.PSCredential] $Credential,
    [int] $ConnectionTimeout = 30
)
{
    $ci = "Data Source=$Server;Initial Catalog=$Database;Connect Timeout=$ConnectionTimeout;"

    if ($Credential -eq $null)    
    {
        $ci = $ci + "Integrated Security=true;";
    } else {
        $ci = $ci + "Integrated Security=false;User ID="+$Credential.UserName+";Password="+$Credential.Password.ToString()+";"
    }
        
    Write-Verbose "Connection string: $ci"    

    $cnn = new-object System.Data.SqlClient.SqlConnection;
    $cnn.ConnectionString = $ci;
    $cnn.Open();
    $cnn;

<#
.DESCRIPTION
    Instantiates and Opens SqlConnection object

.PARAMETER Server
    Server name

.PARAMETER Database
    Database name (optional. default value �master�)

.PARAMETER Credential
    Credentials for Sql Authentication. If not specified, Windows Authentication will be used

.PARAMETER ConnectionTimeout
    Connection timeout

.EXAMPLE    
    $connection =  New-SqlConnection -Server "."

.EXAMPLE    
    $connection =  New-SqlConnection -Server "." -Database "MyDatabase"

.OUTPUTS
    Connected SqlConnection ovject

.LINK
    http://powershell4sql.codeplex.com
#>    

}



Function Convert-RGBtoLong 
(
    [string] $RGBString = "0,160,175" #Teal
)
{

    $RGBLong = ([int]$RGBString.split(",")[2] *65536) + ([int]$RGBString.split(",")[1]*256) + [int]$RGBString.split(",")[0]
    Write-Verbose "Convert-RGBtoLong: $RGBString = $RGBLong"
    $RGBLong;
    
<#
.DESCRIPTION
    Function Convert-RGBtoLong accepts a string of three comma seperated values and converts these to a Long value suitable for use in Excel.

.PARAMETER RGBString
    String of three comma seperated values

.EXAMPLE    
    $connection =  Convert-RGBtoLong ("198,217,45")

.OUTPUTS
    long colour value

.NOTES
    NAME: Convert-RGBtoLong
    AUTHOR: Fergus Stevens
    LASTEDIT: 30 Jan 2014
        
.LINK
    https://gist.github.com/fergus/8567876
    
#Requires -Version 2.0    
#>     
}

function Generate-Filename
(
    [string] $OutputDirectory = [Environment]::GetFolderPath("Desktop"),
    [string] $DateFormat = "yyyyMMdd", 
    [string] $FilePostfix = "xlsx",
    [string] $FilePrefix = "Generated"
)
{
    $timeStamp = get-date -f $DateFormat
    $fileName = "$FilePrefix$timeStamp.$FilePostfix";
    $filePath = join-path $OutputDirectory $fileName;

    $filePath;
<#
.DESCRIPTION
    Generate a File name to export data to

.PARAMETER OutputDirectory
    blah

.PARAMETER DateFormat
    blah

.PARAMETER FilePostfix
    blah

.PARAMETER FilePrefix
    blah

.EXAMPLE    
    $temp = Generate-Filename ([Environment]::GetFolderPath("Desktop"))  "yyyyMM"  "docx" "BoardReport"

.OUTPUTS
    string with the full file path and name.

.LINK
    N/A
#>    
}