如何在PowerShell脚本中导入Excel文件？

数百万人使用 Microsoft Excel 来存储、组织和分析数据。 Excel 文件（.xlsx、.xls）包含大量有用的数据，例如报告、列表、计算等。作为 PowerShell 脚本编写者，您可能经常需要从 Excel 文件中提取数据以在脚本中使用。使用 PowerShell，您可以直接将 Excel 文件导入和导出到脚本中，并以编程方式处理数据。在本初学者指南中，我将向您介绍 PowerShell Import-Excel cmdlet，并向您展示如何使用它从 Excel 文件导入数据。

我们还将介绍如何安装模块、读取特定工作表和单元格范围、访问和过滤 Excel 数据，甚至更新现有 Excel 文件 - 所有这些都在 PowerShell 内进行！无论您需要自动化报告、处理计算还是提取数据进行分析，本教程都将向您展示 PowerShell 如何提供简单的 Excel 集成和自动化。继续阅读，了解使用 PowerShell 编写 Excel 脚本的强大功能！

PowerShell ImportExcel 模块简介

ImportExcel 模块是一个 PowerShell 模块，提供用于在 Excel 电子表格中导入和导出数据的 cmdlet。该模块提供与 Excel 文件的无缝集成并简化了使用 Excel 文件的过程。它提供了一系列 cmdlet，使用户能够轻松读取、操作和导出 Excel 文件中的数据。这对于自动化数据管理任务特别有用，否则这些任务将非常耗时且乏味。

默认情况下，PowerShell 中不包含 ImportExcel 模块，因此您需要单独安装它。安装后，您可以使用该模块提供的 cmdlet 在 PowerShell 中与 Excel 电子表格进行交互。

在 PowerShell 中安装 ImportExcel 模块

在 PowerShell 中导入 Excel 文件的最简单方法是使用 ImportExcel 模块。这个方便的模块可以从 PowerShell Gallery 免费安装。要安装 Import-Excel 模块，只需打开 PowerShell 并运行以下命令：

Install-Module -Name ImportExcel

这将下载并安装最新版本的 ImportExcel 模块及其依赖项。顺便说一句，您需要以管理员身份运行 PowerShell 才能安装模块！如果没有，您可以使用“Scope”参数为当前用户安装，无需管理员权限。

Install-Module -Name ImportExcel -Scope CurrentUser

安装后，您可以使用以下命令将模块导入到脚本中：

Import-Module ImportExcel

现在，我们准备将数据从 Excel 导入到 PowerShell！

使用 PowerShell 从 Excel 导入数据

使用 PowerShell 从 Excel 导入数据是一个简单的过程。安装 ImportExcel 模块后，您可以使用 Import-Excel cmdlet 从 Excel 电子表格获取数据。

要导入 Excel 文件，请按照下列步骤操作：

1. 通过打开 PowerShell 控制台或 PowerShell ISE 启动 PowerShell 会话。
2. 通过执行以下命令导入 Import-Excel 模块：Import-Module -Name ImportExcel
3. 使用 Import-Excel cmdlet 导入 Excel 文件。例如：Import-Excel -Path C:\Path\To\Excel\File.xlsx
4. Excel 文件将作为 PowerShell 对象导入，允许用户使用 PowerShell cmdlet 和语法访问和操作其数据。

该 cmdlet 将 Excel 文件的路径作为其参数，并返回包含电子表格中的数据的 PowerShell 对象。

例如，要从位于“C:\Reports\EmpData.xlsx”的 Excel 电子表格导入数据，您可以运行以下命令：

# File Path variable
$FilePath = "C:\Reports\EmpData.xlsx"

# Read the Excel file and store its data into the variable
$ExcelData = Import-Excel -Path $FilePath

Import-Excel cmdlet 读取 Excel 文件并将其存储在 $ExcelData 变量中。

这是我的 Excel 文件：

从 PowerShell 访问 Excel 数据

导入的 Excel 数据作为 PSObject 存储在变量中。您可以像访问任何其他 PowerShell 对象一样访问它。这里有些例子：

阅读具体工作表

要从 Excel 文件读取特定工作表，请使用 -WorkSheetName 参数：

$Sheet1Data = Import-Excel -Path $FilePath -WorksheetName "Sheet1"

这将仅导入“Sheet1”工作表数据。

您可以使用以下方法从 Excel 电子表格导入特定列：

$data = Import-Excel -Path "C:\Temp\EmpData.xlsx" -HeaderName 'Employee Name', 'Designation'

读取单元格范围

您还可以使用 -StartRow 和 -StartColumn 参数从工作表中读取特定单元格区域：

Import-Excel -Path $FilePath -WorksheetName "Sheet1" -StartRow 1 -StartColumn 1 -EndRow 5 -EndColumn 2

在此示例中，我们从“Sheet1”工作表导入数据，重点关注第 1 行至第 5 行以及第 1 列和第 2 列。

使用 PowerShell Import-Excel 的高级数据管理技术

PowerShell Import-Excel 提供了广泛的高级数据管理技术，可以帮助您自动执行数据管理任务。一些最有用的技术包括过滤、排序和分组数据。

过滤数据

要筛选数据，您可以使用Where-Object cmdlet。此 cmdlet 允许您指定数据中的每一行必须满足的条件。不满足条件的行将被过滤掉。

$SalesData = Import-Excel -Path $FilePath -WorksheetName "Sales"

$USSales = $SalesData | Where-Object {$_.Region -eq "US"}

这会将销售工作表过滤为仅包含美国地区的记录。您还可以使用以下方式进行过滤：

$FilteredData = Import-Excel -Path $FilePath | Where-Object {$_.Designation -eq 'Software Engineer'}

在此示例中，我们从“SalesData”工作表导入数据，重点关注第 2 行到第 10 行。

• 要对数据进行排序，您可以使用 Sort-Object cmdlet。此 cmdlet 允许您指定一个或多个排序依据的列以及排序顺序。
• 要对数据进行分组，您可以使用 Group-Object cmdlet。此 cmdlet 允许您按一列或多列对数据进行分组并对每一组执行计算。

使用 PowerShell 更新 Excel 文件

您还可以使用 ImportExcel 模块更新现有 Excel 文件或将数据添加到现有 Excel 文件。

首先，导入Excel文件：

#Import Excel File
$ExcelData = Import-Excel -Path "C:\Temp\EmpData.xlsx" -WorksheetName "Sheet1"

更改 $ExcelData 变量，例如添加新行：

#Add New Row
$NewRow = [PSCustomObject]@{
    "Employee Name" = "Ada Lovelace" 
    "Designation" = "Software Engineer"
    "Region" = "US"
}

$ExcelData += $NewRow

最后，将更新后的数据保存回原始Excel文件：

#Save the Excel File
Export-Excel -Path $FilePath -InputObject $ExcelData -ClearSheet

-ClearSheet 参数先清除所有现有数据，然后再导出更新的数据。

同样，您可以通过相应修改 $ExcelData 来更新、删除或添加新工作表。

替代方案：COM Interop 读取 Excel 数据

在 PowerShell 中导入 Excel 文件而无需任何外部模块的另一种方法是使用 COM Interop。您需要安装 Excel 才能使用此方法创建或访问 Excel 文件。

首先，创建一个 Excel COM 对象：

$Excel = New-Object -ComObject Excel.Application

然后打开所需的 Excel 文件：

$Workbook = $Excel.Workbooks.Open("C:\Reports\data.xlsx")

现在您可以读取单元格值，例如：

$Value = $Workbook.Worksheets["Sheet1"].Cells.Item(1,1).Value()

COM Interop 方法使您可以对 Excel 应用程序进行细粒度控制。这是一个完整的例子：

# Create COM Excel object 
$Excel = New-Object -ComObject Excel.Application

# Open Excel file
$Workbook = $Excel.Workbooks.Open("C:\Temp\EmpData.xlsx") 

#Get the Worksheet
$Worksheet = $Workbook.Worksheets["Sheet1"]

# Read specific cells
$cellValue1 = $worksheet.Cells.Item(1, 1).Value()
$cellValue2 = $worksheet.Cells.Item(1, 2).Value()

# Display value
Write-Host "Cell A1 Value: " $cellValue1
Write-Host "Cell B1 Value: " $cellValue2

# Close Excel
$workbook.Close($false)
$Excel.Quit()

使用 PowerShell Import-Excel 进行常见错误和故障排除

与任何工具一样，使用 PowerShell Import-Excel 时可能会出现错误。一些最常见的错误包括格式、文件路径和权限问题。

错误：“ImportExcel：术语‘ImportExcel’未被识别为 cmdlet、函数、脚本文件或可操作程序的名称。”

解决方案：通过运行以下命令安装 ImportExcel 模块：Install-Module -Name ImportExcel

错误：文件路径不正确

错误消息：找不到文件“C:\wrong\path\to\file.xlsx”。要修复，请确保文件路径正确。如果路径包含空格，请将其用引号引起来。

错误：“无法加载 Excel 工作簿。”

这通常意味着文件路径或名称不正确。仔细检查路径和文件名并确保文件存在。还可以尝试完全限定路径而不是使用相对路径。您还可以使用 Test-Path cmdlet，它允许您测试指定路径中是否存在文件或目录。

错误：在工作簿中找不到工作表“WrongSheetName”

解决方案：确保工作表名称正确。工作表名称区分大小写。

错误：“文档未打开。”

如果您尝试操作或保存工作簿而不先打开它，就会发生这种情况。确保在执行任何其他操作之前使用 Open-ExcelPackage cmdlet 打开工作簿。

错误：“工作簿已打开。”

您一次只能在一个 PowerShell 进程中打开 ExcelPackage。确保在再次打开之前使用 Close-ExcelPackage 完全关闭工作簿。

错误：“工作表‘Sheet1’不存在。”

指定的工作表名称与 Excel 文件中的实际工作表名称不匹配。仔细检查名称或使用 Get-ExcelSheetInfo 列出所有工作表。

错误：“行数无效。”

如果导入特定行范围，请确保结束行号对于工作表有效。 Get-ExcelSheetInfo 可用于检查总行数。

为了避免错误，请检查工作簿路径、工作表名称、行号并正确打开/关闭工作簿。使用 -ErrorAction Stop 来显示错误。查看模块文档以获取更多指导。

PowerShell 导入 Excel 资源和进一步学习

如果您有兴趣了解有关 PowerShell Import-Excel 的更多信息，可以使用许多资源来帮助您入门。 PowerShell Gallery 或 GitHub 上的 ImportExcel 模块是查找可与 PowerShell Import-Excel 结合使用的其他模块和 cmdlet 的好地方。

结论

借助 ImportExcel 模块，在 PowerShell 中导入和自动化 Excel 文件既简单又强大。在本文中，我们介绍了如何安装和使用 ImportExcel 模块将 Excel 数据读取到 PowerShell 对象中。您学习了如何使用 PowerShell 语法导入特定工作表、单元格范围、筛选和处理 Excel 数据。我们还了解了如何直接从脚本更新和导出 Excel 文件。通过本指南中概述的技术和最佳实践，您可以充分利用此工具并成为更高效的数据分析师。

使用免费的 ImportExcel 模块在 PowerShell 中导入 Excel 文件非常容易。关键步骤是：

• 安装导入Excel模块
• 使用 Import-Excel cmdlet 读取 Excel 文件
• 作为 PowerShell 对象访问工作表数据
• 使用 PowerShell 语法过滤、处理 Excel 数据
• 从 PowerShell 更新和导出 Excel 文件

通过这些技术，您可以直接在 PowerShell 脚本中自动执行 Excel 报告、数据处理等操作。无论您是刚刚开始使用 PowerShell Import-Excel 还是经验丰富的用户，总有新的东西需要学习和探索。想象一下您可以节省的时间和提高的生产力。因此，立即开始使用 PowerShell 编写 Excel 脚本，将您的数据处理提升到新的水平！