Powershell - Reading Excel after formatting data

user_14448739 0
Viewed 87

I have an excel file of new-hires for the company. Firstly I need to hide all the columns that will be used for searching users. That was pretty simple and I managed to do it. Now I'm left only with the columns I really need.

Now is the real problem:

I need to filter the data and then import those usernames to the PowerShell array. So in excel it looks like this: Excel

Then I have the function:

Function GetUsernames ($WorkSheet) {

    $userName = $WorkSheet.UsedRange.Rows.Columns["UserNameColumn"].Value2

    return $userName

}

But it's returning all of the records in the Username column - 651 records instead of 476.

The function is waiting for my input after I format the excel file manually.

Any directions will be appreciated! :)

excel powershell
1 Answers

What you seek is all the values from rows in a certain column that are not hidden in the Excel file.

To get those, you need to go through the Rows of the selected column. In my Office version 2016, I cannot reference a column directly by its name, so I have extended your function to first find the column index.

Also, I have renamed the function a bit to follow the Verb-Noun convention in PowerShell

function Get-Usernames ($WorkSheet, $Column) {
    # for me (office 2016) I cannot reference a Column by its name 
    # using Columns["UserNameColumn"], so I have to find the index first
    $index = 0
    if ($Column -is [int]) {
        $index = $Column
    }
    else {
        for ($col = 1; $col -le $WorkSheet.UsedRange.Columns.Count; $col++) {
            $name = $WorkSheet.Cells.Item(1, $col).Value()  # assuming the first row has the headers
            if ($name -eq $Column) {
                $index = $col
                break
            }
        }
    }
    if ($index -gt 0) {
        # now return the values in the columns for the rows that are not hidden
        # skip the first row, because that is the column name itself
        ($WorkSheet.UsedRange.Rows.Columns($index).Rows | Select-Object -Skip 1 | Where-Object { !$_.hidden }).Value2
    }
}

You can now use the function in your script like this:

$userNames = Get-Usernames $workbook.Worksheets(1) "UserNameColumn"
user_98986430
Related