excel - 方法调用异常 Powershell

问题描述

我正在尝试在 PS 7.1.0 中导入 excel 模块，但出现以下错误：
MethodInvocationException: C:\Temp\Deploy-iDMZ\Modules\ImportExcel\7.1.1\Public\Export-Excel.ps1:672 行 | 第672章否则 { $pkg.Save() } | ~~~~~~~~~~~ | 使用“0”参数调用“保存”的异常：“保存文件时出错 | C:\Temp\Deploy-iDMZ\iDMZ-Parameters-XYZ1234.xlsx”
这是我的代码：
     # Attempt to load the require modules All the modules are included as nested
# modules in smallville.psd1
param(
    # Path to IDC parameters spreadsheet
    [Parameter(Mandatory=$false,Position=0)]
    [string]$Path,
    # Skip hardware validation checks
    [Parameter(Mandatory=$false)]
    [switch]$SkipHWChecks,
    # Use customer AD
    [Parameter(Mandatory=$false)]
    [string]$ExistingDomain
)

$scriptDir = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
$LogFile = "$scriptDir\raidc.log"

$env:PSModulePath = "$scriptDir\Modules " + ';' + $env:PSModulePath

try {
    Write-Host "Loading modules..." -NoNewline
    Write-Host "CSM-Shared..." -NoNewline
    Import-Module -Name CSM-Shared -MinimumVersion 3.7.0
    Write-Host "ImportExcel..."
    Import-Module -Name ImportExcel -MinimumVersion 7.0.1
    Write-Host "QrCodes..." -NoNewline
    Import-Module -Name QrCodes -MinimumVersion 1.2.0.80
    Write-Host "VMware.PowerCLI..."
    try {
        Import-Module -Name "VMware.PowerCLI" -MinimumVersion 12.1.0 -ErrorAction SilentlyContinue
    }
    catch {
        # do nothing but eat the exceptions from PowerCLI failing to load one or more of it's sub modules that are not compatable with PS.core.
    }
    
    $ErrorActionPreference = 'Stop'
    $pCliConfig = Set-PowerCLIConfiguration -InvalidCertificateAction Ignore -ParticipateInCeip $false -Scope Session -DisplayDeprecationWarnings $false -DefaultVIServerMode Single -Confirm:$false
    Write-LogDebug -InputObject $pCliConfig
    Write-Host "Done." 
} catch {
    Write-Host -ForegroundColor Red -Object "Failed to load one or more modules."
    throw $_
}

# Display the banner graphic
Get-Content -Path $scriptDir\ra-idc-ascii.txt
#region Import and/or generate excel data
Write-LogMessage -Level DBG -Message "### Script Start"
# Import the parameters files
if ($PSBoundParameters.ContainsKey("Path")) {
    # Verify that the file exists
    try {
        $UnitParametersFile = Get-ChildItem -Path $Path -ErrorAction Stop
    } catch {
        Write-LogException -ErrorRecord $_ -Message "File $Path is inaccessible."
        throw $_
    }
    if ($UnitParametersFile.BaseName -eq "RAIDC-Parameters-Template") {
        Write-LogMessage -Level ERR -Message "Attempting to use parameters template as inpute file, aborting."
        throw "418 I'm a teapot, just kidding`r`n406 Not Acceptable"
    }
    try {
        # Open the file, fail if files cannot be opened
        $ExcelPackage = Open-ExcelPackage -Path $Path -ErrorAction Stop
        
        # if file opens, verify that required worksheets are present
        $worksheets = $ExcelPackage | Get-Member -Type "ScriptProperty"
        if (!($worksheets.name -contains "Common" -and
              $worksheets.name -contains "Networks" -and
              $worksheets.name -contains "Switches" -and
              $worksheets.name -contains "Hosts" -and
              $worksheets.name -contains "Infrastructure" -and
              $worksheets.name -contains "AppVMs" )){
                Write-LogMessage -Level ERR -Message "Required worksheets missing from specified file"
                throw "422 Unprocessable Entity"
            }
    } 
    catch {
        Write-LogException -ErrorRecord $_ -Message "Failed to process $Path"
        throw $_
    } 
    finally {
        Close-ExcelPackage -ExcelPackage $ExcelPackage -ErrorAction SilentlyContinue 
    }

    # attempt to pull the unit specific information like serial number and license keys
    $Unit = Import-Excel -Path $Path -WorksheetName "Unit" -HeaderName "name","value" -StartRow 2  -ErrorAction SilentlyContinue

    if ($null -eq $unit) {
        # Either there is no unit worksheet or the workbook in inaccessable. If it's
        # the later, we should have already failed and exited so we will assume the
        # workbook is just missing this sheet.
        $Unit = New-UnitParametersFile
        # If we're successful generating the unit parameters and we have a valid password to save, output to the excel workbook
        $UnitUsage = [pscustomobject]@{name="Usage";value="The fields below are exported from the Deploy-RAIDC script and contain the system specific parameters, including VMware serial numbers and default administrative password"}
        Export-Excel -Path $Path -WorksheetName "Unit" -InputObject $UnitUsage -AutoSize -StartRow 1 -StartColumn 1 -ClearSheet -NoHeader -MoveToStart -Activate
        Export-Excel -Path $Path -WorksheetName "Unit" -InputObject ($Unit | ConvertFrom-HashTable | Sort-Object -Property "Name") -AutoSize -StartRow 2 -NoHeader -MoveToStart -Activate
        # Get the actual [System.Security.SecureString] object for the password that we'll need elsewhere.
        $Unit.SecurePassword = ConvertFrom-PlainText -String $Unit.Password

    } else {
        $Unit = ConvertTo-HashTable -InputArray $Unit
        $Unit.SecurePassword = ConvertFrom-PlainText -String $Unit.Password
    }

    $Unit.SecurePassword = ConvertFrom-PlainText -String $Unit.Password
} else {
    # If there's no workbook specified, we'll build one
    $unit = New-UnitParametersFile
    try {
        $Path = "$scriptDir\RAIDC-Parameters-$($Unit.Serial).xlsx"
        Copy-Item -Path "$scriptDir\RAIDC-Parameters-Template.xlsx" -Destination $Path
    }
    catch {
        Write-LogException -ErrorRecord $_ -Message "Failed to copy template into new file"
        throw
    }
    # If we're successful generating the unit parameters and we have a valid password to save, output to the excel workbook
    $UnitUsage = [pscustomobject]@{name="Usage";value="The fields below are exported from the Deploy-RAIDC script and contain the system specific parameters, including VMware serial numbers and default administrative password"}
    Export-Excel -Path $Path -WorksheetName "Unit" -InputObject $UnitUsage -AutoSize -StartRow 1 -StartColumn 1 -ClearSheet -NoHeader -MoveToStart -Activate
    Export-Excel -Path $Path -WorksheetName "Unit" -InputObject ($Unit | ConvertFrom-HashTable | Sort-Object -Property "Name") -AutoSize -StartRow 2 -NoHeader -MoveToStart -Activate
    # Get the actual [System.Security.SecureString] object for the password that we'll need elsewhere.
    $Unit.SecurePassword = ConvertFrom-PlainText -String $Unit.Password
}
# Load the unit parameters from file
Write-LogDebug -InputObject $Unit
try {
    # Import raw data from Excel workbook
    $Common = Import-Excel -Path $Path  -WorksheetName "Common" -HeaderName "name","value" -StartRow 2 | ConvertTo-Hashtable
    Write-LogDebug -InputObject $Common
    $Switches = Import-Excel -Path $Path  -WorksheetName "Switches" -StartRow 2
    Write-LogDebug -InputObject $Switches
    $Networks = Import-Excel -Path $Path  -WorksheetName "Networks" -StartRow 2
    Write-LogDebug -InputObject $Networks
    $Hosts = Import-Excel -Path $Path  -WorksheetName "Hosts" -StartRow 2
    Write-LogDebug -InputObject $Hosts
    $Infrastructure = Import-Excel -Path $Path  -WorksheetName "Infrastructure" -HeaderRow 2
    Write-LogDebug -InputObject $Infrastructure
}
catch {
    Write-LogException -ErrorRecord $_ -Message "Failed to import base data from parameters workbook"
}

#endregion

ConvertTo-QRCode -InputObject (ConvertTo-PlainText $Unit.SecurePassword) | Format-QRCode
ConvertTo-PlainText $Unit.SecurePassword | Out-BarcodeImage -BarcodeFormat QR_CODE -Path "$PWD\Serial-$($Unit.Serial).png"

#region common validation
# If not specified, assume that the user is properly synchronizing
# their IDC toolkit and look for the files in their profile folder.
# NOTE: This default will need to get updated once the Toolkit is republished for Gen3.5
if ($null -eq $Common.ToolkitFolder) {
    $ToolkitFullPath = "$env:USERPROFILE\Rockwell Automation, Inc\NSS - Industrial Data Center\Binaries"
} else {
    try {
        $ToolkitFullPath = (Get-ItemProperty -Path $Common.Toolkitfolder).FullName.TrimEnd('\')
    }
    catch {
        Write-LogException -ErrorRecord $_ -Message "Failed to validate location of IDC Toolkit"
        throw $_
    }
}
$cwd = $pwd
Set-Location -Path $ToolkitFullPath
# Get the path to each required file and verify that they exist
$vCenterISO = (Get-ChildItem -Path $ToolkitFullPath -Filter "*-VCSA-*.iso" | Select-Object -First 1).FullName
if ($null -eq $vCenterISO) { Write-LogMessage -Level ERR -Message "vCenter installation ISO not found in $ToolkitFullPath"}
$witnessOVA = (Get-ChildItem -Path $ToolkitFullPath -Filter "*-VirtualSAN-Witness-*.ova" | Select-Object -First 1).FullName
if ($null -eq $witnessOVA) { Write-LogMessage -Level ERR -Message "Witness installation OVA not found in $ToolkitFullPath"}
$NetSvcsOVA = (Get-ChildItem -Path $ToolkitFullPath -Filter "NetSvcs.ova" | Select-Object -First 1).FullName
if ($null -eq $NetSvcsOVA) { Write-LogMessage -Level ERR -Message "NetSvcs installation OVA not found in $ToolkitFullPath"}
#$BackupISO = (Get-ChildItem -Path $ToolkitFullPath -Filter "*BE-DVD*.iso").FullName
#if ($null -eq $BackupISO) { Write-LogMessage -Level ERR -Message "BackupExec installation ISO not found in $ToolkitFullPath"}
$BackupFolder = "$ToolkitFullPath\Backup\"
$TemplateFolder = "$ToolkitFullPath\Templates\"
Set-Location -Path $cwd.Path



$NtpServers = @($Common.NtpServer.Split(',').Trim())
[ipaddress]$tmpOutput = 0.0.0.0
foreach ($ntp in $NtpServers) {
    if (!([ipaddress]::TryParse($ntp,[ref]$tmpOutput))) {
        Write-LogMessage -Level ERR -Message "NTP server $ntp is not a valid IP address"
        Throw
    }
}

$DnsServers = @($Common.DnsServer.Split(',').Trim())
[ipaddress]$tmpOutput = 0.0.0.0
foreach ($dns in $dnsServers) {
    if (!([ipaddress]::TryParse($dns,[ref]$tmpOutput))) {
        Write-LogMessage -Level ERR -Message "NTP server $dns is not a valid IP address"
        Throw
    }
}
#endregion

# As we convert the data from Excel to match the hash tables and
# array format expected by the scripts, lets validate that we have 
# good configuration data.

#region networks validation
# First validate that there's no missing rows in the networks worksheet
$tmpMgmt = $Networks | Where-Object { $_.Name -eq "Management Network" }
if ($null -eq $tmpMgmt) {
    Write-LogMessage -Level ERR -Message "No management network defined in IDC parameters sheet. Please check the networks tab and ensure one entry is named `"Management Network`""
    Throw "No Management network"
标签： excelpowershellpowercli
excel - 方法调用异常 Powershell

问题描述

解决方案

推荐阅读
