在没有完整版 Excel 的情况下使用 PowerShell

• 下载 powershell.zip - 23.2 KB

引言

PowerShell 有一个很棒的命令可以导入 CSV 文件为 PS 对象 (Import-CSV)。如果能有一个直接从 Excel 电子表格导入的命令，那该多好！

背景

我为以前的雇主构建了一个 PowerShell 应用程序，该程序会根据 Excel 电子表格中的规范构建 SharePoint 站点。问题是 Excel 中的每个选项卡都必须导出为 CSV。维护起来是个噩梦。因此，我研究并找到了 Robert M. Troup, Jr. 的博客，关于将 Excel 导入 PowerShell，以及论坛上 Michael Sorens (msorens) 发布的一篇帖子，关于使用该方法创建一个 Import-Excel 函数。太棒了！我复制并在我的虚拟机中运行，结果…我需要安装 Excel。

我的虚拟机中没有 Excel，并且我希望这个脚本尽可能通用（且廉价）。此外，我不能指望它安装在我们的客户服务器上。这是我的解决方案……

使用代码

此解决方案使用可再发布的 Access 下载，但它是免费的！！！

下载并安装

• http://www.microsoft.com/en-us/download/details.aspx?id=13255  

接下来，在你的 PowerShell 顶部包含以下函数，或者将它放在另一个 ps1 文件中，并使用 PowerShell include 方法（句点后跟你的函数文件的路径）来“安装”它，在你要将 Excel 对象作为 PSObject 导入的位置之前。

function Import-Excel
{
    param (
    [string]$FileName,
    [string]$WorksheetName
    )

    if ($FileName -eq "") {
        throw "Please provide path to the Excel file"
        Exit
    }

    if (-not (Test-Path $FileName)) {
        throw "Path '$FileName' does not exist."
        Exit
    }

    $strSheetName = $WorksheetName + '$'
    $query = 'select * from ['+$strSheetName+']';

    $connectionString = 
      "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='"$FileName'";Extended 
         Properties='"Excel 12.0 Xml;HDR=YES'";"
    $conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
    $conn.open()

    $cmd = New-Object System.Data.OleDb.OleDbCommand($query,$conn) 
    $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd) 
    $dataTable = New-Object System.Data.DataTable 

    $dataAdapter.fill($dataTable)
    $conn.close()

    $myDataRow ="";
    $columnArray =@();
    foreach($col in $dataTable.Columns)
    {
        $columnArray += $col.toString();
    }

    $returnObject = @();
    foreach($rows in $dataTable.Rows)
    {
        $i=0;
        $rowObject = @{};
        foreach($columns in $rows.ItemArray){
            $rowObject += @{$columnArray[$i]=$columns.toString()};
            $i++;
        } 

        $returnObject += new-object PSObject -Property $rowObject;
    }

    return $returnObject;
}  

像这样调用你的 Import-Excel 函数…

$listOne = Import-Excel "test.xlsx" -WorksheetName:"list Sample One"
$listTwo= Import-Excel "test.xlsx" -WorksheetName:"list Sample Two"

示例 Excel 电子表格

完成了！！！

关注点

在开发此解决方案时，我学到了两件事：1). PowerShell 没有像 Import-CSV 那样内置的 Excel 导入功能，2). 网络上最常用的 Excel 导入方法依赖于安装完整的 Excel。

哦，而且 PowerShell 真棒！

历史

• 添加了脚本的 zip 下载，并链接到正确的 Microsoft 可再发布组件。是 Access 版本，而不是 Office 版本。
• 修改了一些措辞。