Powershell查找Excel单元格参考 [英] Powershell find excel cell reference

问题描述

我正在使用以下powershell代码在excel文档中搜索字符串，并根据找到的内容返回true或false.

I am using the following powershell code to search through a excel document for a string and return true or false depending on if its found.

if (test-path $filePath) {
$wb = $xl.Workbooks.Open($filePath)
if ([bool]$xl.cells.find("German")) {$found = 1}
}

我希望能够找到该字符串的单元格引用(如果找到)，但是我无法弄清楚它或在Google上找到答案.你能帮忙吗?

I want to be able to get the cell reference of the string if its found but I cant figure it out or find an answer on google. Can you help?

推荐答案

虽然有一种方法可以在整个工作簿中搜索值，通常是

While there is a method to search through an entire workbook for a value, typically a Range.Find method is performed on a worksheet. You are setting a var to the workbook but still using the application as the search. You should be getting the worksheet to search from the workbook and using that as the target of the Find operation.

以下是对PS1的一些建议修改.

Following are some suggested modifications to your PS1.

$filePath = "T:\TMP\findit.xlsx"
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
if (test-path $filePath) {
$wb = $xl.Workbooks.Open($filePath)
$ws = $xl.WorkSheets.item("sheet1")
if ([bool]$ws.cells.find("German")) 
    {
    $found = 1
    write-host $found
    write-host $ws.cells.find("German").address(0, 0, 1, 1)
    }
}

要继续搜索所有出现的内容，请使用 Range.FindNext方法，直到您循环回到原始单元格地址.

To continue the search for all occurrences use the Range.FindNext method until you loop back to the original cell address.

$filePath = "T:\TMP\findit.xlsx"
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
if (test-path $filePath) {
$wb = $xl.Workbooks.Open($filePath)
$ws = $wb.WorkSheets.item("sheet1")

$rc1 = $ws.cells.find("German")
if ($rc1) 
    {
    $found = 1
    $addr = $rc1.address(0, 0, 1, 0)
    do
        {
        $rc1 = $ws.cells.findnext($rc1)
        write-host $rc1.address(0, 0, 1, 0)
        } until ($addr -eq $rc1.address(0, 0, 1, 0))
    }
}

由于缺少很多代码，因此很难提供比一般性更多的信息.我已经用自己的测试环境填充了缺少的信息.

It's hard to provide much more than generalities since so much of your code is missing. I've filled in the missing information with my own test environment.

这篇关于Powershell查找Excel单元格参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！