Powershell脚本以匹配Excel单元格值的条件 [英] Powershell script to match condition of excel cell values

问题描述

我是powershell的新手程序员，我正在尝试执行excel搜索以及更改格式和字体选项.这是我正在尝试搜索通过"一词并将其颜色更改为绿色和粗体的代码段，当前代码确实退出了，而没有按预期更改，这是我不知道的错误所在，需要帮助这方面.

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)文件具有以下内容

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.

推荐答案

$ row 是包含最后一个行号的字符串，与 for 循环会给您带来麻烦，因为它会给您字符串本身的长度.

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

将其更改为:

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

在循环内的 if 语句中，还有另一个问题: =

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

为了比较两个值是否相等，请使用 -eq 运算符而不是 = ( = 仅用于分配):

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
}

最后，Excel单元格引用不是从零开始的，因此 Item(1,2)将引用示例中值为"test"的单元格(请注意，它如何占用一行(第一个参数，第二列).将其更改为 Item(2,3)以测试正确的单元格，并将单元格坐标也转置到if块内.

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.

您可能还希望更新 for 循环以反映这一点:

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

这篇关于Powershell脚本以匹配Excel单元格值的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！