Complete PowerShellScript for exporting Reports to Excel using ImportExcel

exportReport.ps1

#exportReport.ps1
<#
.SYNOPSIS
   Takes a query from a SQL file, adds parameters to it, executes it on a SQL Server and writes the result into an Excel file. Return value 0 indicates successful execution, Return value -1 indicates errors.
.DESCRIPTION
   This script enables exporting query results to excel from a machine, where Excel is not installed.
   This is done leveraging the module ExcelPSLib.

   System requirements:
   - Powershell version 3 or greater with modules dbatools and ImportExcel installed
   - SQL Server 2008 or greater
.PARAMETER databaseServer
    Name of the database server the query should be executed on
.PARAMETER databaseName
    Name of the database the query should be executed within
.PARAMETER sqlFile
    Path to a file with a SQL query. 
.PARAMETER parameters
    List of parameters with their assigned values in a DECLARE-statement.
    This have to be the same parameters like those contained in the query (if any).
    Typically queries running in a specified interval are parameterized regarding the time slice the information is needed for.
    Example: 
        DECLARE
	        @dateTimeFrom DATETIME = '20190701 00:00',
	        @dateTimeTo DATETIME = '20190801 00:00'
.PARAMETER fileName
    (Base) Name for the generated file (default Export)
.PARAMETER fileNameSupplement
    additional file name with separator (default timestamp): the defaults fileName Export and fileNameSupplement timestamp will resolve to something like "Export_20191126_144401.xlsx"
.PARAMETER worksheetName
    name of the excel worksheet with the exported data (default Export)
.PARAMETER mailRecipient
    Array of E-Mailadresses the report should be sent to. Example: 'test@info.com', 'mock@info.de'
.EXAMPLE
   & X:\Scripts\exportReport.ps1  -fileNameSupplement '_201910' -parameters 'DECLARE       @dateTimeFrom DATETIME = ''20191001'',       @dateTimeTo DATETIME = ''20191101''' -databaseServer 'mydbserver' -databaseName 'mydb' -sqlFile 'X:\scripts\myexport.sql' -fileName 'myexport' -mailRecipient 'mail@info.de', 'test@info.de'
.NOTES
#>
param( 
    [Parameter(Mandatory=$TRUE)]  [ValidateNotNullOrEmpty()] [string] $databaseServer,
    [Parameter(Mandatory=$TRUE)]  [ValidateNotNullOrEmpty()] [string] $databaseName,
    [Parameter(Mandatory=$TRUE)]  [ValidateNotNullOrEmpty()] [string] $sqlFile,
    [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $parameters = '',
    [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $fileName = 'Export',
    [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $worksheetName = 'Export',
    [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string[]] $mailRecipient = 'test@vcab.de',
    [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $fileNameSupplement = '_' + $(Get-Date -Format yyyyMMdd_HHmmss)
    )
# Function to print all parameter values 
# shamelessly copied from https://stackoverflow.com/questions/21559724/getting-all-named-parameters-from-powershell-including-empty-and-set-ones
# turns out that the most rated approach with the nicely layed out function does not work with invocation using &
# thus I used the solution by Jason Kreissler ....pardon me for copying that one-liner
function printParameters {
    (Get-Command -Name $PSCommandPath).Parameters | Format-Table -AutoSize @{ Label = "Key"; Expression={$_.Key}; }, @{ Label = "Value"; Expression={(Get-Variable -Name $_.Key -EA SilentlyContinue).Value}; }
}

Try
{
    # import necessary modules
    Import-Module dbatools;
    Import-Module ImportExcel;

    # set up variables
    $tempFolder = 'X:\Scripts\temp'; # temporary folder to store information needed only during script execution
    $logFolder = 'X:\Scripts\log'; # folder to store logging information from script exection
    $logFile = $logFolder + "\exportReport_$(Get-Date -Format yyyyMMdd_HHmmss).log"; # fileName of the log file
    $PSEmailServer = "myMailServer"; # address of the e-mail-server
           
    echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Starting script execution. Parameters are as following: " > $logFile;
    printParameters >> $logFile;
 
    # add supplement to filename
    # derive file extension: code assumes that there is just one dot in the fullName separating the fileName from the extension
    $originalFileName = $fileName;
   
    If ($filename.split('.').length -gt 2)
    {
        echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [WARNING] Filename "$fileName" contains multiple dots. Script expects at max one dot separating the extension from the name. The part after the first dot will not be processed into the final filename." >> $logFile;
    }


    $fileExtension = 
        If ($filename.split('.')[1] -ne $null)
            { 
                $filename.split('.')[1]
            } 
        Else
            {
                'xlsx'
            };

    $fileName = $filename.split('.')[0] + $fileNameSupplement + '.' + $fileExtension;

    echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Supplement ""$fileNameSupplement"" has been added to provided fileName. Complete FileName is now ""$fileName""." >> $logFile;

    # read SQL query into variable $sql and add parameter declaration to sqlFile
    $sql = Get-Content $sqlFile;
    $sql =  $parameters +  "
     " +  $(Get-Content $sqlFile);

    # echo for debugging
    #echo $sql;

    echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Parameters added to SQL. Executing query now." >> $logFile;

    # execute query on database server
    $result= Invoke-DbaQuery -SQLInstance $databaseServer -Database $databaseName -Query $sql ; # -SqlCredential

    echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Query execution finished. Writing results to Excel now." >> $logFile;
     
    # write results to excel
    $outputpath = $tempFolder + '\' + $fileName;
    #echo $outputpath;
    #remove internal columns... thanks @Shane O'Neill (https://nocolumnname.blog/2017/04/27/using-excludeproperty-in-select-object/) 
    # export
    $result | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Excel $outputpath -AutoFilter -AutoSize -BoldTopRow -FreezeTopRowFirstColumn -WorksheetName $worksheetName

    echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Export to Excel finished. Sending e-mail to $mailrecipient." >> $logFile;

    # prepare and send e-mail
    $encoding = New-Object System.Text.utf8encoding;
    $mailsubject = "Export $originalFileName";
    $mailbody = "...";
    Send-MailMessage -To $mailrecipient -From "powershell@abc.com" -Subject $mailsubject -Body $mailbody -Encoding $encoding -Attachments $outputpath;

    echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Mail sent." >> $logFile;

    echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Remove temporary export file after successful execution." >> $logFile;
    Remove-Item $outputpath;

    $returnValue = 0;
}
Catch
{  
    echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [ERROR] Powershell exception occurred. For details see next lines." >> $logFile;
    # get error
    $_  >> $logFile; 
    # Rename the log file to show erroneous executions easily
    Rename-Item $logFile "$logFile.err";
    $logFile = "$logFile.err";
    # set return value
    $returnValue = -1;
}
Finally
{
     Exit $returnValue;
}