How to write an unknown number of values to one column using Export-Excel?

Question

So, using Export-Excel, and dbatools, I'd like to pipe data from one loop to one column.

All of the examples I've found so far, only show data you get from i.e. Get-Service

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

Which is going to create data on columns A, B, C and D on row 2, etc.

I'm running multiple foreach loops:

    # 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
        }

    }

I've also named the excel column "headers" to my liking:

    $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"

But now I'm stuck on piping data to A2->A*, B2->B*, etc. based on the number of results I get from my previous loops.

So what I'm trying to accomplish is, how do I write data from $instance to A2 and down without hardcoding the column values, because they can change at any time.

Nobody but you knows how to map your Excel column headers to `$instance/$group` properties. While your way could work it's IMO to cumbrsome dealing with every cel(row,col). I'd create a `[PSCustomObject]` inside the 2nd foreach and let Export-Excel Do the tedious work submitting the collected data in one go. — , Sep 05 '19 at 11:09
@f6a4 thank you, you were correct - however the correct syntax is $ws.Cells[$col, 1].Value - and then you just increment $col++ :) — GhostyIs1337, Sep 05 '19 at 14:17
use value2 instead of value. See here https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 — f6a4, Sep 06 '19 at 11:42

score 1 · Answer 1 · answered Sep 05 '19 at 11:36

According to my comment:

Nobody but you knows how to map your Excel column headers to $instance/$group properties. While your way could work it's IMO to cumbrsome dealing with every cel(row,col). I'd create a [PSCustomObject] inside the 2nd foreach and let Export-Excel Do the tedious work submitting the collected data in one go.

Something like this could do:

## 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

How to write an unknown number of values to one column using Export-Excel?

1 Answers1