Skip to content

Instantly share code, notes, and snippets.

@RamblingCookieMonster
Last active August 29, 2015 14:24
Show Gist options
  • Save RamblingCookieMonster/602af9782490610ea39d to your computer and use it in GitHub Desktop.
Save RamblingCookieMonster/602af9782490610ea39d to your computer and use it in GitHub Desktop.
PSExcel.HighlightLine.ps1
<#
This code illustrates highlighting a line based on a cell value
Search-CellValue will help identify the rows we care about
Format-Cell will format these rows
Prerequisite: Download and load up PSExcel http://ramblingcookiemonster.github.io/PSExcel-Intro/
#>
# View the help on the primary commands we will be using
Get-Help Search-CellValue -Full
Get-Help Format-Cell -Full
#Set up the data
$File = 'D:\VarEg.xlsx'
$WorksheetName = 'WS1'
$DemoData = 1..10 | Foreach-Object{
$EID = Get-Random -Minimum 1 -Maximum 1000
$Date = (Get-Date).adddays(-$EID)
New-Object -TypeName PSObject -Property @{
Name = "jsmith$_"
EmployeeID = $EID
Date = $Date
FlagThis = (Get-Random -InputObject $false, $True)
} | Select Name, EmployeeID, Date, FlagThis
}
$DemoData | Export-XLSX -Path $File -WorksheetName $WorksheetName -force
# Find rows that have a false in them. Note that if you have false in another column, you'll have to filter out that column before expanding the row.
$Rows = Search-CellValue -Path $File -WorkSheetName $WorksheetName -FilterScript {$_ -eq $false} | Select -ExpandProperty Row
#We have rows... Loop through them and set background to red
$Excel = New-Excel -Path $File
$WorkSheet = $Excel | Get-WorkSheet -Name $WorksheetName
foreach($Row in $Rows)
{
Format-Cell -StartRow $Row -EndRow $Row -WorkSheet $WorkSheet -BackgroundColor Red
}
$Excel | Close-Excel -Save
#View the damage. Red is a jarring color!
ii $File
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment