CODEWITHSUNDEEP
excelpowershell
Emman
Emman

Reputation: 1234

Powershell script to match condition of excel cell values

I am novice programmer of powershell, I am trying to do excel search and change of format and font option. Here is the snippet were I am trying to search for the word "PASSED" and change the color to green and bold, currently the code does exits out without changing as expected what is wrong in this which I could not figure out, need help in this regards. 

  $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $False
    $workbook = $excel.Workbooks.Open("C:\test.xlsx")
    $sheet = $workbook.ActiveSheet

    $xlCellTypeLastCell = 11

    $used = $sheet.usedRange 
    $lastCell = $used.SpecialCells($xlCellTypeLastCell) 
    $row = $lastCell.row # goes to the last used row in the worksheet

    for ($i = 1; $i -lt $row.length; $i++) {
    If ($sheet.cells.Item(1,2).Value() = "PASSED") {
            $sheet.Cells.Item(1,$i+1).Font.ColorIndex = 10
            $sheet.Cells.Item(1,$i+1).Font.Bold = $true
              }
    }

    $workbook.SaveAs("C:\output.xlsx")
    $workbook.Close()

Input(test.xlsx) file has the following

Module | test | Status
ABC      a      PASSED

Its quiet a huge file with different status of each unit test.

Upvotes: 1

Views: 10100

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174445

$row is a string containing the last row number, comparing to it's Length property in the for loop will land you in trouble since it'll give you the length of the string itself.

Change it to:

for ($i = 1; $i -lt $row; $i++) {

In the if statement inside the loop, there's another problem: =

In order to compare two values for equality, use the -eq operator instead of = (= is only for assignment):

if ($sheet.cells.Item($i,2).Value() -eq "PASSED") {
    $sheet.Cells.Item(1,$i+1).Font.ColorIndex = 10
    $sheet.Cells.Item(1,$i+1).Font.Bold = $true
}

Lastly, Excel cell references are not zero-based, so Item(1,2) will refer to the cell that in your example has the value "test" (notice how it takes a row as the first parameter, and a column as the second). Change it to Item(2,3) to test against the correct cell, and transpose the cell coordinates inside the if block as well.

You may want to update the for loop to reflect this as well:

for ($i = 2; $i -le $row; $i++) {
    if ($sheet.cells.Item($i,3).Value() = "PASSED") {
        $sheet.Cells.Item($i,3).Font.ColorIndex = 10
        $sheet.Cells.Item($i,3).Font.Bold = $true
    }
}

Upvotes: 2

Related Questions