时间戳

首页 > 解决方案 > 如何使用 Export-Excel 将未知数量的值写入一列？

powershell - 如何使用 Export-Excel 将未知数量的值写入一列？

问题描述

因此，使用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:\Users\janb\Desktop\testExport.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 并向下写入，因为它们可以随时更改。

标签： powershellexport-to-excel

解决方案

根据我的评论：

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

这样的事情可以做到：

## Q:\Test\2019\09\05\SO_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

推荐阅读