PowerShell 技能连载 - 创建 Excel 报表(第三部分——独立操作工作簿)

有些时候您可能需要创建非表格形式的,个性化的报表。

对于这种情况, PowerShell 可以连接到 Excel 的对象模型。通过这种方法,您可以操作独立的单元格,读写它们的内容,甚至对它们进行格式化。这给你最大的灵活度。然而,它的缺点是需要大量的编码,因为您需要人工操作每一个单元格。并且,通过 .NET 操作 COM 对象相对比较慢。

以下是起步的代码。它展示了如何连接到 Excel,存取独立的单元格,并应用格式设置:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#requires -Version 2.0
Add-Type -AssemblyName System.Drawing

# accessing excel via COM
$excel = New-Object -ComObject Excel.Application
# make it visible (for debugging only, can be set to $false later in production)
$excel.Visible = $true

# add workbook
$workbook = $excel.Workbooks.Add()

# access workbook cells
$workbook.ActiveSheet.Cells.Item(1,1) = 'Hey!'

# formatting cell
$workbook.ActiveSheet.Cells.Item(1,1).Font.Size = 20

$r = 200
$g = 100
$b = 255
[System.Drawing.ColorTranslator]::ToOle([System.Drawing.Color]::FromArgb(255,$r,$g,$b))
$workbook.ActiveSheet.Cells.Item(1,1).Font.Color = $r + ($g * 256) + ($b * 256 * 256)

# saving workbook to file
$Path = "$env:temp\excel.xlsx"
$workbook.SaveAs($Path)

PowerShell 技能连载 - 创建 Excel 报表(第二部分——彩色)

当从 CSV 中导入数据到 Excel 时,您无法指定格式,包括字体和颜色等。从 HTML 数据中导入 Excel 则可以包含格式。以下是一个演示创建一个带格式的彩色 Excel 报告是多么容易的例子,假设报表中包含一张表格:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#requires -Version 2.0
$html = & {
'<table>'
'<tr><th>Name</th><th>Status</th></tr>'
Get-Service |
ForEach-Object {
if ($_.Status -eq 'Running')
{
$color = 'green'
}
else
{
$color = 'red'
}

'<tr><td>{0}</td><td bgcolor="{2}">{1}</td></tr>' -f $_.Name, $_.Status, $color

}
'</table>'
}

$PathHTML = "$env:temp\report.htm"
$html | Set-Content $PathHTML -Encoding UTF8
# open as HTML
Invoke-Item -Path $PathHTML
# open as Excel report
Start-Process -FilePath excel -ArgumentList """$PathHTML"""

PowerShell 技能连载 - 创建 Excel 报表(第一部分——黑白)

用 PowerShell 创建一个最简单的 Excel 报表只需要几行代码:将结果导出为 CSV 文件,然后将它作为参数启动 Excel:

1
2
3
4
5
6
7
8
9
#requires -Version 2.0

$timestamp = Get-Date -Format 'yyyy-MM-dd HH-mm-ss'
$Path = "$env:temp\Excel Report $timestamp.csv"

Get-Service |
Export-Csv -Path $Path -Encoding UTF8 -UseCulture -NoTypeInformation

Start-Process -FilePath excel -ArgumentList """$Path"""

有一些需要注意的事项:

  • Excel 打开一个文件时会将它锁定。所以请在文件名之前加上时间戳或其它唯一的识别名。否则,当您多次运行脚本而没有关闭之前的文档会遇到错误。
  • 当导出数据到 CSV 时,请使用 UTF-8 编码,来保留特殊字符。
  • 同样地,确保 CSV 和 Excel 使用相同的分隔符。只需要使用 -UseCulture 来使用注册表中设置的分隔符即可。
  • 当启动 Excel 时,请确保将路径置于双引号中,否则,如果路径中包含空格,Excel 将找不到该文件。

PowerShell 技能连载 - 创建 HTML 报表(第五部分 - 应用样式和设计)

在前一个技能中我们开始将 PowerShell 的结果转换为 HTML 报告。报告内容目前一切正常。要使人加深印象,结果需要做一些设计改进。以下是我们之前的成果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$preContent = "<h1>$env:computername</h1>
<h3>Error Events from $startText until $endText</h3>
"
$postContent = "<p><i>(C) 2017 SysAdmin $today</i></p>"

$replacementStrings = @{
Name = 'ReplacementStrings'
Expression = { $_.ReplacementStrings -join ',' }
}

$timeGenerated = @{
Name = 'Time'
Expression = { $_.TimeGenerated }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
Select-Object -Property EventId, Message, Source, InstanceId, $TimeGenerated, $ReplacementStrings, UserName |
ConvertTo-Html -PreContent $preContent -PostContent $postContent |
Set-Content -Path $Path

Invoke-Item -Path $Path

要改进它的样式,可以对报告应用 HTML CSS 样式(层叠样式表)。CSS 决定了报告中所有 HTML 元素的样式细节。您可以在 -Head 参数中插入一个 CSS 样式表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$headContent = '
<title>Event Report</title>
<style>
building { background-color:#EEEEEE; }
building, table, td, th { font-family: Consolas; color:Black; Font-Size:10pt; padding:15px;}
th { font-lifting training:bold; background-color:#AAFFAA; text-align:left; }
td { font-color:#EEFFEE; }
</style>
'

$preContent = "<h1>$env:computername</h1>
<h3>Error Events from $startText until $endText</h3>
"
$postContent = "<p><i>(C) 2017 SysAdmin $today</i></p>"

$replacementStrings = @{
Name = 'ReplacementStrings'
Expression = { $_.ReplacementStrings -join ',' }
}

$timeGenerated = @{
Name = 'Time'
Expression = { $_.TimeGenerated }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
Select-Object -Property EventId, Message, Source, InstanceId, $TimeGenerated, $ReplacementStrings, UserName |
ConvertTo-Html -PreContent $preContent -PostContent $postContent -Head $headContent |
Set-Content -Path $Path

Invoke-Item -Path $Path

应用一个样式表之后,报告会一下子变得清新现代起来。

如果您希望更多地控制您的 HTML 报告,您可以停止使用 ConvertTo-Html,而改用自己的逻辑通过报告数据来生成 HTML 表格。不过这超出了我们快速技能的范畴。

PowerShell 技能连载 - 创建 HTML 报表(第四部分 - 重命名列)

在之前的技能中我们开始将 PowerShell 结果转为 HTML 报告。现在报告的结果接近完成了。我们只需要对某些列标题进行润色和重命名即可。这是上一次的脚本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$preContent = "<h1>$env:computername</h1>
<h3>Error Events from $startText until $endText</h3>
"
$postContent = "<p><i>(C) 2017 SysAdmin $today</i></p>"

$replacementStrings = @{
Name = 'ReplacementStrings'
Expression = { $_.ReplacementStrings -join ',' }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName |
ConvertTo-Html -PreContent $preContent -PostContent $postContent |
Set-Content -Path $Path

Invoke-Item -Path $Path

要重命名列标题,请使用之前同样的策略将非字符串内容转换为字符串内容:使用计算属性。所以如果您想将 TimeGenerated 重命名为 Time,那么可以这样做:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$preContent = "<h1>$env:computername</h1>
<h3>Error Events from $startText until $endText</h3>
"
$postContent = "<p><i>(C) 2017 SysAdmin $today</i></p>"

$replacementStrings = @{
Name = 'ReplacementStrings'
Expression = { $_.ReplacementStrings -join ',' }
}

$timeGenerated = @{
# specify NEW name for column (property)
Name = 'Time'
# use existing value
Expression = { $_.TimeGenerated }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
Select-Object -Property EventId, Message, Source, InstanceId, $TimeGenerated, $ReplacementStrings, UserName |
ConvertTo-Html -PreContent $preContent -PostContent $postContent |
Set-Content -Path $Path

Invoke-Item -Path $Path

PowerShell 技能连载 - 创建 HTML 报表(第三部分 - 增加头部和尾部)

在前一个技能中我们开始将 PowerShell 的结果转换为 HTML 报告。现在,这份报告需要一些头部和尾部。以下是我们上一个版本的代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$startDate = (Get-Date).AddHours(-48)

$replacementStrings = @{
Name = 'ReplacementStrings'
Expression = { $_.ReplacementStrings -join ',' }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName |
ConvertTo-Html |
Set-Content -Path $Path

Invoke-Item -Path $Path

要在数据前后加入内容,请使用 -PreContent-PostContent 参数。比如在头部加入机器名,在尾部加入版权信息,请使用以下代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$today = Get-Date
$startDate = $today.AddHours(-48)
$startText = $startDate.ToString('MMMM dd yyyy, HH:ss')
$endText = $today.ToString('MMMM dd yyyy, HH:ss')

$preContent = "<h1>$env:computername</h1>
<h3>Error Events from $startText until $endText</h3>
"
$postContent = "<p><i>(C) 2017 SysAdmin $today</i></p>"

$replacementStrings = @{
Name = 'ReplacementStrings'
Expression = { $_.ReplacementStrings -join ',' }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName |
ConvertTo-Html -PreContent $preContent -PostContent $postContent |
Set-Content -Path $Path

Invoke-Item -Path $Path

PowerShell 技能连载 - 创建 HTML 报表(第二部分 - 修复非字符串内容)

在前一个技能中我们开始使用 PowerShell 来将结果转换为 HTML 报告。目前,我们已经生成了报告,但报告的界面看起来很丑。我们从这里开始:

1
2
3
4
5
6
7
8
9
10
11
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$startDate = (Get-Date).AddHours(-48)

Get-EventLog -LogName System -EntryType Error -After $startDate |
Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, ReplacementStrings, UserName |
ConvertTo-Html |
Set-Content -Path $Path

Invoke-Item -Path $Path

当您运行这段代码时,报告显示有一些属性包含非字符串内容。请看 “ReplacementStrings” 列:报告中含有 string[],也就是字符串数组类型,而不是真实数据。

要修复这个问题,请使用计算属性,并且将内容转换为可读的文本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$startDate = (Get-Date).AddHours(-48)

# make sure the property gets piped to Out-String to turn its
# content into readable text that can be displayed in the report
$replacementStrings = @{
Name = 'ReplacementStrings'
Expression = { ($_.ReplacementStrings | Out-String).Trim() }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
# select the properties to be included in your report
Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName |
ConvertTo-Html |
Set-Content -Path $Path

Invoke-Item -Path $Path

如您所见,该属性现在能正常显示它的内容了。

要如何将属性内容转换成可读的文本依赖于您的选择。如果将属性通过管道传给 Out-String,将把转换工作留给 PowerShell 自动完成。如果您希望更精细的控制,而且某个属性包含一个数组,您也可以使用 -join 操作符来连接数组元素。通过这种方式,您可以选择使用哪种分隔符来分割数组元素。以下例子使用逗号分隔:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$startDate = (Get-Date).AddHours(-48)

# make sure the property gets piped to Out-String to turn its
# content into readable text that can be displayed in the report
$replacementStrings = @{
Name = 'ReplacementStrings'
Expression = { $_.ReplacementStrings -join ',' }
}

Get-EventLog -LogName System -EntryType Error -After $startDate |
# select the properties to be included in your report
Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, $ReplacementStrings, UserName |
ConvertTo-Html |
Set-Content -Path $Path

Invoke-Item -Path $Path

PowerShell 技能连载 - 创建 HTML 报表(第一部分 - 创建 HTML)

要将 PowerShell 的处理结果输出为 HTML 报表,只需要将结果用管道传给 ConvertTo-Html,然后将结果保存到文件。所以它最基本的使用形式类似如下。它创建一个包含过去 48 小时发生的所有事件系统错误的报表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#requires -Version 2.0

# store report here
$Path = "$env:temp\eventreport.htm"
# set the start date
$startDate = (Get-Date).AddHours(-48)

# get data and convert it to HTML
Get-EventLog -LogName System -EntryType Error -After $startDate |
ConvertTo-Html |
Set-Content -Path $Path

# open the file with associated program
Invoke-Item -Path $Path

不过,输出的报告可能有点丑,因为包含了许多无用的信息。所以美化的第一步是选择报告中需要的属性。只需要在代码中加入 Select-Object

1
2
3
4
5
6
7
8
9
10
11
12
#requires -Version 2.0

$Path = "$env:temp\eventreport.htm"
$startDate = (Get-Date).AddHours(-48)

Get-EventLog -LogName System -EntryType Error -After $startDate |
# select the properties to be included in your report
Select-Object -Property EventId, Message, Source, InstanceId, TimeGenerated, ReplacementStrings, UserName |
ConvertTo-Html |
Set-Content -Path $Path

Invoke-Item -Path $Path

PowerShell 技能连载 - 在 Windows 10 中控制控制台的透明度

在 Windows 10 中,当您打开一个 PowerShell 控制体态,只需要按住 CTRL + SHIFT 键,然后滚动鼠标滚轮,就可以改变控制台的背景。

要实现这样的功能,请打开控制台的属性窗口,“使用旧版控制台”选项必须勾选。您可以单击控制台标题栏左上角的应用程序图标,然后选择“属性”来打开控制台属性。

PowerShell 技能连载 - 查找已安装和缺失的更新(第四部分)

有些时候,Microsoft.Update.Session 对象可以用来检查一台机器上是否安装了某个更新。有些作者用这种方法查询更新的标题字符串:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#requires -Version 3.0

function Get-UpdateInstalled([Parameter(Mandatory)]$KBNumber)
{
$Session = New-Object -ComObject "Microsoft.Update.Session"
$Searcher = $Session.CreateUpdateSearcher()
$historyCount = $Searcher.GetTotalHistoryCount()

$status = @{
Name="Operation"
Expression= {
switch($_.operation)
{
1 {"Installation"}
2 {"Uninstallation"}
3 {"Other"}
}
}
}

$Searcher.QueryHistory(0, $historyCount) |
Where-Object {$_.Title -like "*KB$KBNumber*" } |
Select-Object -Property Title, $status, Date
}

function Test-UpdateInstalled([Parameter(Mandatory)]$KBNumber)
{
$update = Get-UpdateInstalled -KBNumber $KBNumber |
Where-Object Status -eq Installation |
Select-Object -First 1

return $update -ne $null
}

Test-UpdateInstalled -KBNumber 2267602
Get-UpdateInstalled -KBNumber 2267602 | Out-GridView

请注意这个方法不仅更快,而且由于它将任务分成两个函数,所以您还可以读出所有已安装的更新标题:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
PS> Get-UpdateInstalled -KBNumber 2267602

Title Operation Date
----- --------- ----
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.348.0) Installation 28.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.281.0) Installation 27.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.237.0) Installation 26.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.191.0) Installation 25.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.139.0) Installation 24.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.95.0) Installation 22.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.93.0) Installation 22.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.28.0) Installation 21.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.249.13.0) Installation 20.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.247.1068.0) Installation 19.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.247.1010.0) Installation 18.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.247.969.0) Installation 17.07.20...
Definitionsupdate für Windows Defender – KB2267602 (Definition 1.247.966.0) Installation 17.07.20...