PowerShell 和 Excel：發揮 ImportExcel 的威力 – techsyncer

Microsoft Excel是一種無法逃避的普遍工具，即使我們試圖逃避也無法。許多IT專業人員使用Excel作為一個小型資料庫，將大量的數據存儲在各種自動化例程中。自動化和Excel的最佳情景是什麼？PowerShell和Excel！

Excel電子表格一直以來都很難編寫和自動化腳本。與其較少功能（並且更簡單）的CSV文件對應的Excel工作簿不僅僅是簡單的文本文件。Excel工作簿需要使用PowerShell來操作複雜的元件對象模型(COM)，因此您必須安裝Excel。但現在不再需要。

幸運的是，一位敏銳的PowerShell社區成員Doug Finke為我們這些凡人創建了一個PowerShell模塊，稱為ImportExcel。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庫下載和安裝模塊。幾秒鐘後，你就可以開始使用了。

使用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'

Worksheet created with PowerShell and Excel

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

通過使用 AutoSize 開關參數自動調整所有行的大小，TableName 指定將包含所有數據的表的名稱，並使用 WorksheetName 參數名為 Processes，您可以在下面的屏幕截圖中看到可以構建的內容。

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。

在最基本的用法中，您只需要提供 Excel 文檔/工作簿的路徑，使用 Path 參數，如下所示。您將看到它讀取第一個工作表，即 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 命令有其他用於讀取各種類型的 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']

此過程類似於使用 COM 對象打開工作簿的方式，使用 excel.workbooks.open。

將工作表分配給變量後，您可以深入到單個行、列和儲存格。也許您需要查找 A1 行中的所有儲存格值。只需引用 Cells 屬性，並提供 A1 的索引，如下所示。

$worksheet.Cells['A1'].Value

您還可以通過分配不同的值來更改工作表中單元格的值，例如 $worksheet.Cells['A1'] = 'differentvalue'

將Excel包在內存中後，使用Close-ExcelPackage cmdlet來釋放該包是很重要的。

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文件，您將看到與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，您可以像處理 CSV 檔案一樣匯入、匯出和管理 Excel 工作簿中的資料，而不需要安裝 Excel！

在本文中，您學習了在 Excel 工作簿中讀取和寫入資料的基本知識，但這只是冰山一角。使用 PowerShell 和 ImportExcel 模組，您可以創建圖表、樞紐分析表，以及利用 Excel 的其他強大功能！