Powershell，Excel在单元格中查找字符串，颜色行，删除其他 [英] Powershell, Excel Finding string in cell, Color row, delete others

查看：65 发布时间：2021/5/5 19:10:04 excel powershell

本文介绍了Powershell，Excel在单元格中查找字符串，颜色行，删除其他的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

那么，我正在寻找一种读取Excel工作表的方法；

I am looking for a way to read my excel sheet, then;

找到空单元格并为其命名，然后将其标记为黄色.
查找包含某些单词集的行并将其标记为红色.如果脚本
如果找不到行中指定的单词，则应删除整行.

到目前为止，这是我的脚本:任何帮助将不胜感激

在@TheMadTechician的帮助下更新

Updated with help of @TheMadTechician

#If there is no Out-Clipboard, set it
If(!(Get-Command Out-Clipboard -ErrorAction SilentlyContinue)){Set-Alias Out-Clipboard "$env:SYSTEMROOT\System32\clip.exe"}

#Get current date
$Date = get-date -format yyyy-MM-dd
$Company = "company"
$Company2 = "company2"
#Define all files/Paths.
$Path = "C:\$Company2\BlockedIP"
md "$Path\HTML\$Date" -Force |Out-Null
$path2 = "$Path\HTML\$Date"
$PathWeb = "/HTML/$Date"

#Path = C:/$Company2/BlockedIP
#Path2 = C:/$Company2/BlockedIP/HTML/2014-07-09

#Define File's used or created in this script.
$File = "$Path\IP-$Date.txt"
$FileHtml = "$Path2\IP-$Date.htm"
$FileXML = "$Path\IP-$Date.xlsx"
$FileHTMLWeb = "$PathWeb\IP-$date.htm"

#File = C:/$Company2/BlockedIP/IP-2014-07-09.txt
#FileXML = C:/$Company2/BlockedIP/HTML/2014-07-09/IP-2014-07-09.htm
#FileHtml = C:/$Company2/BlockedIP/HTML/2014-07-09/IP-2014-07-09.xlsx
#FileHTMLWeb = PublicIP/HTML/2014-07-09/IP-2014-07-09.htm



#Define error actions.
#$erroractionpreference = "SilentlyContinue"

#Get content from given IP list.
$colComputers = @(get-content $File | Sort -unique)
$count = $colComputers.Count
    write-output "$Count IP's detected."

#Get DNS Results
$Progress=1
$DNSResults = $colComputers | %{
 Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$count)" -PercentComplete ($Progress/$Count*100) -Status "Please stand by"
try {
    ($dnsresult = [System.Net.DNS]::GetHostEntry($_))|out-null
}
catch {
    $dnsresult = "Fail"
}
[PSCustomObject][Ordered]@{
    Source=$_.ToUpper()
    HostName=$dnsresult.HostName
    IPAddress=$dnsresult.AddressList[0].ToString()
}
$Progress++
}

$DNSResults | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard

#Open Excel.
$a = New-Object -comobject Excel.Application

#Since we want this script to look like it's being used without excel I set it's visibility to false.
$a.visible = $True

#Disable excel confirmations.
$a.DisplayAlerts = $False

<# 
# set interactive to false so nothing from excel is shown.
$Excel.DisplayAlerts = $false
$Excel.ScreenUpdating = $false
$Excel.Visible = $false
$Excel.UserControl = $false
$Excel.Interactive = $false
#>

#Create sheets in Excel.
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Activate() | Out-Null

#Create a Title for the first worksheet and adjust the font
$c.Cells.Item(1,1)= "Blocked IP's $Date"
$c.Cells.Item(1,1).Font.ColorIndex = 55
$c.Cells.Item(1,1).Font.Color = 8210719
$range = $c.Range("a1","e1")
$range.Style = 'Title'
$range.Select()
$range.MergeCells = $true
$range.VerticalAlignment = -4108

$CounterRow = $Count+5
#Define subjects.
$c.Name = "Blocked IP's ($Date)"
$c.Cells.Item(2,1) = "Given IP"
$c.Cells.Item(2,2) = "Resolved DNS"
$c.Cells.Item(2,3) = "Returned IP"
$c.Cells.Item(2,5) = "$Company"
$c.Cells.Item($Count+5,1) = "Created by"
$link = "http://www.$Company"
$link2 = "https://www.linkedin.com/profile/view?id=#########"
$r = $c.Range("E2") 
[void]$c.Hyperlinks.Add($r, $link) 
$r = $c.Range("A$Counterrow") 
[void]$c.Hyperlinks.Add($r, $link)

#Define cell formatting from subjects.
$c.Range("A2:E2").Interior.ColorIndex = 6
$c.Range("A2:E2").font.size = 13
$c.Range("A2:E2").Font.ColorIndex = 1
$c.Range("A2:E2").Font.Bold = $True

#Define html code for Excel save to .htm.
$xlExcelHTML = 44

#Define the usedrange, excluding header and footer rows
$e = $c.Range("A3:E$($DNSResults.Count+2)")

#Populate data into spreadsheet
$DNSResults | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
#$c.Cells.Item(3,1).Select()
$c.Paste($e,$false)

$e = $c.Range("A3:C$($DNSResults.Count+2)")
$c.Paste($e,$false)

$Keywords = "Google","thenetworkfactory","HappyTreeFriends"
$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"

$DNSResults | Where{$_ -match $filter} | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard


ForEach($Cell in $e){
If([String]::IsNullOrWhitespace($Cell.value2)){$Cell.interior.colorindex=6}
}

#Define the usedrange for autofitting.
$d = $c.UsedRange

#Set background color for the IP list.
$E.interior.colorindex = 15

#Define borders here.
$xlOpenXMLWorkbook = 51
$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlRight = -4152
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2 
$selection = $c.range("A3:C$($DNSResults.Count+2)")
$selection.select() |out-null
$selection.HorizontalAlignment = $xlRight
$selection.VerticalAlignment = $xlBottom
$selection.WrapText = $false
$selection.Orientation = 0
$selection.AddIndent = $false
$selection.IndentLevel = 0
$selection.ShrinkToFit = $false
$selection.ReadingOrder = $xlContext
$selection.MergeCells = $false
$selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin



#Make everything fit in it's cell.
$d.EntireColumn.AutoFit() | Out-Null

#Save the file as .xlsx on every placed IP to ensure the file is not lost due to any reason.
$b.SaveAs("$FileXML")

#Clear screen on every checked IP to remove the 'True' statement.
#cls
#


#Save final result as a .htm file
$b.SaveAs("$FileHTML",$xlExcelHTML)

#Close and quit Excel.
$b.Close()
get-process *Excel* | Stop-Process -force

#Move .txt file to the correct HTML folder.
move-item $file $path2 -Force

#Move .xlsx file to the correct HTML folder.
move-item $filexml $path2 -Force

#Declare XLSX file for mail
$MailXML = "$path2\IP-$Date.xlsx"

#Clear screen, again. (Let's keep things tidy.)
#cls

#Variables for public IP
# I am defining website url in a variable
$url = "http://checkip.dyndns.com" 
# Creating a new .Net Object names a System.Net.Webclient
$webclient = New-Object System.Net.WebClient
# In this new webdownlader object we are telling $webclient to download the
# url $url 
$IpPublic = $webclient.DownloadString($url)
# Just a simple text manuplation to get the ipadress form downloaded URL
# If you want to know what it contain try to see the variable $IpPublic
$IpPublic2 = $IpPublic.ToString()
$ipPublic3 = $IpPublic2.Split(" ")
$ipPublic4 = $ipPublic3[5]
$ipPublic5 = $ipPublic4.replace("</body>","")
$FinalIPAddress = $ipPublic5.replace("</html>","")
$ipLocal = (Get-WmiObject -class win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"').ipaddress[0]

#Variables e-mail.
$From = "Blocked IP <r.van.tour@$Company>"
$To = "IT Dept <r.van.tour@$Company>"
$CC = "Someone <$Company2@$Company"
$Subject = "Blocked IPs for $date ($Count Total)"
#The href should point to the htm file in your iis/apache folder.
$WebLink = $FinalIPAddress+$FileHtmlWeb
$here = "<a href='http://$Weblink'><b>Here</b></a>"
#Define the body of your e-mail, in this case it displays a message and shows the server it is send from with it's local IP.
#A link to the .htm file, how many IP's were blocked and the date of the message.
$Body = "<!DOCTYPE html><html><head> <title>Blocked IP's $Date</title></head><header><h1>Blocked IP</h1><p><time pubdate datetime='$date'></time></p></header><br>" 
$body += "<body>Dear <font color=black>$to</font>,<br><br>"
$body += "This is an automated message generated by server: <font color=red><b>$env:COMPUTERNAME, $IPLocal.</b></font><br><br>"
$body += "Click <font color=red><b>$here</b></font> to see the Blocked IP report for $date containing $count IP's.<br>"
$body += "Or see the attachment to open it in Excel.<br></body></html>"


#Clear screen, again. (Let's keep things tidy.)
#cls

#Send output as e-mail.
$SMTPServer = "smtp.gmail.com"
$SMTPPort = "587"
$Username = "##@gmail.com"
$Password = "##"
$message = New-Object System.Net.Mail.MailMessage
$message.IsBodyHTML = $true
$message.ReplyTo = $From
$message.Sender = $From
$message.subject = $subject
$message.body = $body
$message.to.add($to)
$message.from = $From
$message.attachments.add($MailXML)
$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort);
$smtp.EnableSSL = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
$smtp.send($message)

#Create a function to relase Com object at end of script.
function Release-Ref ($ref) { 
([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
[System.__ComObject]$ref) -gt 0) 
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 
                        }

#Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$a) | Out-Null

#Clear screen for the final time. (Let's keep things tidy.)
#cls

#Exit powershell 
exit

@TheMadTechnician

脚本的这一部分似乎存在问题，其中未解析的IP地址无法显示在$ DNSResults中

There seems to be a problem with this part of the script where non-resolved IP adresses fail to be shown in $DNSResults

$Keywords = "Google","Cloudflare","Cloud","Ping","Easy-    Voyage","McAfee","Pingdom","Panopta","Scoot","Uniglobe"
$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"

#Define error actions.
#$erroractionpreference = "SilentlyContinue"

#Get content from given IP list.
$colComputers = @(get-content $File | Sort -unique)
$SourceCount = $colComputers.Count
write-output "$SourceCount IP's detected."

#Get DNS Results
$Progress=1
$DNSResults = $colComputers | %{
Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$sourcecount)" -PercentComplete ($Progress/$sourceCount*100) -Status "Please stand by"
try {
    ($dnsresult = [System.Net.DNS]::GetHostEntry($_))|out-null
}
catch {
    $dnsresult = "Fail"
}
[PSCustomObject][Ordered]@{
    Source=$_.ToUpper()
    HostName=$dnsresult.HostName
    IPAddress=$dnsresult.AddressList[0].ToString()
}
$Progress++
}

$count = ($DNSResults|?{$_ -match $filter}).count

推荐答案

好吧，我想您是从所有发现的脚本中借用并拼凑而成的，因为它们的组合方式似乎是计划外的.就像添加和修订了所有内容一样，没有一个总体规划可以将所有内容整合在一起.那么，从哪里开始呢?有了进度条，因为我已经解决了.

Ok, I'm guessing you have borrowed and pieced this together from scripts found all over because the way it is put together seems kind of unplanned. Like things were added and revised, and there wasn't a master plan that put it all together. So, where to start? With the progress bar, since I already addressed that.

将 $ i = 1 移动到 ForEach($ colComputers中的$ strComputer)上方，并在 $ intRow = $之后添加 $ i ++ intRow + 1 (可以缩写为 $ intRow ++ ).但是我们已经知道了.由于我已经对您的脚本进行了很多修改，因此这成为了争论的焦点，但是在实践中，您将在接下来出现的ForEach循环中看到它.

Move $i=1 above ForEach($strComputer in $colComputers) and add $i++ after $intRow = $intRow + 1 (which could be shortened to $intRow++). But we already knew that. This kind of becomes a moot point, since I've reworked a good bit of your script, but in practice you'll see it with the ForEach loop coming up next.

接下来，您的ForEach循环.男孩，那真是个傻瓜.您正在使它一遍又一遍地做事，而实际上，只需要在所有步骤完成后再做一次即可.那么，循环后我们将移动到什么位置?让我们从几乎所有内容开始.单元格格式化?之后.填充细胞?之后.排序?之后.调整列宽?之后.保存文件?以后！

Next, your ForEach loop. Boy, that's quite a doozy there. You are making it do things over, and over, and over that really just need to be done once after everything is complete. So, what shall we move to after the loop? Let's start with, well, almost everything. Cell formatting? Later. Filling cells? Later. Sorting? Later. Adjusting column width? Later. Saving the file? Later!

那么，那让我们陷入了循环?确实不多，剩下的只是进度条和检查DNS条目.为什么要这样呢?因为我们可以在PowerShell中使用所需的数据创建一个数组，对数据进行排序，从数组中仅选择要使用的字段，然后一次粘贴所有数据，而不是一次粘贴一个记录，然后执行所有操作之后的格式.实际上，格式化可能会更好，而不是检查单元格是否为空白并将其着色为红色，只需对其应用条件格式设置，以便如果它们为空白，则显示为红色，然后让Excel为您完成工作.

So, what does that leave us in the loop? Not much really, all it leaves is the progress bar and checking DNS entries. Why do it this way? Because we can create an array in PowerShell with the data you want, sort the data, select only the fields that you want to use from the array, and then paste all data in at once instead of one record at a time, and do all the formatting afterwards. In fact, what would probably be better for formatting, instead of checking cells for blanks and coloring them red is to just apply Conditional Formatting to them so that if they're blank they show up red, and let Excel do the work for you.

这将使脚本的运行速度大大提高，因为您不会多次执行相同的工作，并且正在PowerShell中处理原始数据，而不是让Excel执行此操作.我最终将您的ForEach循环减少到只有几行:

This is going to make the script run a lot faster since you aren't doing the same work several times, and are working with raw data in PowerShell instead of making Excel do it. I ended up reducing your ForEach loop down to just a few lines:

#Get DNS Results
$Progress=1
$DNSResults = $colComputers | %{
     Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$count)" -PercentComplete ($Progress/$Count*100) -Status "Please stand by"
    try {
        ($dnsresult = [System.Net.DNS]::GetHostEntry($_))|out-null
    }
    catch {
        $dnsresult = "Fail"
    }
    [PSCustomObject][Ordered]@{
        Source=$_.ToUpper()
        HostName=$dnsresult.HostName
        IPAddress=$dnsresult.AddressList[0].ToString()
    }
    $Progress++
}

这将遍历条目，并创建具有3个属性的自定义对象数组，这3个属性是您在电子表格中所需的3个单元格.

That will loop through the entries and create an array of custom objects that have 3 properties that are the 3 cells you wanted in your spreadsheet.

好吧，您显然在格式和注入简单文本方面都掌握了一些知识，因此，我现在就对您的标题设置进行详细介绍，然后将处理后的数据输入电子表格中.

Ok, you obviously have a grasp of things as far as formatting and injecting simple text, so I'm going to glaze over your title setup for now and get to getting the processed data into the spreadsheet.

对于数据数组(例如您要放入的Computer/HostName/IP数据数组)，将其作为制表符分隔的CSV对象粘贴到Excel中更为容易.即使我认为应该，Out-Clipboard也不是标准的PowerShell cmdlet.另一方面，Clip.exe是Windows的标准配置，因此我们可以为其设置别名(如果您具有PowerShell社区扩展，则已经为您完成了此操作).我将其放在脚本的顶部，以使其不受干扰.通常，如果需要设置这样的别名，我会把它放在脚本中设置的所有功能之后.它会检查您是否具有Out-Clipboard，如果没有，则会将Clip.exe的别名设置为Out-Clipboard.

For arrays of data (like your Computer/HostName/IP array of data that you were putting in) it is easier to paste it into Excel as a tab delimited CSV object. Out-Clipboard isn't a standard PowerShell cmdlet, even if I think it should be. On the other hand Clip.exe comes standard with windows, so we can just set an alias for it (if you have the PowerShell Community Extensions this is already done for you). I put this at the top of the script to get it out of the way. Normally I would put it right after any functions that I had setup in a script if I needed to setup an alias like this. It checks if you have Out-Clipboard, and if you don't it sets up the alias for Clip.exe to Out-Clipboard.

If(!(Get-Command Out-Clipboard -ErrorAction SilentlyContinue)){Set-Alias Out-Clipboard "$env:SYSTEMROOT\System32\clip.exe"}

现在，我们可以将内容通过管道传输到剪贴板，这对于接下来要执行的操作非常方便.我们将采用数组并将其转换为制表符分隔的CSV(无类型信息)，跳过第一个条目(标题行)，对HostName剩下的内容进行排序，然后将其通过管道传输到剪贴板.

Now we can pipe things to the clipboard, and that's real handy for what we want to do next. We are going to take our array and convert it to a tab delimited CSV (with no type info), skip the first entry (the header row), sort what's left by HostName, and pipe it to the clipboard.

$DNSResults | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard

现在，我们只需要将其粘贴到Excel中即可.为此，我们需要指定一个范围对象作为目标，因此我将其设置为目标，然后使用WorkSheet对象的Paste(Range，link)方法.不用担心链接部分，我们将为此使用$ false布尔值，因为我们不会将粘贴的数据链接到数据源以进行动态更新.看起来像这样:

Now we just have to paste that into Excel. To do that we need a range object to specify as the target, so I'll set that up, and then we use the WorkSheet object's Paste(Range,link) method. Don't worry about the link part, we are going to use the $false Boolean for that because we are not going to link the pasted data to a datasource for dynamic updating. This looks something like:

$e = $c.Range("A3:E$($DNSResults.Count+2)")
$c.Paste($e,$false)

然后，我们继续为您的结果设置颜色，以及其余的格式设置.

Then we go on to setting the color for your results, and the rest of your formatting.

所以，我最喜欢的叔叔总是告诉我长大了如果没有破裂，就不要修理它" .您的脚本完成了您想要的操作，那么，如果脚本没有损坏，为什么还要对其进行修改?所做的更改并不大，实际上是在加快速度并一次插入所有内容，而不是一次插入所有内容，但是最大的事情是现在我们可以在插入到Excel之前在PowerShell中进行过滤，这使我们回到了原来的状态.问题:

So, my favorite uncle always told me growing up "if it ain't broke, don't fix it". Your script did what you wanted, so why did I revamp it if it wasn't broken? The changes aren't huge, it's really about speeding things up and inserting everything at once instead of one at a time, but the biggest thing is that now we can filter in PowerShell before you insert into Excel, and that brings us to your original questions:

将空白单元格设置为黄色.
用关键字匹配行，删除所有其他行.

我们将在一秒钟之内走到空白，但是现在删除没有特定关键词的行很容易.不要删除它们，只是不要插入它们而已！设置要过滤的单词列表很容易，然后在我们导出到剪贴板时仅包含带有这些单词的记录.

We'll get to the blanks in a second, but deleting the rows that don't have your certain key words is easy now. Don't delete them, just don't insert them to start with! It's easy enough to setup a list of words to filter for, and then only include records with those words when we go to export to the clipboard.

$Keywords = "Google","Facebook","HappyTreeFriends"
$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"

第二行创建一个字符串，您可以对它进行RegEx匹配.它使用您的关键字，转义任何特殊字符，将它们用分隔符的管道连接起来，并用括号括起来.在这两行之后 $ Filter =(Google | Facebook | HappyTreeFriends).然后在要向剪贴板发送数据的行上，添加一个Where子句:

The second line creates a string that you can do a RegEx match against. It takes your keywords, escapes any special characters, joins them up with a pipe separating them, and encloses them in parenthesis. After those two lines $Filter = (Google|Facebook|HappyTreeFriends). Then on the line that you want to send data to the clipboard just add a Where clause:

$DNSResults | Where{$_ -match $filter} | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard

这样，只有具有您列入黑名单的单词之一的记录才会被插入到Excel中，而您不必担心返回和删除行.您想要要做的行将关键字突出显示为红色吗?只需以这种方式为它们上色(现在将它们设置为颜色索引15，灰色，只需将其更改为3，它们就会是红色)开始，因为它们应该是其中唯一的东西.

That way only records that have one of your blacklisted words will be inserted into Excel, and you don't have to worry about going back and deleting rows. You want rows that do have the key words to be highlighted red? Just color them that way (right now you have them set to color index 15, grey, just change that to 3 and they'll be red) to start with since they should be the only things in there.

空白单元格很难被发现，尤其是一旦您进行了一些格式化，并且单元格边界不那么明确时.在这种情况下，找到空单元格确实很容易.我们已经有$ e，它是一个范围对象，其中包括我们刚刚插入Excel的所有数据，甚至包括空白单元格.范围对象基本上是单元格的集合，每个单元格都有自己的属性，例如其位置(行，列)，格式，值等.您可以做的很简单:

Blank cells can be hard to spot, especially once you get some formatting going, and have less defined cell borders. Finding empty cells in this case is really easy though. We already have $e, which is a range object that includes all of the data that we just inserted into Excel, even the blank cells. A range object is basically a collection of cells, each having it's own properties like it's location (row,column), formatting, value, etc. What you can do is simple enough:

#If there is no Out-Clipboard, set it
If(!(Get-Command Out-Clipboard -ErrorAction SilentlyContinue)){Set-Alias Out-Clipboard "$env:SYSTEMROOT\System32\clip.exe"}

#Get current date
$Date = get-date -format yyyy-MM-dd
$Company = "company"
$Company2 = "company2"
#Define all files/Paths.
$Path = "C:\$Company2\BlockedIP"
md "$Path\HTML\$Date" -Force |Out-Null
$path2 = "$Path\HTML\$Date"
$PathWeb = "/HTML/$Date"

#Path = C:/$Company2/BlockedIP
#Path2 = C:/$Company2/BlockedIP/HTML/2014-07-09

#Define File's used or created in this script.
$File = "$Path\IP-$Date.txt"
$FileHtml = "$Path2\IP-$Date.htm"
$FileXML = "$Path\IP-$Date.xlsx"
$FileHTMLWeb = "$PathWeb\IP-$date.htm"

#File = C:/$Company2/BlockedIP/IP-2014-07-09.txt
#FileXML = C:/$Company2/BlockedIP/HTML/2014-07-09/IP-2014-07-09.htm
#FileHtml = C:/$Company2/BlockedIP/HTML/2014-07-09/IP-2014-07-09.xlsx
#FileHTMLWeb = PublicIP/HTML/2014-07-09/IP-2014-07-09.htm

$Keywords = "Google","thenetworkfactory"
$Filter = "($(($Keywords|%{[RegEx]::Escape($_)}) -join "|"))"

#Define error actions.
#$erroractionpreference = "SilentlyContinue"

#Get content from given IP list.
$colComputers = @(get-content $File | Sort -unique)
$SourceCount = $colComputers.Count
    write-output "$Count IP's detected."

#Get DNS Results
$Progress=1
$DNSResults = $colComputers | %{
 Write-Progress -Activity "Creating a usable 'Blocked IP' list ($Progress/$sourcecount)" -PercentComplete ($Progress/$sourceCount*100) -Status "Please stand by"
try {
    ($dnsresult = [System.Net.DNS]::GetHostEntry($_))|out-null
}
catch {
    $dnsresult = "Fail"
}
[PSCustomObject][Ordered]@{
    Source=$_.ToUpper()
    HostName=$dnsresult.HostName
    IPAddress=$dnsresult.AddressList[0].ToString()
}
$Progress++
}

$count = ($DNSResults|?{$_ -match $filter}).count

#Open Excel.
$a = New-Object -comobject Excel.Application

#Since we want this script to look like it's being used without excel I set it's visibility to false.
$a.visible = $True

#Disable excel confirmations.
$a.DisplayAlerts = $False

<# 
# set interactive to false so nothing from excel is shown.
$Excel.DisplayAlerts = $false
$Excel.ScreenUpdating = $false
$Excel.Visible = $false
$Excel.UserControl = $false
$Excel.Interactive = $false
#>

#Create sheets in Excel.
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Activate() | Out-Null

#Create a Title for the first worksheet and adjust the font
$c.Cells.Item(1,1)= "Blocked IP's $Date"
$c.Cells.Item(1,1).Font.ColorIndex = 55
$c.Cells.Item(1,1).Font.Color = 8210719
$range = $c.Range("a1","e1")
$range.Style = 'Title'
$range.Select()
$range.MergeCells = $true
$range.VerticalAlignment = -4108

$CounterRow = $Count+5
#Define subjects.
$c.Name = "Blocked IP's ($Date)"
$c.Cells.Item(2,1) = "Given IP"
$c.Cells.Item(2,2) = "Resolved DNS"
$c.Cells.Item(2,3) = "Returned IP"
$c.Cells.Item(2,5) = "$Company"
$c.Cells.Item($Count+5,1) = "Created by"
$link = "http://www.$Company"
$link2 = "https://www.linkedin.com/profile/view?id=#########"
$r = $c.Range("E2") 
[void]$c.Hyperlinks.Add($r, $link) 
$r = $c.Range("A$Counterrow") 
[void]$c.Hyperlinks.Add($r, $link)

#Define cell formatting from subjects.
$c.Range("A2:E2").Interior.ColorIndex = 6
$c.Range("A2:E2").font.size = 13
$c.Range("A2:E2").Font.ColorIndex = 1
$c.Range("A2:E2").Font.Bold = $True

#Define html code for Excel save to .htm.
$xlExcelHTML = 44

#Define the usedrange, excluding header and footer rows
$e = $c.Range("A3:E$(2+$Count)")

#Populate data into spreadsheet
$DNSResults | Where{$_ -match $filter} | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
#$c.Cells.Item(3,1).Select()
$c.Paste($e,$false)

ForEach($Cell in $e){
If([String]::IsNullOrWhitespace($Cell.value2)){$Cell.interior.colorindex=6}
}

#Define the usedrange for autofitting.
$d = $c.UsedRange

#Set background color for the IP list.
$E.interior.colorindex = 15

#Define borders here.
$xlOpenXMLWorkbook = 51
$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlRight = -4152
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2 
$selection = $c.range("A3:C$($DNSResults.Count+2)")
$selection.select() |out-null
$selection.HorizontalAlignment = $xlRight
$selection.VerticalAlignment = $xlBottom
$selection.WrapText = $false
$selection.Orientation = 0
$selection.AddIndent = $false
$selection.IndentLevel = 0
$selection.ShrinkToFit = $false
$selection.ReadingOrder = $xlContext
$selection.MergeCells = $false
$selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin



#Make everything fit in it's cell.
$d.EntireColumn.AutoFit() | Out-Null

#Save the file as .xlsx on every placed IP to ensure the file is not lost due to any reason.
$b.SaveAs("$FileXML")

#Clear screen on every checked IP to remove the 'True' statement.
#cls
#


#Save final result as a .htm file
$b.SaveAs("$FileHTML",$xlExcelHTML)

#Close and quit Excel.
$b.Close()
get-process *Excel* | Stop-Process -force

#Move .txt file to the correct HTML folder.
move-item $file $path2 -Force

#Move .xlsx file to the correct HTML folder.
move-item $filexml $path2 -Force

#Declare XLSX file for mail
$MailXML = "$path2\IP-$Date.xlsx"

#Clear screen, again. (Let's keep things tidy.)
#cls

#Variables for public IP
# I am defining website url in a variable
$url = "http://checkip.dyndns.com" 
# Creating a new .Net Object names a System.Net.Webclient
$webclient = New-Object System.Net.WebClient
# In this new webdownlader object we are telling $webclient to download the
# url $url 
$IpPublic = $webclient.DownloadString($url)
# Just a simple text manuplation to get the ipadress form downloaded URL
# If you want to know what it contain try to see the variable $IpPublic
$IpPublic2 = $IpPublic.ToString()
$ipPublic3 = $IpPublic2.Split(" ")
$ipPublic4 = $ipPublic3[5]
$ipPublic5 = $ipPublic4.replace("</body>","")
$FinalIPAddress = $ipPublic5.replace("</html>","")
$ipLocal = (Get-WmiObject -class win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"').ipaddress[0]

#Variables e-mail.
$From = "Blocked IP <r.van.tour@$Company>"
$To = "IT Dept <r.van.tour@$Company>"
$CC = "Someone <$Company2@$Company"
$Subject = "Blocked IPs for $date ($Count Total)"
#The href should point to the htm file in your iis/apache folder.
$WebLink = $FinalIPAddress+$FileHtmlWeb
$here = "<a href='http://$Weblink'><b>Here</b></a>"
#Define the body of your e-mail, in this case it displays a message and shows the server it is send from with it's local IP.
#A link to the .htm file, how many IP's were blocked and the date of the message.
$Body = "<!DOCTYPE html><html><head> <title>Blocked IP's $Date</title></head><header><h1>Blocked IP</h1><p><time pubdate datetime='$date'></time></p></header><br>" 
$body += "<body>Dear <font color=black>$to</font>,<br><br>"
$body += "This is an automated message generated by server: <font color=red><b>$env:COMPUTERNAME, $IPLocal.</b></font><br><br>"
$body += "Click <font color=red><b>$here</b></font> to see the Blocked IP report for $date containing $count IP's.<br>"
$body += "Or see the attachment to open it in Excel.<br></body></html>"


#Clear screen, again. (Let's keep things tidy.)
#cls

#Send output as e-mail.
$SMTPServer = "smtp.gmail.com"
$SMTPPort = "587"
$Username = "##@gmail.com"
$Password = "##"
$message = New-Object System.Net.Mail.MailMessage
$message.IsBodyHTML = $true
$message.ReplyTo = $From
$message.Sender = $From
$message.subject = $subject
$message.body = $body
$message.to.add($to)
$message.from = $From
$message.attachments.add($MailXML)
$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort);
$smtp.EnableSSL = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
$smtp.send($message)

#Create a function to relase Com object at end of script.
function Release-Ref ($ref) { 
([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
[System.__ComObject]$ref) -gt 0) 
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 
                        }

#Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$a) | Out-Null

#Clear screen for the final time. (Let's keep things tidy.)
#cls

#Exit powershell 
exit

编辑:我发现您的脚本存在问题.您没有更新要复制到剪贴板的行，只是在脚本已粘贴到Excel之后在脚本的后面添加了几行，因此您添加的代码实际上无济于事.我已经对您的代码进行了一些更新(以更好地处理记录数并正确过滤)，并更新了上面的脚本.

I found the issue with your script. You didn't update the line where you are copying to the clipboard, you just added in lines later in the script, after the script already pasted to Excel, so the code you added effectively did nothing. I've made a couple of updates to your code (to handle record counts better, and filter correctly), and updated the above script.

Edit2 :这篇文章越来越长.好的，为了包括所有条目，我已经做了一些修改.首先，在ForEach循环中查找DNS结果，我修改了对象创建行以在尝试填充之前检查它们是否存在，以免在没有用于DNS查找的一个或两个主机名和IP地址的事物上引发错误./p>

Man this post is getting long. Ok, so to include all entries I've modified a few things. First the ForEach loop that looks up the DNS results, I modified the object creation lines to check if they exist before trying to populate so it stops throwing errors on things that don't have one or both HostName and IP Addresses for DNS Lookups.

[PSCustomObject][Ordered]@{
    Source=$_.ToUpper()
    HostName=$(if(!([string]::IsNullOrEmpty($dnsresult.HostName))){$dnsresult.HostName})
    IPAddress=$(if(!([string]::IsNullOrEmpty($dnsresult.AddressList))){$dnsresult.AddressList[0].ToString()})
}

然后，我将结果分为三类:主机名匹配关键字，主机名不匹配关键字，没有主机名(还要更改$ count wince，我们将包括所有内容).

Then I split the results into three catagories: With Hostname matching a keyword, with Hostname not matching a keyword, and no Hostname (also change $count wince we are including everything).

$DNSWithKeyword = $DNSResults | ?{$_.HostName -match $Filter}
$DNSNoKeyword = $DNSResults | ?{!($_.HostName -match $Filter) -and !([string]::IsNullOrEmpty($_.HostName))}
$DNSLookupFailed = $DNSResults | ?{([string]::IsNullOrEmpty($_.HostName))}
#$count = ($DNSResults|?{$_ -match $filter}).count
$count = $SourceCount

然后，在定义使用范围的方式中，我为每个类别添加了一个，而不是仅执行一个复制/粘贴，而是执行了三个，因此首先有那些匹配的关键字，按主机名排序，然后没有关键字按主机名排序，并且然后那些没有主机名的主机.然后我按部分给每个单元上色，然后又回过头来，将空白单元格重新着色为黄色.

Then down a ways where used range is defined I added one for each category, and instead of just one Copy/Paste I do three so there are those matching keywords first, sorted by hostname, then no keyword sorted by hostname, and then those that have no hostname. Then I colored each by section, and went back after and colored blank cells yellow again.

#Define the usedrange, excluding header and footer rows
$KeyRange = $c.Range("A3:E$(2+$DNSWithKeyword.Count)")
$NoKeyRange = $c.Range("A$(2+$DNSWithKeyword.Count+1):E$(2+$DNSWithKeyword.Count+$DNSNoKeyword.Count)")
$NoDNSRange = $c.Range("A$(2+$DNSWithKeyword.Count+$DNSNoKeyword.Count+1):E$(2+$DNSWithKeyword.Count+$DNSNoKeyword.Count+$DNSLookupFailed.Count)")
$e = $c.Range("A3:E$(2+$Count)")

#Populate data into spreadsheet
$DNSWithKeyword | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
$c.Paste($KeyRange,$false)
$DNSNoKeyword | Sort HostName | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
$c.Paste($NoKeyRange,$false)
$DNSLookupFailed | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Out-Clipboard
$c.Paste($NoDNSRange,$false)

#Define the usedrange for autofitting.
$d = $c.UsedRange

#Set background color for the IP list.
$KeyRange.interior.colorindex = 3
$NoKeyRange.interior.colorindex = 15
$NoDNSRange.interior.colorindex = 14

ForEach($Cell in $e){
If([String]::IsNullOrWhitespace($Cell.value2)){$Cell.interior.colorindex=6}
}

这篇关于Powershell，Excel在单元格中查找字符串，颜色行，删除其他的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

Powershell，Excel在单元格中查找字符串，颜色行，删除其他 [英] Powershell, Excel Finding string in cell, Color row, delete others

问题描述

推荐答案

相关文章

其他开发最新文章

热门教程

热门工具

登录关闭

Powershell，Excel在单元格中查找字符串，颜色行，删除其他 [英] Powershell, Excel Finding string in cell, Color row, delete others

问题描述

推荐答案

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

登录关闭