ExportToExcel.ps1

cls
Import-Module -Name SqlPs #or SqlServer
Import-Module -Name ImportExcel

# Database servername
$ServerName = "Server"
# Datbase name
$DatabaseName = "AdventureWorksDW2017"
# database select query
$Query = "SELECT TOP (5) [DatabaseLogID],[PostTime],[DatabaseUser],[Event],[Schema],[Object] FROM [AdventureWorksDW2017].[dbo].[DatabaseLog]"
# Temporary file name with path
$xlfile = "$env:TEMP\Db-Reports.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

# function which connects to datbase using windows autentication and creates a excel using the output generated
function QueryDatabase{
	[OutputType([bool])]
	param(
		[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
		[String]$ServerName,
		[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
		[String]$DatabaseName,	
		[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
		[String]$Query
	)
	try{
            Write-Output "Invoking database operations on $(Get-Date -format 'dd-MM-yyyy HH:mm')"
	    $AuditResult = Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Query | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors 
	    $excel = $AuditResult | Export-Excel $xlfile -AutoSize -StartRow 1 -TableName DatabaseResult -PassThru -WorksheetName "Database Results"

            # Create a hashtable with a few properties # that you'll splat on Set-Format
            $xlParams = @{WorkSheet=$ws;Bold=$true;FontSize=18;AutoSize=$true}

            # Create the headings in the Excel worksheet
            Set-Format -Range A1  -Value "Database Result" @xlParams
            
            # Close and Save the changes to the Excel file
            # Launch the Excel file using the -Show switch
            Close-ExcelPackage $excel -Show
	}catch{ Write-Warning "Error Occured, $_"}	
}

QueryDatabase $ServerName $DatabaseName $Query