使用powershell将SQL脚本的返回导出到Excel文档

Question

目前我有以下代码，它获取返回表并将其输出到CSV文件中。

Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location
$SQLServer = "localhost"
$today = (get-date).ToString("dd-MM-yyyy")
$DBName = "ZoomBI"
$ExportFile = "\Shared_DocumentsFC FolderDespatchBrexit FilesDHLDHL "+$today+".csv"
$Counter = 0
$Storedprocedure = "EXEC [dbo].[DHLDeliveries]"
while ( $true )
{
# Remove the export file
if (Test-Path -Path $ExportFile -PathType Leaf) {
Remove-Item $ExportFile -Force
}
# Clear the buffer cache to make sure each test is done the same
$ClearCacheSQL = "DBCC DROPCLEANBUFFERS"
Invoke-Sqlcmd -ServerInstance $SQLServer -Query $ClearCacheSQL
# Export the table through the pipeline and capture the run time. Only the export is included in the run time.
$sw = [Diagnostics.Stopwatch]::StartNew()
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DBName -Query $Storedprocedure | Export-CSV -Path $ExportFile -NoTypeInformation
$sw.Stop()
$sw.Elapsed
$Milliseconds = $sw.ElapsedMilliseconds    
$Counter++
Exit
}

但是，我需要能够将结果输出到具有两张表的Excel文档中并将结果放入每张纸中。

# Create a Excel Workspace 
$excel = New-Object -ComObject Excel.Application 

# make excel visible 
$excel.visible = $true 

# add a new blank worksheet 
$workbook = $excel.Workbooks.add() 

# Adding Sheets 
foreach($input in (gc c:tempinput.txt)){ 
$s4 = $workbook.Sheets.add() 
$s4.name = $input 
} 

# The default workbook has three sheets, remove them 
($s1 = $workbook.sheets | where {$_.name -eq "Sheet1"}).delete()  

#Saving File 
"`n" 
write-Host -for Yellow "Saving file in $env:userprofiledesktop" 
$workbook.SaveAs("$env:userprofiledesktopExcelSheet_$Today.xlsx")

有人能帮忙吗？

Answer 1

我来看看ImportExcel模块。我花了两行代码创建了一个包含两张表格的excel文档。

https://www.powershellgallery.com/packages/ImportExcel/5.4.2

https://www.youtube.com/watch?v=fvKKdIzJCws&list=PL5uoqS92stXioZw-u-ze_NtvSo0k0kq