PowerShell Excel procura substituir problema com loop

Question

O que estou fazendo aqui é procurar uma planilha do excel por um valor, se o valor corresponder, ele será substituído pelo primeiro valor em um determinado arquivo de texto:

exemplo:

procure pelo texto do valor encontre a primeira instância substitua pelo primeiro valor em um arquivo de texto que seja "lido", procure novamente pelo texto encontre novamente substitua pelo "leia" até não encontrar "texto", agora procure pelo segundo valor na matriz "text1", procure na planilha do Excel, encontre-o substituindo pela segunda linha no valor do texto, que é "read1" e assim por diante.

Eu tenho o script que está funcionando um pouco, o problema é que ele substituirá todos os valores da matriz apenas pela primeira linha de arquivo de texto que é "lida", não está circulando pela segunda linha do arquivo de texto.

Alguém por favor pode ajudar?

código:

arquivo test.txt lido como:

leia

read1

read2

$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()

    

Answer 1

Eu acho que o problema é que, por padrão, ele está procurando por "texto" em qualquer lugar da célula, que combina "texto", "texto1", "texto2" etc. Você pode reorganizar as coisas para que "texto" seja o último um que procura, mas isso não é ideal. Realmente, precisamos combiná-lo com a célula inteira .

Felizmente, há um parâmetro em Range.Find () que nos permite fazer exatamente isso: LookAt ( link ).

Infelizmente, é um pouco difícil acessar parâmetros nomeados em objetos COM no PowerShell, portanto, o mais fácil é simplesmente especificar os parâmetros anteriores especificamente:

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

$ First é A1, -4163 é o valor enum para procurar valores e 1 é o valor enum para todo .

A interoperabilidade do PS-COM para o Excel é realmente complicada com relação aos tipos de dados. Onde eu normalmente esperaria ser capaz de escolher a primeira célula do intervalo e passá-la para a função, ela não parece gostar disso, então eu especifiquei manualmente em $ First.

Eu procurei as enums e codifiquei-as porque isso é outra coisa dolorosa no PowerShell - espero que alguém possa sugerir uma maneira legal de fazer isso.

Então todo o script acaba sendo:

$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()

Ele irá se lembrar das configurações de busca para as chamadas FindNext (), então não é necessário alterar nada lá.

Espero que ajude!