CODEWITHSUNDEEP
excelpowershell
Paolo Rossi
Paolo Rossi

Reputation: 2510

How to return object from function

I have a big problem that I can not understand when I take data from Excel sheet. I use this function to read data (1 row) from excel and it does so correctly

function ExtractExcelRows {
    [cmdletbinding()]
    Param($ExcelFile)

    # Excel.exe not autokill fix
    $before = Get-Process | % { $_.Id }
    $excel = New-Object -ComObject Excel.Application
    $excelId = Get-Process excel | % { $_.Id } | ? { $before -notcontains $_ }

    $workbook = $excel.Workbooks.Open($ExcelFile.FullName)
    $sheet = $workbook.Worksheets.Item(1)
    $excel.Visible = $false

    $rowMax = ($sheet.UsedRange.Rows).Count

    # Declare the starting positions
    $rowEmail, $colEmail = 1, 11

    $Rows = @()
    for ($i=1; $i -le $rowMax-1; $i++) {
        if ($sheet.Cells.Item($rowEmail+$i, $colEmail).Text) {
            $Rows += @{
                Email = $sheet.Cells.Item($rowEmail+$i, $colEmail).Text
            }
        }
    }

    $workbook.Close($false)
    $excel.Quit()

    Stop-Process -Id $excelId -Force

    Write-Host $Rows.Count  # count 1 row ! right!

    return $Rows
}

When I try to save my object in a global variable the result of the count is different and I do not understand why.

$global:ExcelData = ExtractExcelRows $ExcelFile
write-host $ExcelData.Count  # count 4 row!!!! not right!

Can anyone tell me where the error is and how to fix it?

Upvotes: 0

Views: 164

Answers (2)

Theo
Theo

Reputation: 61188

To put my comments as answer:

function ExtractExcelRows {
    [cmdletbinding()]
    Param($ExcelFile)

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false

    $workbook = $excel.Workbooks.Open($ExcelFile.FullName)
    $sheet    = $workbook.Worksheets.Item(1)
    $rowMax   = ($sheet.UsedRange.Rows).Count

    # Declare the starting positions
    $rowEmail, $colEmail = 1, 11

    $Rows = for ($i = 1; $i -lt $rowMax; $i++) {
        if ($sheet.Cells.Item($rowEmail + $i, $colEmail).Text) {
            [PSCustomObject]@{ 'Email' = $sheet.Cells.Item($rowEmail+$i, $colEmail).Text }
        }
    }

    $workbook.Close($false)
    $excel.Quit()
    # clean up used COM objects
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)    | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)    | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()

    Write-Host $Rows.Count  # count 1 row ! right!

    # The comma used as unary operator wraps the array in another single element array. 
    # Powershell unboxes that to return an array, even if it is empty.
    return ,$Rows
}

Upvotes: 1

TheMadTechnician
TheMadTechnician

Reputation: 36332

Have you checked the contents of that variable? I'm betting the first three items are True/False, or something like that. The reason is that the Excel com object's methods tend to return a value to indicate if the execution was successful or not, and all output that is not explicitly redirected is output by the function, not only the items you specify with return (for that matter, there is no need to use return). You should pipe things like $workbook.close($false) to Out-Null as such:

$workbook.close($false) | Out-Null
$excel.quit() | Out-Null

That should account for 2 of your 4 items, I'm not sure what the other one is.

Upvotes: 0

Related Questions