如何使用导出Excel将未知数量的值写入一列?

Question

因此，使用Export-Excel和dbatools，我想将数据从一个循环传输到一列。

到目前为止，我找到的所有示例仅显示您从(即Get-Service(获得的数据

Get-Service | Select-Object -Property Name, Status, DisplayName, ServiceName

这将在第 2 行的 A、B、C 和 D 列等上创建数据。

我正在运行多个 foreach 循环：

# Check if SQL Server Agent is running
foreach ($instance in $sqlInstanceConfig) {
$agent = Get-DbaAgentServer $instance
$availabilityGroup = Get-DbaAvailabilityGroup -SqlInstance $instance
if ($agent.SqlServerRestart) {
Write-Log "INFO" "Agent is running on $instance." $ExecutionLogFullPath
}
elseif (!$agent.SqlServerRestart) {
Write-Log "WARNING" "Agent is not running on $instance." $ExecutionLogFullPath
}
# Grab availability groups, and databases inside the groups
foreach ($group in $availabilityGroup) {
Write-Log "INFO" "For instance $($group.SqlInstance), Name: $($group.AvailabilityGroup) | Role: $($group.LocalReplicaRole) | Databases: $($group.AvailabilityDatabases)" $ExecutionLogFullPath
}
} 

我还根据自己的喜好将 excel 列命名为"标题"：

$exportExcel = Export-Excel -Path "C:UsersjanbDesktoptestExport.xlsx" -ClearSheet -WorksheetName "TEST 123" -PassThru
$ws = $exportExcel.Workbook.Worksheets["TEST 123"]
$ws.Cells["A1"].Value = "SQL Instance"
$ws.Cells["B1"].Value = "Instance status"
$ws.Cells["C1"].Value = "Server agent status"
$ws.Cells["D1"].Value = "DB disk space"
$ws.Cells["E1"].Value = "DB in AG / Synchronizing"
$ws.Cells["F1"].Value = "Primary replica"
$ws.Cells["G1"].Value = "DB full & log backup"
$ws.Cells["H1"].Value = "Log backup oversize"
$ws.Cells["I1"].Value = "Backup disk space"
$ws.Cells["J1"].Value = "Job name"
$ws.Cells["K1"].Value = "Jobs correctly enabled"
$ws.Cells["L1"].Value = "Enabled job running / Last run date"

但是现在我坚持根据我从以前的循环中获得的结果数量将数据管道到 A2->A*、B2->B* 等。

因此，我要完成的是，如何在不对列值进行硬编码的情况下将数据从 $instance 写入 A2 并向下写入，因为它们可以随时更改。

Answer 1

根据我的评论：

除了您之外，没有人知道如何将 Excel 列标题映射到$instance/$group属性。虽然您的方式可以奏效，但 IMO 需要繁琐地处理每个 cel(row，col(。我会在第二个 foreach 中创建[PSCustomObject]，让 Export-Excel 一次性完成提交收集数据的繁琐工作。

像这样的事情可以做：

## Q:Test201995SO_57803106.ps1
# Check if SQL Server Agent is running
$Data = foreach ($instance in $sqlInstanceConfig) {
$agent = Get-DbaAgentServer $instance
$availabilityGroup = Get-DbaAvailabilityGroup -SqlInstance $instance
if ($agent.SqlServerRestart) {
Write-Log "INFO" "Agent is running on $instance." $ExecutionLogFullPath
}
elseif (!$agent.SqlServerRestart) {
Write-Log "WARNING" "Agent is not running on $instance." $ExecutionLogFullPath
}
# Grab availability groups, and databases inside the groups
foreach ($group in $availabilityGroup) {
Write-Log "INFO" "For instance $($group.SqlInstance), Name: $($group.AvailabilityGroup) | Role: $($group.LocalReplicaRole) | Databases: $($group.AvailabilityDatabases)" $ExecutionLogFullPath
[PSCustomObject]@{
"SQL Instance"              = $group.SqlInstance
"Instance status"           = "what"
"Server agent status"       = "ever"
"DB disk space"             = "you"
"DB in AG / Synchronizing"  = "like"
"Primary replica"           = "to"
"DB full & log backup"      = "map"
"Log backup oversize"       = "to"
"Backup disk space"         = "the"
"Job name"                  = "excel"
"Jobs correctly enabled"    = "column"
"Enabled job running / Last run date" = "here"
}
}
} 
## optionally preview Data in a gridview
# Data | Out-GridView
$ExcelFile = Join-Path ([Environment]::GetFolderPath('Desktop')) "testExport.xlsx"
$Data | Export-Excel -Path $ExcelFile -ClearSheet -WorksheetName "TEST 123" -AutoSize