Basic test script on how to export to Excel using the module ImportExcel from a SQL query fired off by dbatools

testImportExcel

Import-Module dbatools;
Import-Module ImportExcel;

$credential = Get-Credential;
# get results from table 
$result= Invoke-DbaQuery -SQLInstance localhost -Database master -Query "SELECT * FROM sys.tables" -SqlCredential $credential ;
# debug the output
$result | Out-GridView;

$begin = Get-Date;
# export to excel
#$result | Export-OOXML -FileFullPath test.xlsx -WorksheetName test -AutoFit -HeaderStyle WhiteSmoke;
$result | Export-Excel $outputpath -WorksheetName test -AutoFilter -AutoSize -BoldTopRow -FreezeTopRowFirstColumn;
$end = Get-Date;

echo "duration $($end-$begin)";