Excel 列号和数字互相转换

Excel 的列号是采用“A”、“B”……“Z”、“AA”、“AB”……的方式编号。但是我们在自动化操作中,往往希望用数字作为列号。我们可以用 PowerShell 来实现 Excel 的列号和数字之间的互相转换。

需求归纳

Excel 列号 -> 数字

A   1
AB  28
AC  29

数字 -> Excel 列号

1   A
2   B
24  Y
26  Z
27  AA
28  AB

算法分析

  • Excel 列号 -> 数字
    • 用 ASCII 编码对输入的字符串解码,得到一个数字型数组。
    • 用 26 进制对数组进行处理(逐位 *= 26,然后累加)。
  • 数字 -> Excel 列号
    • 用 26 进制对数字进行处理(不断地 /= 26,取余数),得到数字型数组。
    • 将数字型数组顺序颠倒。
    • 用 ASCII 编码对数字型数组编码,得到 Excel 风格的列号。

源代码

转换函数:

function ConvertFrom-ExcelColumn ($column) {
    $result = 0
    $ids = [System.Text.Encoding]::ASCII.GetBytes($column) | foreach {
        $result = $result * 26 + $_ - 64
    }
    return $result
}

function ConvertTo-ExcelColumn ($number) {
    $ids = while ($number -gt 0) {
        ($number - 1) % 26 + 1 + 64
        $number = [math]::Truncate(($number - 1) / 26)
    }

    [array]::Reverse($ids)
    return [System.Text.Encoding]::ASCII.GetString([array]$ids)
}

测试代码:

echo "A`t$(ConvertFrom-ExcelColumn A)"
echo "AB`t$(ConvertFrom-ExcelColumn AB)"
echo "AC`t$(ConvertFrom-ExcelColumn AC)"

echo ''

@(1..2) + @(25..28) | foreach {
    echo "$_`t$(ConvertTo-ExcelColumn $_)"
}

执行结果:

A   1
AB  28
AC  29

1   A
2   B
25  Y
26  Z
27  AA
28  AB

您也可以在这里下载完整的脚本。

在PowerShell中操作Word - 使用格式化样式

在这个系列的上一步中,我们演示了用Windows PowerShell创建Microsoft Word文档的基本步骤。如果您试用了我的示例脚本,您会注意到文档格式化方面略有不足。幸运的是,我们有一些简洁的办法来改进您文档的质量,我将会在这篇文章中向您演示这个过程。我们将用第一部分的脚本作为起点。

关键之处在于 Selection 对象。

PS C:\>$word=new-object -ComObject "Word.Application"
PS C:\>$doc=$word.documents.Add()
PS C:\> $selection=$word.Selection

您可以更改 Selection 对象的一个重要元素是 Font。您可以轻松地修改字体大小和颜色,以及使用哪种字体。我将把日期和时间的字体改为绿色。

PS C:\> $selection.Font.Color="wdColorGreen"
PS C:\> $selection.TypeText((Get-Date))

在VBScript的年代中,我们需要定义 wdColorGreen 的值并将它赋给一个常量。但是在PowerShell中我们可以轻松地以字符串的形式插入这个常量。您一定很好奇有哪些颜色可以使用?问问PowerShell吧:

PS C:\> [enum]::GetNames([microsoft.office.interop.word.wdcolor])

您需要把字体颜色改回来,除非您需要把整个文档设为这个颜色。

PS C:\ >$selection.font.Color="wdColorAutomatic"
PS C:\> $selection.TypeParagraph()

在我原先的脚本中我插入了一个标题。现在我们把它变成大一点的字体。我将用我上次使用的WMI代码。

$selection.Font.Size=12
$selection.TypeText("Operating System Information for $($os.CSName)")

回顾一下前面一篇文章,有一个问题是PowerShell输出到Word早期是用等宽字体而后来用的是非等宽字体。解决方法是从PowerShell中插入结果之前指定一个合适的字体。

PS C:\> $selection.Font.Size=10
PS C:\> $selection.Font.Name="Consolas"
PS C:\> $selection.TypeText(($os | Select -Property $props | Out-String))

还要做的最后一件事是添加一段格式化的文本,说明报告的创建者。我希望采用Word的斜体格式来呈现。

PS C:\> $selection.Font.size=8
PS C:\> $selection.Font.Name="Calibri"
PS C:\> $selection.Font.Italic=$True
PS C:\> $by="Report created by $env:userdomain\$env:username"
PS C:\> $selection.TypeText($by)

我相信您一定也掌握了如何使文本变成粗体。

除了指定字体之外,您还可以采用Word内置的样式。

$selection.Style="Title"
$selection.TypeText("Operating System Report")
$selection.TypeParagraph()

您可以用PowerShell查询 Document 对象,看看有哪些样式可以用。

$doc.Styles | select NameLocal

大多数这些样式只能应用在文本的第一行,不过您也可以自己做实验调整一下。

通过这些步骤您可以简洁地通过您的PowerShell脚本创建一个漂亮的Word文档。请下载版本修订过的脚本,New-WordDoc2,并且自己做一下实验。

用PowerShell操作Office系列文章

在PowerShell中操作Word - 生成文档

我假设许多读者用PowerShell生成服务器、事件以及Windows网络上各种数据的报表。您也许创建过纯文本文件,甚至漂亮的HTML报表。但是您也可以用上Microsoft Word,当然,前提是您已经安装了Word。在这个系列的第二部分,我将会向您演示如何用PowerShell为Word文档套用样式

创建Word应用程序对象

PowerShell可以通过COM接口控制Word应用程序。有趣的地方是,虽然您可以交互式地做所有的操作,但我希望您最终能够用脚本操作一切。我们从创建一个Word程序对象开始。

PS C:\> $word=new-object -ComObject "Word.Application"

如果您好奇的话,可以将这个对象输出到 Get-Member 命令。我们将创建一系列对象,您可以将每一个对象通过管道输出到 Get-Member 来探索它们。

下一步,我们创建一个文档对象。

PS C:\> $doc=$word.documents.Add()

现在,Word程序已经启动,并且创建了一个新文档,但是您在屏幕上看不到任何东西。通常这是正常的,因为我们需要它在后台运行。但是如果您想查看所创建的文档,您需要将应用程序的 Visible 属性设置为 True

PS C:\> $word.Visible=$True

在我们插入文本之前,我们需要获取焦点。创建一个 Selection 对象可以帮我们做一些诸如设置字体大小和颜色等操作,我们将在第二部分介绍这些操作。

PS C:\> $selection=$word.Selection

用PowerShell在文档中插入文本

现在光标在文档的顶部,现在可以开始插入文本了。我们将用 Selection 对象的 TypeText() 方法插入当前的日期和时间。

PS C:\> $selection.TypeText((Get-Date))

如果我们继续插入文本,那么文本将会紧挨在日期的后面。现在我们用 TypeParagraph() 方法插入一个回车符。

PS C:\> $selection.TypeParagraph()

让我们继续插入一些文本。我将用WMI获取本地计算机的操作系统信息。

PS C:\> $os=Get-WmiObject -class win32_OperatingSystem
PS C:\> $selection.TypeText("Operating System Information for $($os.CSName)")

由于我希望写入所有的非系统属性,所以我将快速递创建一个数组用来保存所有的属性名。

PS C:\> $os.properties | select Name | foreach -begin {$props=@()} -proc {$props+="$($_.name)"}

现在我可以从 $os 获取所有的属性并插入Word文档。很重要的一点是 TypeText() 的值是字符串型的,所以我需要将内联的PowerShell表达式通过管道输出到 Out-String

PS C:\> $selection.TypeText(($os | Select -Property $props | Out-String))

如果需要的话,还可以继续插入文字和图片。当完成操作以后,我将保存并关闭文档。

PS C:\> $doc.SaveAs([ref]"c:\work\osreport.docx")
PS C:\> $doc.Close()

请确认使用 [ref] 为文件路径转换数据类型。假设我不再创建新的文档,那么剩下的就是关闭Word应用程序。

PS C:\> $word.quit()

这些就是要做的所有事情。最终生成的Word文档是可用的,虽然可能不太漂亮。在我的例子中发现一个问题:Word用的事非等宽字体,而PowerShell的输出格式假设用的是等宽字体。(译者注:可能会造成输出的结果对不整齐)。在第二部分,我将向您演示如何解决这些问题。同时,欢迎下载示例脚本 New-WordDoc.ps1

用PowerShell操作Office系列文章

在PowerShell中操作Excel - 读取数据

欢迎回到“在PowerShell中操作Excel”三部曲系列文章。在这一系列的前两部分,我们学习了如何将数据写入Excel并且创建“富”报表,以及额外的格式化选项等Microsoft Excel高级用法。

对于IT专家来说,这个故事的另一半是如何从一个Excel文档中读取数据。它的挑战性在于您必须事先知道工作表的结构。我们可以搜索数据,但那是更复杂的情况。我假设您已有一个用过且了其解结构的Excel文档。这样,用PowerShell读取数据就不那么复杂。

读取数据

像我们在本系列文章的前两部分那样,第一步是创建一个Excel应用程序对象。

$xl=New-Object -ComObject "Excel.Application"

我将在我的脚本中使用这个Excel文件。

Excel数据

用工作簿对象的 Open() 方法打开文件。

$wb=$xl.Workbooks.Open($file)
$ws=$wb.ActiveSheet

$ws对象是我们对数据最重要的的引用点。我需要用的数据从A2单元格开始。在我的测试环境中,我也许知道我需要处理多少行,但是既然我知道从哪儿开始,我可以用一个Do循环来读取每一行,获取数据,进行进一步操作。

$Row=2

do {
  $data=$ws.Range("A$Row").Text
...

通过使用Range属性,我可以获取A2单元格。Text属性是该单元格的值。我的示例脚本将要从第一列获取计算机名,获取一些WMI信息,然后向管道写入一个和电子表格的其它部分数据有关的自定义数据。

当您处理Excel数据的时候,我建议您进行一系列校验。假设单元格里有一个数据,我假设它是一个机器名,那么我会试着ping一下它。

if ($data) {
    Write-Verbose "Querying $data"
      $ping=Test-Connection -ComputerName $data -Quiet

如果ping通了,我将会使用WMI来获取操作系统名称,否则我会设置$OS变量为$Null。

if ($Ping) {
        $OS=(Get-WmiObject -Class Win32_OperatingSystem -Property Caption -computer $data).Caption
      }
      else {
        $OS=$Null

最后,对于每台计算机,我将用 New-Object cmdlet创建一个自定义对象。

New-Object -TypeName PSObject -Property @{
        Computername=$Data.ToUpper()
        OS=$OS
        Ping=$Ping
        Location=$ws.Range("B$Row").Text
        AssetAge=((Get-Date)-($ws.Range("D$Row").Text -as [datetime])).TotalDays -as [int]
      }

请注意我设置的其它属性值,比如说Location,是位于B2单元格,至少对于这台计算机而言。请注意您从它的Text属性获取到的只是文本。但您还可以将它们转换为各种数据类型,就像我对AssetAge属性的处理那样。我从D2单元格读取文本,并把它转换为一个 DateTime 对象,于是我可以将它和当前时间做减法,得到一个 TimeSpan 对象。该对象有一个 TotalDays 属性。

loop循环的最后一步是使行计数器自增1。

$Row++
} While ($data)

下一次进入lopp循环的时候,脚本将会处理第3行的数据。直到PowerShell遇到一个空行。在最后,我将关闭文件并且退出。

$xl.displayAlerts=$False
$wb.Close()
$xl.Application.Quit()

我的脚本运行以后生成一下输出结果:

PS C:\scripts> .\Demo-ReadExcel.ps1

AssetAge     : 687
Ping         : True
Computername : SERENITY
Location     : R1-1
OS           : Microsoft Windows 7 Ultimate

AssetAge     : 293
Ping         : True
Computername : QUARK
Location     : R1-4
OS           : Microsoft Windows 7 Professional

AssetAge     : 293
Ping         : False
Computername : SERVER01
Location     : R3-2
OS           :

AssetAge     : 2005
Ping         : True
Computername : JDHIT-DC01
Location     : R2-1
OS           : Microsoft(R) Windows(R) Server 2003, Enterprise Edition

我在PowerShell中用数的代码就可以实现从Excel电子表格中读取数据并且在我的程序中使用它。如果需要的话,我可以结合前面文章中的技术,在读取的同时更新电子表格的内容!

结论

您可以下载我的示例脚本并且自己做一下实验。记住,当使用PowerShell读取Excel文件的时候,您需要事先了解文档的结构,并且做好错误处理和数据有效性验证。我并不推荐初学者用PowerShell操作Excel,但具有一些经验并投入一些耐心以后,您可以得到很丰厚的回报。

用PowerShell操作Office系列文章

在PowerShell中操作Excel - 创建一个简单的报表

除了文本文件之外,Windows系统管理员最常用的工具是Microsoft Excel。我观察论坛中人们的讨论发现,有一大堆关于Excel电子表格讨论。许多人想要读写Excel的方法。通常,我告诉人们如果他们使用PowerShell,那么可以简单地将结果导出到CSV文件,然后用Excel打开,但是您很有可能需要一个真正的Excel文件。

我着手准备写几个关于如何在PowerShell中操作Excel的专题。今天我们将要通过Microsoft Excel和Windows PowerShell创建一个简单的报表。在第二部分中,我将演示如何创建一个更复杂的Excel文档。然后在第三部分中,我将向您演示如何从Excel文件中读取数据。

创建一个简单的报表

Microsoft Excel包含一个非常复杂的COM对象模型,我们可以在Windows Powershell中和它交互。让我们从头开始创建一个Excel应用程序的对象。

PS C:\> $xl=New-Object -ComObject "Excel.Application"

执行完这一步以后,Excel已经开始在后台运行,虽然看不见可交互窗口。

PS C:\> get-process excel

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    203      23    16392      24340   267     0.28   1280 EXCEL

下一步,我们将要创建一个工作簿对象。

PS C:\> $wb=$xl.Workbooks.Add()

下一步,我们将要创建一个工作表对象。

PS C:\> $ws=$wb.ActiveSheet

您可以将任何一个对象通过管道输出到 Get-Member 来学习它们。下一步,我们将使这个应用程序可见。

PS C:\> $xl.Visible=$True

当您开始写脚本的时候,您可以不必做这步。但是这步能帮您检验我们写的PowerShell命令的执行结果。有很多种办法能将信息输入到电子表格中。做为一个简单的任务,我将演示如何使用单元格(cell)对象。

PS C:\> $cells=$ws.Cells

我们可以用行和列坐标来获取每一个单元格对象。

PS C:\> $cells.item(1,1)

如果您试这行代码,您将获取到很多信息。我们将继续往下并且输入一些信息到这个单元格。

PS C:\> $cells.item(1,1)=$env:computername

您的计算机名将会被填入 A1 单元格。让我们来填入更多的数据。

PS C:\> $cells.item(1,2)=$env:username
PS C:\> $cells.item(2,1)=(get-Date)

这个过程真的很简单。您只需要不断地记下当前的位置即可。如果您需要基本的格式,您可以使用每个单元格的 Font 属性。

PS C:\> $cells.item(1,1).font.bold=$True
PS C:\> $cells.item(1,2).font.bold=$True
PS C:\> $cells.item(1,1).font.size=16
PS C:\> $cells.item(1,2).font.size=16

好了,现在我们可以用 WorkBook 对象的 SaveAs() 方法保存这个文件。

PS C:\> $wb.SaveAs("c:\work\test.xlsx")

To fully exit, we’ll close the workbook and quit Excel.
若要完全退出,我们需要关闭工作簿并且退出Excel。

PS C:\> $wb.Close()
PS C:\> $xl.Quit()

如果您检查进程的话,您也许会发现Excel任然在运行,但它将会在5-10分钟之内退出,自少按我的经验是这样。以上是基本的要点,但在圆满完成之前,让我整理一个脚本,将这些材料整合在一起。

Param([string]$computer=$env:computername)

#get disk data
$disks=Get-WmiObject -Class Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"

$xl=New-Object -ComObject "Excel.Application"

$wb=$xl.Workbooks.Add()
$ws=$wb.ActiveSheet

$cells=$ws.Cells

$cells.item(1,1)="{0} Disk Drive Report" -f $disks[0].SystemName
$cells.item(1,1).font.bold=$True
$cells.item(1,1).font.size=18

#define some variables to control navigation
$row=3
$col=1

#insert column headings
"Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}

foreach ($drive in $disks) {
    $row++
    $col=1
    $cells.item($Row,$col)=$drive.DeviceID
    $col++
    $cells.item($Row,$col)=$drive.Size/1GB
    $cells.item($Row,$col).NumberFormat="0"
    $col++
    $cells.item($Row,$col)=$drive.Freespace/1GB
    $cells.item($Row,$col).NumberFormat="0.00"
    $col++
    $cells.item($Row,$col)=($drive.Size - $drive.Freespace)/1GB
    $cells.item($Row,$col).NumberFormat="0.00"
    $col++
    $cells.item($Row,$col)=($drive.Freespace/$drive.size)
    $cells.item($Row,$col).NumberFormat="0.00%"
    $col++
    $cells.item($Row,$col)=($drive.Size - $drive.Freespace) / $drive.size
    $cells.item($Row,$col).NumberFormat="0.00%"
}

$xl.Visible=$True

$filepath=Read-Host "Enter a path and filename to save the file"

if ($filepath) {
    $wb.SaveAs($filepath)
}

这也许是您想在PowerShell里做的事情:用WMI获取磁盘使用信息并将其记录在Excel电子表格中。这段脚本以计算机名做为参数,缺省值为localhost。然后使用Get-WMIObject来获取磁盘信息。

脚本的第一部分看起来应该很熟悉,它创建一个Excel应用程序和对象。该脚本向A1单元格插入一个标题。

$cells.item(1,1)="{0} Disk Drive Report" -f $disks[0].SystemName
$cells.item(1,1).font.bold=$True
$cells.item(1,1).font.size=18

脚本的主体部分从每个逻辑磁盘中提取数据,并且将一些属性写入Excel。由于我需要通过行和列来操作这些单元格对象,所以我将定义一些用来定位用的辅助变量。

$row=3
$col=1

通过它们,我可以插入我的表头。

"Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}

每循环一次,$col就增加1,所以达到向右“移动”的效果。现在我需要遍历disks集合。每次需要“向下”移动一行,并且从第一列开始。

foreach ($drive in $disks) {
    $row++
    $col=1
    $cells.item($Row,$col)=$drive.DeviceID
    $col++
    $cells.item($Row,$col)=$drive.Size/1GB
    $cells.item($Row,$col).NumberFormat="0"
    $col++
...

接下来我将合适的WMI属性插入对应的单元格。每增加一行,我可以从左开始这个过程。请注意我使用了 NumberFormat 属性来格式化每个单元格的值。有一种探索的方法是创建一个Excel宏来记下所有您希望的步骤,然后查看生成的VBA代码。通过稍许的练习,您可以将这些命令翻译为PowerShell代码。

当脚本向电子表格写完数据以后,我把它显示出来并且提示用户输入文件名。如果用户输入的文件名,那么文件以该文件名保存。否则,您可以继续编辑电子表格,然后手动保存它。这个演示脚本并不会自动关闭Excel。这个脚本执行的结果如图1所示:

PowerShell生成的Excel报表

结论

我知道还有很多问题,所以我将会把它们总结出来。在第二部分,我们将看到一些高级的格式化选项,以及其它使用Microsoft Excel的深入用法。如果您将要用PowerShell来创建Excel文档,您会尽可能做到极致。

用PowerShell操作Office系列文章

在PowerShell中操作Excel - 创建一个富Excel文档

让我们继续《在PowerShell中操作Excel》系列文章。上一次我们掩饰了如何用Microsoft Excel和Windows PowerShell来创建一个基本的报表。从某些方面来讲,我们上次创建的东西和创建CSV并在Excel中打开差不了多少。所以,如果您希望用Excel,让我们彻彻底底地使用它!在今天的文章中,我将沿用上次的演示脚本,但是创建一个更“富(richer)”的Excel文档。下一步,在第三部分中,我将为您演示如何从Excel文件中读取数据。

创建一个富Excel文档

和之前一样,我们将通过WMI获取磁盘信息,并创建一个Excel应用程序对象。

$disks=Get-WmiObject -Class Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"
$xl=New-Object -ComObject "Excel.Application"

和Microsoft Word类似,Excel自动化很大程度依赖于使用类似 xlDown 等内置常量。我可以记下一个常量值并创建一个变量,或者可以读取包含所需常量的合适的类。在我印象中,我将需要提取以下信息:

$xlConditionValues=[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
$xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

将来当我希望使用 xlDown 时,我可以通过 $xlDirection::xlDown 来指定它。您等等会看到一些这样的代码。现在,我将像第一部分那样写入磁盘数据,但先让我们加入一些样式。另一种引用电子表格的一部分是使用工作簿对象的 Range 属性。您既可以通过类似的方式 A1 引用一个单元格,或者通过类似 A1:A10 的方式引用一个范围。范围(Range)对象有一个样式(Style)属性。我将把 A1 单元格的样式设置成“Title”,并且把我的表头样式设置成“Heading 2”。

$range=$ws.range("A1")
$range.Style="Title"
#或者用这种方法
$ws.Range("A3:F3").Style = "Heading 2"

另一个常见的格式化选项是调整列宽。我们可以设置列宽为固定值或者为自动调整列宽。

$ws.columns.item("C:C").columnwidth=15
$ws.columns.item("D:F").columnwidth=10.5
$ws.columns.item("B:B").EntireColumn.AutoFit() | out-null

顺便提一句,我将某些方法,比如 AutoFit() 输出到管道 Out-Null 来禁止不需要的输出。以下是很有意思的地方:我想如果能用上Excel的条件格式功能将会很酷。具体来说,我想用交通灯图标集来反映每个驱动器的使用量。如我之前所说,既然我们要创建一个Excel文件,那么尽量做到极致。我将为您演示这些代码,不用紧张:)

$start=$ws.range("F4")
#获取最后一个单元格
$Selection=$ws.Range($start,$start.End($xlDirection::xlDown))
#增加图标集
$Selection.FormatConditions.AddIconSetCondition() | Out-Null
$Selection.FormatConditions.item($($Selection.FormatConditions.Count)).SetFirstPriority()
$Selection.FormatConditions.item(1).ReverseOrder = $True
$Selection.FormatConditions.item(1).ShowIconOnly = $False
$Selection.FormatConditions.item(1).IconSet = xlIconSet::xl3TrafficLights1
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Type = xlConditionValues::xlConditionValueNumber
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Value = 0.8
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Operator = 7
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Type = xlConditionValues::xlConditionValueNumber
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Value = 0.9
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Operator = 7

我并不是一夜之间突然知道怎么用PowerShell来做这些事情。相反地,我创建了一个Excel宏,然后应用样式,然后将代码翻译成PowerShell脚本。我希望我可以为您提供一系列翻译的规则,但是碰到一系列障碍和错误。请注意常量的使用?
(译者注:原文为I wish I could give you a set of translation rules, but it just takes trial and error and experience. Notice the use of the constant values?)

下一步,我将为插入一个柱形图到工作表:

$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlBarClustered

我又一次采用了创建一个宏来观察其中的方法并修正其中的值的方法。接下来,我需要为图表选择数据源。

$start=$ws.range("A3")
#获取最后一个单元格
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range("F3")
#获取最后一个单元格
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

驱动器名称将作为Y轴,%Used将作为X轴。我将用这个区域的集合来定义图表的数据。

$chartdata=$ws.Range("A$($Y.item(1).Row):A$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)")
$chart.SetSourceData($chartdata)

我希望对这个图表做的最后一件事是增加数据标题和图表标题。

$chart.seriesCollection(1).Select() | Out-Null
$chart.SeriesCollection(1).ApplyDataLabels() | out-Null
$chart.ChartTitle.Text = "Utilization"

Excel很可能并不会按您所希望的位置摆放这个图表,所以您可以使用以下代码来定位它:

$ws.shapes.item("Chart 1").top=40
$ws.shapes.item("Chart 1").left=400

TopLeft 是从Excel窗口开始计算的顶边距和左边距。可能会在获取右边距的时候遇到一些障碍和错误,但请注意在多台计算机上进行测试。最后一步是将工作表以计算机名来命名。

$xl.worksheets.Item("Sheet1").name=$name

当您明白所有这些Excel的魔法师如何工作的,那么要为您希望查看的每台计算机增加一个工作表也不是那么难了。以下截图显示最终的结果:

增强的Excel报表

结论

您可以下载我的演示脚本并且自己进行测试。如果您的确需要写数据到Excel,我建议您完整地操作一遍。也许需要掌握一些基础知识,但这方面的努力是值得的。下一步我们将演示如何从Excel文件中读取数据。

用PowerShell操作Office系列文章