Powershell 获取Excel, Worksheet返回DataTable

最新推荐文章于 2024-03-27 14:32:33 发布

mystert

最新推荐文章于 2024-03-27 14:32:33 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： Powershell 文章标签： Powershell 获取Excel DataTable

本文链接：https://blog.youkuaiyun.com/Q215046120/article/details/89887346

Powershell 专栏收录该内容

60 篇文章

订阅专栏

注意：此脚本只能运行于32位Powersehll(x86)

建议本篇所有代码写于并保存于一个文件夹下

首先打开Powersehll_ISE(x86)

写入如下代码并保存：

Import-Module .\ACE.PSM1

$ds = Get-ACEData ".\附件二.RT-XXX control list for XXX.v3.Ex import and more att_20190428.xlsx" -Table "control list$"
$dt = $ds.tables[0]
#$drs = $dt.Select("[Calenda Date] is not null")
Write-Host $dt.Columns[0].ColumnName
Write-Host $dt.Rows[0][0];
$dt.Rows[0].ItemArray[0].ToString();
$dt.Rows[0].ItemArray[1].ToString();
$dt.Rows[0].ItemArray[2].ToString();

Import-Module .\ACE.PSM1 和 $ds = Get-ACEData ".\附件二.RT-RPA control list for FSA.v3.Ex import and more att_20190428.xlsx" -Table "control list$" 因为都是导入相对路径，也可以写入绝对路径， -Table "control list$" 是EXCEL的Sheet名字，后面需要加"$"

保存为test.ps1

然后再新建一个Powershell 文件,写入如下代码并保存为ACE.PSM1:

#######################
function Get-ACEConnection
{
    param($ConnectionString)

    $conn = new-object System.Data.OleDb.OleDbConnection($ConnectionString)
    $conn.open()
    $conn

} #Get-ACEConnection

#######################
function Get-ACETable
{
    param($Connection)

    $Connection.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)

} #Get-ACETable

#######################
function Get-ACEConnectionString
{
    param($FilePath)

    switch -regex ($FilePath)
    {
        '\.xls$|\.xlsx$|\.xlsb$' {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=Yes;IMEX=1`";"}
        '\.mdb$|\.accdb$'        {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Persist Security Info=False;"}
    }

} #Get-ACEConnectionString

#######################
<#
.SYNOPSIS
Queries Excel and Access files.
.DESCRIPTION
Get-ACEData gets data from Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel (*.xls, *.xlsx, and *.xlsb) files
.INPUTS
None
    You cannot pipe objects to Get-ACEData
.OUTPUTS
   System.Data.DataSet
.EXAMPLE
Get-ACEData -FilePath ./budget.xlsx -WorkSheet 'FY2010$','FY2011$'
This example gets data for the worksheets FY2010 and FY2011 from the Excel file
.EXAMPLE
Get-ACEData - -FilePath ./budget.xlsx -WorksheetListOnly
This example list the Worksheets for the Excel file
.EXAMPLE
Get-ACEData -FilePath ./projects.xls -Query 'Select * FROM [Sheet1$]'
This example gets data using a query from the Excel file
.NOTES
Imporant!!!
Install ACE 12/26/2010 or higher version from LINK below
If using an x64 host install x64 version and use x64 PowerShell
Version History
v1.0   - Chad Miller - 4/21/2011 - Initial release
.LINK
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
#>
function Get-ACEData
{

    [CmdletBinding()]
    param(
    [Parameter(Position=0, Mandatory=$true)]
    [ValidateScript({$_ -match '\.xls$|\.xlsx$|\.xlsb$|\.mdb$|\.accdb$'})] [string]$FilePath,
    [Parameter(Position=1, Mandatory=$false)]
    [alias("Worksheet")] [string[]]$Table,
    [Parameter(Position=2, Mandatory=$false)] [string]$Query,
    [Parameter(Mandatory=$false)]
    [alias("WorksheetListOnly")] [switch]$TableListOnly
    )

    $FilePath = $(resolve-path $FilePath).ProviderPath
    $conn = Get-ACEConnection -ConnectionString $(Get-ACEConnectionString $FilePath)

    #If TableListOnly switch specified list tables/worksheets then exit
    if ($TableListOnly)
    {
        Get-ACETable -Connection $conn
        $conn.Close()

    }
    #Else tablelistonly switch not specified
    else
    {
        $ds = New-Object system.Data.DataSet
        $cmd = new-object System.Data.OleDb.OleDbCommand
        $cmd.Connection = $conn
        $da = new-object System.Data.OleDb.OleDbDataAdapter

        if ($Query)
        {
            $qry = $Query
            $cmd.CommandText = $qry
            $da.SelectCommand = $cmd
            $dt = new-object System.Data.dataTable
            $null = $da.fill($dt)
            $ds.Tables.Add($dt)
        }
        #Return one or more specified tables/worksheets
        elseif ($Table)
        {
            $Table |
            foreach{ $qry = "select * from [{0}]" -f $_;
            $cmd.CommandText = $qry;
            $da.SelectCommand = $cmd;
            $dt = new-object System.Data.dataTable("$_");
            $null = $da.fill($dt);
            $ds.Tables.Add($dt)}
        }
        #Return all tables/worksheets
        else
        {
            Get-ACETable $conn |
            where {$_.TABLE_TYPE -eq 'TABLE' } |
            foreach{ $qry = "select * from [{0}]" -f $_.TABLE_NAME;
            $cmd.CommandText = $qry;
            $da.SelectCommand = $cmd;
            $dt = new-object System.Data.dataTable("$($_.TABLE_NAME)");
            $null = $da.fill($dt);
            $ds.Tables.Add($dt)}
        }

        $conn.Close()
        Write-Output ($ds)
    }

} #Get-ACEData

Export-ModuleMember -function Get-ACEData