Can't get 'Close-ExcelPackage' to work properly

[SamScripting]

Dead new to your module, so apologies for the abhorrent code you're about to look at.

function Get-CellRange {
    param(
        [string]$maxC,
        [string]$row
    )

    $col = 1
    $isRangeEmpty += @()

    foreach ($i in 1..$maxC) {
        $isRangeEmpty += $worksheet[$row, $col].Value
        $col++
    } 

    foreach ($cell in $isRangeEmpty) {
        if (-not [string]::IsNullOrWhiteSpace($cell)) {
            return $false
        } 
    }

    return $row
}

if (-not (Get-Module -ListAvailable -Name ImportExcel)) {
    Install-Module -Name ImportExcel -Scope CurrentUser -Force
}

$computerTable = @{
    "Hostname" = "Nexus"
    "Model" = "No"
    "Purchase Date" = "Today"
    "Asset Tag" = "1234"
    "Manufacturer Serial" = "123"
    "Status" = "Good"
    "User" = "You"
    "Owner" = "Me"
}

$file = "c:\users\admin\downloads\excel.xlsx"

$excelPkg = Open-ExcelPackage -Path $file
$worksheet = $excelPkg.Workbook.Worksheets["sheet1"].Cells
$headerTitle = @()

$col = 1
while ($true) {
    $cell = $worksheet[1, $col].Value
    if ($cell) {
        $headerTitle += $cell
    } else {
        $maxColumns = $col
        break
    }
    $col++
}

$row = 2

while ($true) {
    $emptyRowCheck = Get-CellRange  -maxC $maxColumns -row $row
    if ($emptyRowCheck -eq $false) {
        $row++
    } else {
        break
    }
}

$col = 1

foreach ($item in $computerTable.GetEnumerator()) {
    $worksheet[$row, $col].value = $($item.Value)
    $col++
}

Close-ExcelPackage $file

For whatever reason, no matter what I do, I can't properly find the parameters for Close-ExcelPackage. I'm pretty sure it's the proper thing to be calling at the end when I want to save and end the script - I got it in your FAQ documentation. But no matter what I do, nothing seems to make this work. Changing the path from ~ to c:\users\admin did not change anything, either.

Also for what it's worth, the script's tl;dr is "find the bottom-most row that has nothing in it for the range the headers span, then fill it in with the values from the hash tables". I have no doubt this is very sloppily done compared to some of the ways this could be cut down to like, 30 lines, if even, but like I said - I'm new to your script and am just doing this the hard way, I suppose. Would love to be pointed in the right direction of "read up on this thing over here", though. Frankly, it could be an array too, but I want to be sure that it's always put in the same column header as the key value. It doesn't do that right now, but the script won't even finish, anyway, and I KNOW this is horrifically overengineered as is.

[SamScripting]

Got my problem fixed. Close-ExcelPackage wanted to be fed back the variable that was stored using Open-ExcelPackage, IE

Close-ExcelPackage -ExcelPackage $excelPkg

Though if someone wants to tell me how to clean all this code up and have it so I can plug my hashtable into -append with export-excel, I'd love that. I can't seem to make that work for the life of me

[dfinke]

Try this

$computerTable = [PSCustomObject]@{
    "Hostname"            = "Nexus"
    "Model"               = "No"
    "Purchase Date"       = "Today"
    "Asset Tag"           = "1234"
    "Manufacturer Serial" = "123"
    "Status"              = "Good"
    "User"                = "You"
    "Owner"               = "Me"
}

$file = "$PSScriptRoot\excel.xlsx"
Remove-Item $file -ErrorAction SilentlyContinue

$data = 1..10 | ForEach-Object {
    $computerTable 
}

$data | Export-Excel $file -AutoSize -AutoFilter -TableName Data -Append -Show

[SamScripting]

Try this

$computerTable = [PSCustomObject]@{
    "Hostname"            = "Nexus"
    "Model"               = "No"
    "Purchase Date"       = "Today"
    "Asset Tag"           = "1234"
    "Manufacturer Serial" = "123"
    "Status"              = "Good"
    "User"                = "You"
    "Owner"               = "Me"
}

$file = "$PSScriptRoot\excel.xlsx"
Remove-Item $file -ErrorAction SilentlyContinue

$data = 1..10 | ForEach-Object {
    $computerTable 
}

$data | Export-Excel $file -AutoSize -AutoFilter -TableName Data -Append -Show

Thanks, I actually got it working last night. Really helpful how the hash table keys automatically match the header names to plug in the values to the lowest row by nature.

I'm guessing my issue was something to do with not properly ensuring the worksheet name I specified was identical. Is there a wildcard flag or something that will just match the first sheet, or if I omit it, will it always match the first (it should be the only sheet) one?

Also, anywhere you'd suggest I'd poke around to look up some very basic examples? A lot of the examples in the code hosted here are way over my head since I don't currently need tables or anything fancy, I just need some very basic looking up of cells, rows, columns, ranges, and getting the data returned, or plugging in data I acquire from other variables into the aforementioned. Basically every video example online was just a retelling of the "pipe Get-Process to an excel sheet".

E: Sorry, didn't mean to reopen the issue with that comment. E2: It's literally as simple as importing the worksheet to a variable and iterating over it while accessing the attributes (classes? whatever the $var.___) is technically called. Crazy simple. This is amazing. I really wish this kind of dead basic stuff was demo'd anywhere in the examples, just half an hour of this super simple manipulation would have gone a loooooong way in the beginning.

[dfinke]

@SamScripting glad you got it working. Thank you for the feedback.

On the Export-Excel if you don't specify the worksheet, it picks the first. There is no wild carding for the export.

Interesting, I have several videos on this, posted for a few years. I don't have one on Import-Excel. Based on what you are describing, I think I did not do a video on that in depth because that is a what you learn about PowerShell early on.

$data  = Get-Process
$data[0..3].Name

$data  = Import-Excel .\excel.xlsx
$data[0..3].HostName

That is leveraging PowerShell, nothing special to Import-Excel.

Would be happy to chat with you about the other topics you mentioned. I think they would be good to have. I could curate them and add it.

Thanks again - Doug

[SamScripting]

@SamScripting glad you got it working. Thank you for the feedback.

On the Export-Excel if you don't specify the worksheet, it picks the first. There is no wild carding for the export.

Interesting, I have several videos on this, posted for a few years. I don't have one on Import-Excel. Based on what you are describing, I think I did not do a video on that in depth because that is a what you learn about PowerShell early on.

$data  = Get-Process
$data[0..3].Name

$data  = Import-Excel .\excel.xlsx
$data[0..3].HostName

That is leveraging PowerShell, nothing special to Import-Excel.

Would be happy to chat with you about the other topics you mentioned. I think they would be good to have. I could curate them and add it.

Thanks again - Doug

I'm definitely new to programming so my fundamentals, especially when it comes to the intrinsic understanding of things like objects and their classes (that's the word for the _.Attribute, right?) is not something I know second nature. To be frank, I largely started with a bunch of asking ChatGPT questions and just...doing, as opposed to always truly understanding, even if I always attempt to circle back and not just accept code it spits at me every time. I can definitely see now how it essentially just breaks down cells into objects with indexes for rows and attributes for headers, but coming in blind as a new person, I'll definitely admit I didn't 'get' that. I still probably don't, fully.

Just another reason for me to get around to reading that Powershell in a Month of Lunches everybody talks about, I suppose.