CODEWITHSUNDEEP
powershellms-wordcom
wgray
wgray

Reputation: 75

Coloring specific cells of a table in word

I currently am making a table and searching through each cell to find specific text and color cells based on that text. The table creation happens quickly in less than a second normally but adding color to each cell that need sot is extremely slow. Is there any better way to do this?

This is my current code.

$Word = New-Object -comobject word.application
$Word.Visible = $true
$Doc = $Word.Documents.Add()
$Range = $Doc.Range()

$text=(Import-CSV "c:\users\user\documents\AIX\Server Owner.csv" -header @("Server name", "Description", "OS", "OS EOL", "SQL", "SQL EOL") )
$text = $text -replace ",",""
$newtext = (($text -replace "@{Server name=(.*)?; Description=(.*)?; OS=(.*)?; OS EOL=(.*)?; SQL=(.*)?; SQL EOL=(.*)?}", '$1, $2, $3, $4, $5, $6') | Out-String).trim()
$newtext
$Range.Text = "Server name, Description, OS, OS EOL, SQL, SQL EOL`n$newtext"
$separator=[Microsoft.Office.Interop.Word.WdTableFieldSeparator]::wdSeparateByCommas
$table=$Range.ConvertToTable($separator)
$table.AutoFormat([Microsoft.Office.Interop.Word.WdTableFormat]::wdTableFormatNone)
$Table.Style = "Medium Shading 1 - Accent 1"

#Adds colours to the table blocks
#How do I make this faster

This part below is what I need to speed up

$x = 2
foreach($l in $text) {
    if((($Table.Cell($x,4).Range.Text | Out-String) -replace "","").trim() -eq 'Out of date') {
        $Table.Cell($x,4).Range.shading.BackgroundPatternColor = 255
    }
    elseif((($Table.Cell($x,4).Range.Text | Out-String) -replace "","").trim() -like "*!*") {
        $Table.Cell($x,4).Range.shading.BackgroundPatternColor = 65535
    }

    if((($Table.Cell($x,6).Range.Text | Out-String) -replace "","").trim() -eq 'Out of date') {
        $Table.Cell($x,6).Range.shading.BackgroundPatternColor = 255
    }
    elseif((($Table.Cell($x,6).Range.Text | Out-String) -replace "","").trim() -like "*!*") {
        $Table.Cell($x,6).Range.shading.BackgroundPatternColor = 65535
    }
    $x++
}

Basically what is happening is it goes through each row of the table and checks columns 4 and 6. It the cells for "Out of Date" and the character "!". If the cells contains either of those the color is changed to either yellow or red. The "| Out-String) -replace "","").trim()" part is just to make sure it is properly formatted when comparing.

An example line from the import CSV

"Server name","Server description","Microsoft Windows Server 2008 R2 (64-bit)","14-Jan-2020","Microsoft SQL Server 2008 R2 SP1 Standard","9-Jul-2019 if updated to the latest service pack (SP3)!"

Which when imported using Import-Csv would look like

@{Server name=Server name; Description=Server description; OS=Microsoft Windows Server 2008 R2 (64-bit); OS EOL=14-Jan-2020; SQL=Microsoft SQL Server 2008 R2 SP1 Standard; SQL EOL=9-Jul-2019 if updated to the latest service pack (SP3)!;}

And because SQL EOL has the character ! in it, the cell would be coloured yellow.

Upvotes: 2

Views: 1880

Answers (1)

wgray
wgray

Reputation: 75

Searching through the CSV that is imported and then changing the colors based on what you find in the CSV is much faster than searching the table. It's not nearly as fast as the table creation itself, but it'll do for now. Here is my updated code.

$Word = New-Object -comobject word.application
$Word.Visible = $true
$Doc = $Word.Documents.Add()
$Range = $Doc.Range()

$text=(Import-CSV "c:\users\user\documents\AIX\Server Owner.csv" -header @("Server name", "Description", "OS", "OS EOL", "SQL", "SQL EOL") )
$newtext = $text -replace ",",""
$newtext = (($newtext -replace "@{Server name=(.*)?; Description=(.*)?; OS=(.*)?; OS EOL=(.*)?; SQL=(.*)?; SQL EOL=(.*)?}", '$1, $2, $3, $4, $5, $6') | Out-String).trim()
$newtext
$Range.Text = "Server name, Description, OS, OS EOL, SQL, SQL EOL`n$newtext"
$separator=[Microsoft.Office.Interop.Word.WdTableFieldSeparator]::wdSeparateByCommas
$table=$Range.ConvertToTable($separator)
$table.AutoFormat([Microsoft.Office.Interop.Word.WdTableFormat]::wdTableFormatNone)
$Table.Style = "Medium Shading 1 - Accent 1"

#Adds colours to the table blocks
$x = 2
foreach($l in $text) {
    if($l."OS EOL" -like 'Out of date') {
        $Table.Cell($x,4).Range.shading.BackgroundPatternColor = 255
    }
    elseif($l."OS EOL" -like "*!*") {
        $Table.Cell($x,4).Range.shading.BackgroundPatternColor = 65535
    }

    if($l."SQL EOL" -like 'Out of date') {
        $Table.Cell($x,6).Range.shading.BackgroundPatternColor = 255
    }
    elseif($l."SQL EOL" -like "*!*") {
        $Table.Cell($x,6).Range.shading.BackgroundPatternColor = 65535
    }
    $x++
}

Remove-variable x, table, text, newtext, range, separator, word

Upvotes: 1

Related Questions