在单词中为表格的特定单元格着色

Question

我目前正在制作一个表格并搜索每个单元格以查找特定的文本并根据该文本为单元格着色。表格创建通常在不到一秒的时间内快速完成，但为每个需要 sot 的单元格添加颜色非常慢。有没有更好的方法可以做到这一点？

这是我当前的代码。

$Word = New-Object -comobject word.application
$Word.Visible = $true
$Doc = $Word.Documents.Add()
$Range = $Doc.Range()
$text=(Import-CSV "c:usersuserdocumentsAIXServer 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

下面的这部分是我需要加速的

$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++
}

基本上，它遍历表的每一行并检查第 4 列和第 6 列。它是"过时"的单元格和字符"！如果单元格包含其中任何一个，则颜色将更改为黄色或红色。"|Out-String( -replace "，"(.trim((" 部分只是为了确保在比较时格式正确。

导入 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)!"

当使用导入-CSV 导入时

，它看起来像@{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)!;}

而且因为 SQL EOL 中有字符 ！，所以单元格将被涂成黄色。

Answer 1

搜索导入的 CSV，然后根据您在 CSV 中找到的内容更改颜色比搜索表快得多。它没有表创建本身那么快，但现在可以。这是我更新的代码。

$Word = New-Object -comobject word.application
$Word.Visible = $true
$Doc = $Word.Documents.Add()
$Range = $Doc.Range()
$text=(Import-CSV "c:usersuserdocumentsAIXServer 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