SharePoint Online：使用 PowerShell 将 Excel 文件数据导入现有列表

要求： 将 Excel 文件中的数据导入到现有的 SharePoint Online 列表中。

SharePoint Online：使用 PowerShell 将 Excel 导入现有列表

您是否正在寻找一种定期将 Excel 电子表格导入 SharePoint Online 列表的方法？如果是这样，PowerShell 可能是一个不错的选择。在本文中，我将向您展示如何使用 PowerShell 将数据从 Excel 导入到 SharePoint Online 列表中。

虽然我的另一篇文章介绍了如何将数据从 CSV 文件导入到 SharePoint Online 列表？，但此脚本的目标是将 XSLX 文件导入到现有的 SharePoint Online 列表中。

请确保数据格式正确！例如，如果您的区域设置是英国标准，则日期列应采用“dd/mm/yyyy”格式。

PowerShell 将 Excel 数据导入现有 SharePoint Online 列表

让我们使用 PowerShell 将任务列表从 Excel 导入到 SharePoint Online：

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Config Variables
$SiteURL="https://crescent.sharepoint.com/sites/marketing"
$ListName="Project Tasks"
$FilePath ="C:\Users\salaudeen\Desktop\Tasks.xlsx"

#Function to get data from Excelsheet
Function Import-Excel
{
    param ([string]$FileName)

    #Create an Object Excel.Application using Com interface
    $ExcelObject = New-Object -ComObject Excel.Application
    $ExcelObject.Visible = $false

    #Open WorkBook
    $WorkBook = $ExcelObject.Workbooks.Open($FilePath)

    #Load the First work sheet - You can use Sheet name as: $workBook.Sheets.Item("Sheet Title")
    $WorkSheet = $WorkBook.Sheets.Item(1)

    #Select the range of data used
    $Columns = $WorkSheet.UsedRange.Columns.Count
    $Rows = $WorkSheet.UsedRange.Rows.Count
    #Get Column Headers from Excelsheet as Fields in SharePoint List
    $Fields = @()
    For($Column=1; $Column -le $Columns; $Column++) 
    {
        #Get the Value from Excel Sheet
        $FieldName = $WorkSheet.Columns.Item($Column).Rows.Item(1).Text
        $Fields += $FieldName
    }

    #Get All rows from the Excelsheet
    $DataCollection =@()
    For($Row=2; $Row -le $Rows;$Row++)
    {
        $Data = New-Object PSObject
        #Iterate through columns of the row
        For($Column=1; $Column -le $Columns; $Column++)
        {
            #Get the Value from Excel Sheet
            $Value = $WorkSheet.Columns.Item($Column).Rows.Item($Row).Text

            #Frame Data
            $Data | Add-Member -MemberType noteproperty -Name $Fields[$Column-1] -Value $Value
        }
        $DataCollection +=$Data
    }

    #Close Objects
    $WorkBook.Close()
    $ExcelObject.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObject) | Out-null

    Return $DataCollection
}

Try {
    Write-host -f Yellow "Reading Data from Excel File..." -NoNewline
    #Get Data from Excel File
    $ExcelData = Import-Excel $FilePath
    Write-host -f Yellow "$($ExcelData.count) Rows Found!" 

    #Setup Credentials to connect
    $Cred = Get-Credential
 
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

    #Get the List
    $List=$Ctx.Web.Lists.GetByTitle($ListName)

    $i=1
    Foreach ($Row in $ExcelData)
    {
        Write-host -f Yellow "Adding Row $i of $($ExcelData.count)..." -NoNewline
        #Add to SharePoint Online List
        $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
        $ListItem = $List.AddItem($ListItemInfo)
        
        #Set Task Name - Title
        $ListItem["Title"] = $Row.'Task Name'
        
        #Set Start Date & Due Date Fields
        $ListItem["StartDate"] = [DateTime]::parseexact($Row.'Start Date', "dd/mm/yyyy", $null)
        $ListItem["DueDate"] =  [DateTime]::parseexact($Row.'Due Date', "dd/mm/yyyy", $null)

        #Completed Percentage
        $ListItem["PercentComplete"] = $Row.'% Complete'

        #Priority and Task status drop down values
        $ListItem["Priority"] = $Row.Priority
        $ListItem["Status"] = $Row.'Task Status'

        #Assigned To - People Picker Field
        $ListItem["AssignedTo"] = $Ctx.web.EnsureUser($Row.'Assigned To')

        $ListItem.Update()
        $Ctx.ExecuteQuery()
        Write-host -f Green "Done!"
        $i++
    }
}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}

请务必在运行脚本之前更改 Excel 工作表的参数和名称。