Send-SQLDataToExcel.ps1

Function Send-SQLDataToExcel {
        <#
      .SYNOPSIS
        Inserts a DataTable - returned by a SQL query - into an ExcelSheet
      .DESCRIPTION
        This command takes a SQL statement and run it against a database connection; for the connection it accepts either
        * an object representing a session with a SQL server or ODBC database, or
        * a connection string to make a session (if -MSSQLServer is specified it uses the SQL Native client,
          and -Connection can be a server name instead of a detailed connection string. Without this switch it uses ODBC)
        The command takes all the parameters of Export-Excel, except for -InputObject (alias TargetData); after
        fetching the data it calls Export-Excel with the data as the value of InputParameter and whichever of
        Export-Excel's parameters it was passed; for details of these parameters see the help for Export-Excel.
      .PARAMETER Session
        An active ODBC Connection or SQL connection object representing a session with a database which will be queried to get the data .
      .PARAMETER Connection
        A database connection string to be used to create a database session; either
        * A Data source name written in the form DSN=ODBC_Data_Source_Name, or
        * A full ODBC or SQL Native Client Connection string, or
        * The name of a SQL server.
      .PARAMETER MSSQLServer
        Specifies the connection string is for SQL server, not ODBC.
      .PARAMETER SQL
        The SQL query to run against the session which was passed in -Session or set up from -Connection.
      .PARAMETER Database
        Switches to a specific database on a SQL server.
      .PARAMETER QueryTimeout
        Override the default query time of 30 seconds.
      .PARAMETER DataTable
        A System.Data.DataTable object containing the data to be inserted into the spreadsheet without running a query.
        This remains supported to avoid breaking older scripts, but if you have a DataTable object you can pass the it
        into Export-Excel using -InputObject.
      .PARAMETER Force
        If specified Export-Excel will be called with parameters specified, even if there is no data to send
      .EXAMPLE
        C:\> Send-SQLDataToExcel -MsSQLserver -Connection localhost -SQL  "select name,type,type_desc from [master].[sys].[all_objects]" -Path .\temp.xlsx -WorkSheetname master -AutoSize -FreezeTopRow -AutoFilter -BoldTopRow

        Connects to the local SQL server and selects 3 columns from [Sys].[all_objects] and exports then to a sheet named master with some basic header management
      .EXAMPLE
        C:\> $dbPath = 'C:\Users\James\Documents\Database1.accdb'
        C:\> $Connection = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=$dbPath;"
        C:\> $SQL="SELECT top 25 Name,Length  From TestData ORDER BY Length DESC"

        C:\> Send-SQLDataToExcel -Connection  $connection -SQL $sql -path .\demo1.xlsx -WorkSheetname "Sizes" -AutoSize

        This creates an  ODBC connection string to read from an Access file and a SQL Statement to extracts data from it,
        and sends the resulting data to a new worksheet

      .EXAMPLE
        C:\> $dbPath = 'C:\users\James\Documents\f1Results.xlsx'
        C:\> $Connection = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=$dbPath;"
        C:\> $SQL="SELECT top 25 DriverName, Count(RaceDate) as Races, Count(Win) as Wins, Count(Pole) as Poles, Count(FastestLap) as Fastlaps " +
                   " FROM Results  GROUP BY DriverName ORDER BY (count(win)) DESC"

        C:\> Send-SQLDataToExcel -Connection  $connection -SQL $sql -path .\demo2.xlsx -WorkSheetname "Winners" -AutoSize -AutoNameRange -ConditionalFormat @{DataBarColor="Blue"; Range="Wins"}

        Similar to the previous example this creates a connection string, this time for an Excel file, and runs
        a SQL statement to get a list of motor-racing results, outputting the resulting data to a new spreadsheet.
        The spreadsheet is formatted and a data bar added to show make the drivers' wins clearer.
        (the F1 results database is available from https://1drv.ms/x/s!AhfYu7-CJv4ehNdZWxJE9LMAX_N5sg )
      .EXAMPLE
        C:\> $dbPath = 'C:\users\James\Documents\f1Results.xlsx'
        C:\> $SQL = "SELECT top 25 DriverName, Count(RaceDate) as Races, Count(Win) as Wins, Count(Pole) as Poles, Count(FastestLap) as Fastlaps " +
                     " FROM Results GROUP BY DriverName ORDER BY (count(win)) DESC"
        C:\> $null = Get-SQL -Session F1 -excel -Connection $dbPath -sql $sql -OutputVariable Table

        C:\> Send-SQLDataToExcel -DataTable $Table -Path ".\demo3.xlsx" -WorkSheetname Gpwinners -autosize  -TableName winners -TableStyle Light6 -show

        This uses Get-SQL (at least V1.1 - download from the PowerShell gallery with Install-Module -Name GetSQL -
        note the function is Get-SQL the module is GetSQL without the "-" )
        Get-SQL simplify making database connections and building /submitting SQL statements.
        Here Get-SQL uses the same SQL statement as before; -OutputVariable leaves a System.Data.DataTable object in $table
        and Send-SQLDataToExcel puts $table into the worksheet and sets it as an Excel table.
        The command is equivalent to running
        C:\>  Export-Excel -inputObject $Table -Path ".\demo3.xlsx" -WorkSheetname Gpwinners -autosize  -TableName winners -TableStyle Light6 -show
        This is quicker than using
        C:\> Get-SQL <parameters> | export-excel -ExcludeProperty rowerror,rowstate,table,itemarray,haserrors <parameters>
        (the F1 results database is available from https://1drv.ms/x/s!AhfYu7-CJv4ehNdZWxJE9LMAX_N5sg )
      .EXAMPLE
        C:\> $SQL = "SELECT top 25 DriverName,  Count(Win) as Wins FROM Results GROUP BY DriverName ORDER BY (count(win)) DESC"
        C:\> Send-SQLDataToExcel -Session $DbSessions["f1"] -SQL $sql -Path  ".\demo3.xlsx" -WorkSheetname Gpwinners  -ClearSheet -autosize -ColumnChart

        Like the previous example, this uses Get-SQL (download from the gallery with Install-Module -Name GetSQL).
        It uses the database session which Get-SQL created, rather than an ODBC connection string.
        The Session parameter can either be a object (as shown here), or the name used by Get-SQL ("F1" in this case).
        Here the data is presented as a quick chart.
      .EXAMPLE
        C:\>  Send-SQLDataToExcel -path .\demo4.xlsx -WorkSheetname "LR" -Connection "DSN=LR" -sql "SELECT name AS CollectionName FROM AgLibraryCollection Collection ORDER BY CollectionName"

        This example uses an Existing ODBC datasource name "LR" which maps to an adobe lightroom database and gets a list of collection names into a worksheet

    .Link
        Export-Excel
    #>
    [CmdletBinding(DefaultParameterSetName="none")]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidGlobalVars', '', Justification="Allowed to use DBSessions Global variable from GETSQL Module")]

    param (
        [Parameter(ParameterSetName="SQLConnection",   Mandatory=$true)]
        [Parameter(ParameterSetName="ODBCConnection",  Mandatory=$true)]
        $Connection,
        [Parameter(ParameterSetName="ExistingSession", Mandatory=$true)]
        $Session,
        [Parameter(ParameterSetName="SQLConnection",   Mandatory=$true)]
        [switch]$MsSQLserver,
        [Parameter(ParameterSetName="SQLConnection")]
        [String]$DataBase,
        [Parameter(ParameterSetName="SQLConnection",   Mandatory=$true)]
        [Parameter(ParameterSetName="ODBCConnection",  Mandatory=$true)]
        [Parameter(ParameterSetName="ExistingSession", Mandatory=$true)]
        [string]$SQL,
        [int]$QueryTimeout,
        [Parameter(ParameterSetName="Pre-FetchedData", Mandatory=$true)]
        [System.Data.DataTable]$DataTable,
        [switch]$Force
    )
#Import the parameters from Export-Excel, we will pass InputObject, and we have the common parameters so exclude those,
#and re-write the [Parmameter] attribute on each one to avoid parameterSetName here competing with the settings in Export excel.
#The down side of this that impossible parameter combinations won't be filtered out and need to be caught later.
    DynamicParam {
        $ParameterAttribute  =                        "System.Management.Automation.ParameterAttribute"
        $RuntimeDefinedParam =                        "System.Management.Automation.RuntimeDefinedParameter"
        $paramDictionary     =    New-Object -TypeName System.Management.Automation.RuntimeDefinedParameterDictionary
        $attributeCollection =    New-Object -TypeName System.Collections.ObjectModel.Collection[System.Attribute]
        $attributeCollection.Add((New-Object -TypeName $ParameterAttribute -Property @{ ParameterSetName = "__AllParameterSets" ;Mandatory = $false}))
        foreach ($P in (Get-Command -Name Export-Excel).Parameters.values.where({$_.name -notmatch 'Verbose|Debug|Action$|Variable$|Buffer$|TargetData$|InputObject$'}))  {
            $paramDictionary.Add($p.Name, (New-Object -TypeName $RuntimeDefinedParam -ArgumentList $p.name, $p.ParameterType, $attributeCollection ) )
        }
        return $paramDictionary
    }
    process {
      #region Dynamic params mean we can get passed parameter combination Export-Excel will reject, so throw here, rather than get data and then have Export-Excel error.
        if ($PSBoundParameters.Path -and $PSBoundParameters.ExcelPackage) {
            throw 'Parameter error: you cannot specify both a path and an Excel Package.'
            return
        }
        if ($PSBoundParameters.AutoFilter -and ($PSBoundParameters.TableName -or $PSBoundParameters.TableStyle)) {
            Write-Warning "Tables are automatically auto-filtered, -AutoFilter will be ignored"
            $null = $PSBoundParameters.Remove('AutoFilter')
        }
      #endregion
      #region if we were either given a session object or a connection string (& optionally -MSSQLServer) make sure we can connect
        try {
            #If we got -MSSQLServer, create a SQL connection, if we didn't but we got -Connection create an ODBC connection
            if     ($MsSQLserver -and $Connection) {
                if ($Connection -notmatch '=') {$Connection = "server=$Connection;trusted_connection=true;timeout=60"}
                $Session     = New-Object -TypeName System.Data.SqlClient.SqlConnection  -ArgumentList $Connection
                if ($Session.State -ne 'Open') {$Session.Open()}
                if ($DataBase) {$Session.ChangeDatabase($DataBase) }
            }
            elseif ($Connection)  {
                $Session     = New-Object -TypeName System.Data.Odbc.OdbcConnection      -ArgumentList $Connection ; $Session.ConnectionTimeout = 30
            }
        }
        catch {
            Write-Warning "An Error occured trying to connect to $Connection, the error was $([Environment]::NewLine + $_.Exception.InnerException))"
        }
        if ($Session -is [String] -and $Global:DbSessions[$Session]) {$Session = $Global:DbSessions[$Session]}
      #endregion
      #region we may have been given a table, but if there is a db session to connect to, send the query
        if     ($Session) {
            try {
                #If the session a SQL one make a SQL DataAdapter, otherwise make an ODBC one
                if ($Session.GetType().name -match "SqlConnection") {
                    $dataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter -ArgumentList (
                                New-Object -TypeName System.Data.SqlClient.SqlCommand     -ArgumentList $SQL, $Session)
                }
                else {
                    $dataAdapter = New-Object -TypeName System.Data.Odbc.OdbcDataAdapter     -ArgumentList (
                                New-Object -TypeName System.Data.Odbc.OdbcCommand         -ArgumentList $SQL, $Session )
                }
                if ($QueryTimeout) {$dataAdapter.SelectCommand.CommandTimeout = $QueryTimeout}

                #Both adapter types output the same kind of table, create one and fill it from the adapter
                $DataTable       = New-Object -TypeName System.Data.DataTable
                $rowCount        = $dataAdapter.fill($dataTable)
                Write-Verbose -Message "Query returned $rowCount row(s)"
            }
            catch {
                Write-Warning "An Error occured trying to run the query, the error was $([Environment]::NewLine + $_.Exception.InnerException))"
            }
        }
      #endregion
      #region send the table to Excel
        #remove parameters which relate to querying SQL, leaving the ones used by Export-Excel
        'Connection' , 'Database'  , 'Session' , 'MsSQLserver' , 'SQL'  , 'DataTable'  , 'QueryTimeout' , 'Force' |
                ForEach-Object {$null = $PSBoundParameters.Remove($_) }
        #if force was specified export even if there are no rows. If there are no columns, the query failed and export "null" if forced
        if     ($DataTable.Rows.Count) {
             Export-Excel  @PSBoundParameters -InputObject $DataTable
        }
        elseif ($Force -and $DataTable.Columns.Count) {
            Write-Warning -Message "Zero rows returned, and -Force was specified, sending empty table to Excel."
            Export-Excel  @PSBoundParameters -InputObject $DataTable
        }
        elseif ($Force) {
            Write-Warning -Message "-Force was specified but there is no data to send."
            Export-Excel  @PSBoundParameters -InputObject $null
        }
        else   {Write-Warning -Message 'There is no Data to insert, and -Force was not specified.' }
      #endregion
      #If we were passed a connection and opened a session,  close that session.
        if     ($Connection)  {$Session.close() }
    }
}