时间戳

首页 > 解决方案 > 每次我找到搜索结果时为每个 excel 行着色

excel - 每次我找到搜索结果时为每个 excel 行着色

问题描述

打开模板.xls

搜索“确定”字符串

为找到的每个结果打印地址本地

color ColorIndex = 3 出现“ok”的每一行

保存excel表格，关闭

我的问题是，尽管我在 Write-Host 上打印出正确的搜索结果，但我无法更改找到的结果行的颜色！！

这是代码工作。

$file = 'C:\Users\bigadmin\Desktop\Projects\AutoUpdate\Template.xls' 

$xl = new-object -c excel.application

$wb1 = $xl.workbooks.open($file, $null, $true) 

$ws1 = $wb1.WorkSheets.item(1) 

$ws1.activate()

$Range = $WS1.Range("A100:D110")

$Target = $Range.Find("OK")

$First = $Target

Do
{

    Write-Host $Target.AddressLocal()

   $Target.EntireRow.Interior.ColorIndex = 3

    $Target = $Range.FindNext($Target)
}

While ($Target -ne $NULL -and $Target.AddressLocal() -ne 
$First.AddressLocal())


$wb1.Save() 

$wb1.close($true) 

$xl.quit()

spps -n excel

标签： excelpowershell

解决方案

尝试这个：

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$file = 'C:\Users\bigadmin\Desktop\Projects\AutoUpdate\Template.xls' 

$searchFor            = 'Ok'
$colorIndex           = 3
$excel                = New-Object -ComObject Excel.Application
$excel.Visible        = $true
$excel.ScreenUpdating = $true

$workbook  = $excel.Workbooks.Open( $file ,$null, $false )

$ws1       = $workbook.WorkSheets.item(1) 

[void]$ws1.Activate()

$searchRange  = $ws1.Range("A100:D110")

$searchResult = $searchRange.Find( $searchFor, [System.Type]::Missing, [System.Type]::Missing, [Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole, [Microsoft.Office.Interop.Excel.XlSearchOrder]::xlByColumns )

while( $searchResult -and $ws1.Cells( $searchResult.Row, $searchResult.Column ).EntireRow.Interior.ColorIndex -ne $colorIndex  ) {

    $ws1.Cells( $searchResult.Row, $searchResult.Column ).EntireRow.Interior.ColorIndex = $colorIndex
    $searchResult = $searchRange.FindNext( $searchResult )
}

[void]$workbook.Save()
[void]$workbook.Close()
[void]$excel.Quit()

推荐阅读