PowerShell 和 Excel：发挥 ImportExcel 的强大功能

教程
PowerShell

微软 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上工作，你需要使用mono-libgdiplus包，使用brew install mono-libgdiplus进行安装。本文中的所有示例都将在macOS上构建，但所有示例都应该在跨平台上工作。

如果你在macOS上使用，请确保在继续之前重新启动PowerShell会话。

安装ImportExcel模块

首先通过运行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中使用Import-Csv管理CSV文件

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

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

Export-Excel 命令接受任何对象，就像 Export-Csv 一样。您可以将任何类型的对象传送到此命令。

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

Get-Process | Export-Excel -Path './processes.xlsx'
Worksheet created with PowerShell and Excel

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

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

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

Export-Excel cmdlet有很多参数可以用来创建各种类型的Excel工作簿。要了解有关Export-Excel的全部信息，请运行Get-Help Export-Excel。

使用PowerShell导入Excel

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

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

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

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

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

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

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

使用Import-Excel命令有其他参数可用于读取各种类型的Excel工作簿。要了解有关Import-Excel的全部信息，请运行Get-Help Import-Excel。

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

现在您知道如何使用PowerShell和Excel读取整个工作表，但如果您只需要单个单元格的值呢？从技术上讲，您可以使用Import-Excel，然后使用Where-Object来过滤出您需要的值，但这样效率不高。

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

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

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

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

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

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

一旦您将工作表分配给变量，现在您可以深入到单独的行、列和单元格。也许您需要找到A1行中的所有单元格值。您只需引用Cells属性，并提供A1的索引即可，如下所示。

$worksheet.Cells['A1'].Value

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

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

Close-ExcelPackage $excel

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

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

相关：Export-Csv：将对象转换为CSV文件

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

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

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

转换多个工作表

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

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

## 寻找工作簿中的每个工作表
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## 读取每个工作表并创建一个同名的CSV文件
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 的其他强大功能！

Source:
https://adamtheautomator.com/powershell-excel/