PowerShell 和 Excel：掌握权力！

Microsoft Excel 是我们大多数人即使尝试也无法逃脱的无处不在的工具之一。许多 IT 专业人员使用 Excel 作为小型数据库，在各种自动化例程中存储大量数据。自动化和 Excel 的最佳场景是什么？ PowerShell 和 Excel！

Excel 电子表格一直很难编写脚本和自动化。与功能较少（且更简单）的 CSV 文件对应物不同，Excel 工作簿不仅仅是简单的文本文件。 Excel 工作簿需要 PowerShell 来操作复杂的组件对象模型 (COM) 对象，因此您必须安装 Excel。不再。

值得庆幸的是，精明的 PowerShell 社区成员 Doug Finke 为我们这些凡人创建了一个名为 ImportExcel 的 PowerShell 模块。 ImportExcel 模块消除了所有这些复杂性。它使您可以轻松管理 Excel 工作簿并开始编写 PowerShell 脚本！

在本文中，我们将探讨使用 ImportExcel 模块和一些流行的用例可以使用 PowerShell 和 Excel 执行哪些操作。

先决条件

在 Windows 系统上运行 ImportExcel 模块时，不需要单独的依赖项。但是，如果您在 macOS 上工作，则需要使用 brew install mono-libgdiplus 安装 mono-libgdiplus 软件包。本文中的所有示例都将使用 macOS 构建，但所有示例都应该跨平台运行。

如果您使用的是 macOS，请务必在继续之前重新启动 PowerShell 会话。

安装导入Excel模块

首先通过运行 Install-Module ImportExcel -Scope CurrentUser 通过 PowerShell Gallery 下载并安装模块。几分钟后，您就可以开始了。

使用 PowerShell 和 Excel 导出到工作表

您可能熟悉标准 PowerShell cmdlet Export-Csv 和 Import-Csv。这些 cmdlet 允许您读取 PowerShell 对象并将其导出到 CSV 文件。不幸的是，没有 Export-Excel 和 Import-Excel cmdlet。但是使用 ImportExcel 模块，您可以构建自己的功能。

系统管理员最常见的请求之一是将 PowerShell 对象导出到 Excel 工作表。使用 ImportExcel 模块中的 Export-Excel cmdlet，您可以轻松实现这一目标。

例如，也许您需要找到本地计算机上运行的一些进程并将它们放入 Excel 工作簿中。

Export-Excel cmdlet 完全按照 Export-Csv 的方式接受任何对象。您可以将任何类型的对象通过管道传输到此 cmdlet。

要使用 PowerShell 查找系统上运行的进程，请使用 Get-Process cmdlet，它会返回每个正在运行的进程以及有关每个进程的各种信息。要将信息导出到 Excel，请使用 Export-Excel cmdlet 提供将创建的 Excel 工作簿的文件路径。您可以在下面看到命令示例和生成的 Excel 文件的屏幕截图。

Get-Process | Export-Excel -Path './processes.xlsx'

恭喜！您现在已经像 Export-Csv 一样导出了所有信息，但是与 Export-Csv 不同，我们可以使这些数据更加精美。让我们确保工作表名称名为 Processes，数据位于表格中并且行大小自动调整。

通过使用 AutoSize 开关参数自动调整所有行的大小，使用 TableName 指定将包含所有数据的表的名称以及 WorksheetName 参数进程的名称，您可以在下面的屏幕截图中看到可以构建的内容。

Get-Process | Export-Excel -Path './processes.xlsx' -AutoSize -TableName Processes -WorksheetName Proccesses

Export-Excel cmdlet 具有大量参数，可用于创建各种类型的 Excel 工作簿。要全面了解 Export-Excel 的功能，请运行 Get-Help Export-Excel。

使用 PowerShell 导入到 Excel

因此，您在上一节中已将一些信息导出到名为 processes.xlsx 的文件中。也许现在您需要将此文件移动到另一台计算机并使用 PowerShell 和 Excel 导入/读取此信息。没问题。您可以使用Import-Excel。

在最基本的用法中，您只需使用 Path 参数提供 Excel 文档/工作簿的路径，如下所示。您将看到它读取第一个工作表（在本例中为Processes）工作表，并返回 PowerShell 对象。

Import-Excel -Path './processes.xlsx'

也许您的 Excel 工作簿中有多个工作表？您可以使用 WorksheetName 参数读取特定工作表。

Import-Excel -Path './processes.xlsx' -WorkSheetname SecondWorksheet

您是否只需要读取 Excel 工作表中的某些列？使用 HeaderName 参数仅指定您想要读取的参数。

Import-Excel -Path './processes.xlsx' -WorkSheetname Processes -HeaderName 'CPU','Handle'

Import-Excel cmdlet 还具有其他参数，可用于读取各种类型的 Excel 工作簿。要全面了解 Import-Excel 的功能，请运行 Get-Help Import-Excel。

使用 PowerShell 获取（和设置）Excel 单元格值

您现在知道如何使用 PowerShell 和 Excel 读取整个工作表，但如果您只需要单个单元格值怎么办？从技术上讲，您可以使用Import-Excel并使用Where-Object过滤出所需的值，但这不会太高效。

相反，使用 Open-ExcelPackage cmdlet，您可以将 Excel 工作簿“转换”为 PowerShell 对象，然后可以读取和操作该对象。要查找单元格值，首先打开 Excel 工作簿将其调入内存。

$excel = Open-ExcelPackage -Path './processes.xlsx'

如果直接使用 COM 对象，Open-ExcelPackage 类似于使用 New-Object -comobject excel.application。

接下来，选择工作簿内的工作表。

$worksheet = $excel.Workbook.Worksheets['Processes']

此过程类似于使用 excel.workbooks.open 打开工作簿的 COM 对象方式。

将工作表分配给变量后，您现在可以向下钻取到各个行、列和单元格。也许您需要查找 A1 行中的所有单元格值。您只需引用提供索引 A1 的 Cells 属性，如下所示。

$worksheet.Cells['A1'].Value

您还可以通过分配不同的值来更改工作表中单元格的值，例如。 $worksheet.Cells['A1']='不同值'

一旦进入内存，使用 Close-ExcelPackage cmdlet 释放 Excel 包非常重要。

Close-ExcelPackage $excel

使用 PowerShell 和 Excel 将工作表转换为 CSV 文件

一旦您获得了通过 PowerShell 对象表示的 Excel 工作表的内容，将 Excel 工作表“转换”为 CSV 只需将这些对象发送到 Export-Csv cmdlet。

使用之前创建的 processes.xlsx 工作簿，读取第一个工作表，该工作表将所有数据获取到 PowerShell 对象中，然后使用以下命令将这些对象导出到 CSV。

Import-Excel './processes.xlsx' | Export-Csv -Path './processes.csv' -NoTypeInformation

如果您现在打开生成的 CSV 文件，您将在流程工作表中看到相同的数据（在此示例中）。

转换多个工作表

如果您的 Excel 工作簿包含多个工作表，您还可以为每个工作表创建一个 CSV 文件。为此，您可以使用 Get-ExcelSheetInfo cmdlet 查找工作簿中的所有工作表。获得工作表名称后，您可以将这些名称传递给 WorksheetName 参数，并使用工作表名称作为 CSV 文件的名称。

下面是使用 PowerShell 和 Excel 所需的示例代码。

## find each sheet in the workbook
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## read each sheet and create a CSV file with the same name
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

结论

使用 PowerShell 和 Excel，您可以导入、导出和管理 Excel 工作簿中的数据，就像处理 CSV 一样，而无需安装 Excel！

在本文中，您学习了在 Excel 工作簿中读取和写入数据的基础知识，但这只是皮毛。使用 PowerShell 和 ImportExcel 模块，您可以创建图表、数据透视表并利用 Excel 的其他强大功能！