使用 PowerShell 读取数据并将其写入 Excel 文件

在本文中，我们将展示如何直接从 PowerShell 脚本读取和写入 Excel 工作表中的数据。您可以使用 Excel 和 PowerShell 来清点并生成有关计算机、服务器、基础设施、Active Directory 等的各种报告。

您可以通过单独的 COM 对象（组件对象模型）从 PowerShell 访问 Excel 工作表。这需要在计算机上安装 Excel。

在展示如何访问 Excel 单元格中的数据之前，有必要了解 Excel 文件中表示层的体系结构。下图显示了 Excel 对象模型中的 4 个嵌套表示层：

• 应用层 - 处理正在运行的 Excel 应用程序；

• 工作簿层 - 可以同时打开多个工作簿（Excel 文件）；

• 工作表层 - 每个 XLSX 文件可以包含多个工作表；

• 范围层 - 在这里您可以访问特定单元格或单元格范围中的数据。

如何使用 PowerShell 从 Excel 电子表格读取数据？

让我们看一个简单的示例，了解如何使用 PowerShell 访问包含员工列表的 Excel 文件中的数据。

首先，使用 COM 对象在计算机上运行 Excel 应用程序（应用程序层）：

$ExcelObj = New-Object -comobject Excel.Application

运行该命令后，Excel 将在您的计算机后台启动。要显示 Excel 窗口，请更改 COM 对象的 Visible 属性：

$ExcelObj.visible=$true

您可以显示所有 Excel 对象属性，如下所示：

$ExcelObj| fl

然后你可以打开一个Excel文件（工作簿）：

$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")

每个 Excel 文件可以包含多个工作表。让我们显示当前 Excel 工作簿中的工作表列表：

$ExcelWorkBook.Sheets| fl Name, index

然后您可以打开所需的工作表（按其名称或索引）：

$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")

您可以使用以下命令获取当前（活动）Excel 工作表的名称：

$ExcelWorkBook.ActiveSheet | fl Name, Index

然后您可以从 Excel 工作表中的单元格中获取值。您可以使用不同的方法来获取当前 Excel 工作表上的单元格值：使用区域、单元格、列或行。请参阅下面如何从同一单元格获取数据的示例：

$ExcelWorkSheet.Range("B4").Text
$ExcelWorkSheet.Range("B4:B4").Text
$ExcelWorkSheet.Range("B4","B4").Text
$ExcelWorkSheet.cells.Item(4, 2).text
$ExcelWorkSheet.cells.Item(4, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(4).Text
$ExcelWorkSheet.Rows.Item(4).Columns.Item(2).Text

使用 PowerShell 将 Active Directory 用户信息导出到 Excel 电子表格

让我们看一个如何从 PowerShell 访问 Excel 数据的实际示例。假设我们想要从 Active Directory 中获取 Excel 文件中每个用户的一些信息。例如，他们的电话号码（telephoneNumber 属性）、部门和电子邮件地址。

要获取有关 AD 用户属性的信息，我们将使用 PowerShell Active Directory 模块中的 Get-ADUser cmdlet。

# Importing Active Directory module into PowerShell session
import-module activedirectory
# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# Get the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Loop through all rows in Column 1 starting from Row 2 (these cells contain the domain usernames)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# Get the values of user attributes in AD
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
# Fill in the cells with the data received from AD
$ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
$ExcelWorkSheet.Columns.Item(5).Rows.Item($i) = $ADuserProp.department
$ExcelWorkSheet.Columns.Item(6).Rows.Item($i) = $ADuserProp.mail
}
# Save the XLS file and close Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

因此，已为 Excel 文件中的每个用户添加包含 AD 信息的列。

让我们考虑使用 PowerShell 和 Excel 制作报告的另一个示例。假设您想要制作有关所有域服务器上的 Print Spooler 服务状态的 Excel 报告。

您可以使用 Get-ADComputer cmdlet 获取 Active Directory 中的服务器列表，并使用 WinRM Invoke-Command cmdlet 远程检查服务器上服务的状态。

# Create an Excel object
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Add a workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# Rename a worksheet
$ExcelWorkSheet.Name = 'Spooler Service Status'
# Fill in the head of the table
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# Make the table head bold, set the font size and the column width
$ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Item(1).Font.size=15
$ExcelWorkSheet.Columns.Item(1).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(2).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(3).ColumnWidth=28
# Get the list of all Windows Servers in the domain
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# Connect to each computer and get the service status
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer -ScriptBlock { Get-Service spooler | select Name, status }
# Fill in Excel cells with the data obtained from the server
$ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $result.PSComputerName
$ExcelWorkSheet.Columns.Item(2).Rows.Item($counter) = $result.Name
$ExcelWorkSheet.Columns.Item(3).Rows.Item($counter) = $result.Status
$counter++
}
# Save the report and close Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

您可以在多种场景下使用 PowerShell 访问 Excel。例如，您可以创建方便的 Active Directory 报告或创建 PowerShell 脚本来从 Excel 更新 AD 数据。

例如，您可以要求人力资源部门的员工在 Excel 中保留用户注册。然后使用 PowerShell 脚本和

Set-ADUser

cmdlet，员工可以自动更新 AD 中的用户信息（只需委派员工更改 AD 用户属性并展示如何运行 PowerShell 脚本的权限）。因此，您可以保留包含相关电话号码、职位和部门的最新地址簿。