☰

PowerShell Excel 搜索替换循环问题

tag-icon • tag-icon microsoft-excel powershell
PowerShell Excel 搜索替换循环问题

我在这里所做的是在 Excel 表中搜索一个值，如果值匹配，它将被给定文本文件中的第一个值替换：

例子：

搜索值文本找到第一个实例在给定的文本文件中用第一个值“read”替换，再次搜索文本再次查找用“read”替换直到找不到“text”，现在在数组“text1”中搜索第二个值，在 excel 表中搜索找到它用给定文本值中的第二行替换，即“read1”等等。

我有一个可以正常工作的脚本，问题是它将用“读取”的文本文件的第一行替换所有数组值，它不会循环遍历文本文件的第二行。

有人可以帮忙吗？

代码：

test.txt文件内容如下：

读

读1

读2

$text = "text","text1","text2","text3"
$replace=get-content C:\script\test.txt
$File = "C:\script\test.xlsx"

# Setup Excel, open $File and set the the first worksheet
$i=0
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(1)
$Range = $Worksheet.Range("A1","Z10").EntireColumn

Foreach($SearchString in $text){
$Search = $Range.find($SearchString)

if ($search -ne $null){

$SearchString
$replace[$i]

$FirstAddress = $search.Address
do {
        $Search.value() = $replace[$i]
        $search = $Range.FindNext($search)

    } while ( $search -ne $null -and $search.Address -ne $FirstAddress )

$i++
}

 }
$WorkBook.Save()
$WorkBook.Close()
[void]$excel.quit()

答案1

我认为问题在于默认情况下它会在单元格的任意位置搜索“text”，与“text”、“text1”、“text2”等匹配。您可以重新排列内容，使“text”成为它搜索的最后一个，但这并不理想。实际上，我们需要让它匹配全部的细胞。

值得庆幸的是，Range.Find() 中有一个参数可以让我们做到这一点：看着（https://msdn.microsoft.com/en-us/library/office/ff839746.aspx）。

不幸的是，在 PowerShell 中访问 COM 对象中的命名参数有点麻烦，因此更简单的方法是具体指定前面的参数：

$First = $Worksheet.Range("A1")    
$Search = $Range.find($SearchString,$First,-4163,1)

$First 是 A1，-4163 是枚举值查看值，1 是枚举值所有的。

PS-COM 的 Excel 互操作对数据类型要求非常严格。我通常希望能够从范围中挑选出第一个单元格并将其传递给函数，但它似乎不喜欢这样，所以我在 $First 中手动指定了它。

我查找了枚举并对其进行了硬编码，因为这是 PowerShell 中另一件令人痛苦的事情——希望有人能够提出一种好的方法。

因此整个脚本最终为：

$text = "text","text1","text2","text3"
$replace=get-content C:\script\test.txt
$File = "C:\script\test.xlsx"

# Setup Excel, open $File and set the the first worksheet
$i=0
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(1)
$Range = $Worksheet.Range("A1","Z10").EntireColumn
$First = $Worksheet.Range("A1")

Foreach($SearchString in $text){
$Search = $Range.find($SearchString,$First,-4163,1)

if ($search -ne $null){

$SearchString
$replace[$i]

$FirstAddress = $search.Address
do {
        $Search.value() = $replace[$i]
        $search = $Range.FindNext($search)

    } while ( $search -ne $null -and $search.Address -ne $FirstAddress )

$i++
}

 }
$WorkBook.Save()
$WorkBook.Close()
[void]$excel.quit()

它会记住 FindNext() 调用的查找设置，因此无需在那里更改任何内容。

希望有帮助！

相关内容