Hi,
I am getting the error below when running my script to export contents to an Excel worksheet. It seems to error after this line: Close-ExcelPackage -ExcelPackage $Excel
Error:
Exception calling "Save" with "0" argument(s): "Object reference not set to an instance of an object."
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.5.2\Public\Close-ExcelPackage.ps1:29 char:20
+ else { $ExcelPackage.Save() }
+ ~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : NullReferenceException
Here’s the script:
#------------------------------------------------------------------------------
# Parameters
#------------------------------------------------------------------------------
param (
[string]$HostIP = "xxx.xxx.xxx.xxx",
[string]$User = "psadmin",
[string]$Pass = "password",
[string]$FileName = "netapp_config_TEMPLATE.xlsx"
)
#------------------------------------------------------------------------------
# Set PowerShell to use TLSv1.2
#------------------------------------------------------------------------------
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
#------------------------------------------------------------------------------
# Import module(s)
#------------------------------------------------------------------------------
Import-Module ImportExcel
#Import-Module NetApp.ONTAP
Import-Module DataONTAP
#------------------------------------------------------------------------------
# Excel file path
#------------------------------------------------------------------------------
$TemplateExcelFile = $FileName
Remove-Item -force $TemplateExcelFile
#------------------------------------------------------------------------------
# Create Excel file
#------------------------------------------------------------------------------
$Excel = Open-ExcelPackage -Path "$TemplateExcelFile" -Create
Add-Worksheet -ExcelPackage $Excel -WorksheetName blank -MoveToEnd
Close-ExcelPackage -ExcelPackage $Excel
#------------------------------------------------------------------------------
# Define Excel function - open Excel, add new worksheet and write out properites
#------------------------------------------------------------------------------
function Add-ExcelWorksheet {
# Open Excel
$Excel = Open-ExcelPackage -Path $TemplateExcelFile
# Add new worksheet tab
Add-Worksheet -ExcelPackage $Excel -WorksheetName $WorkSheet -MoveToEnd
# Write out properties to Excel
$ObjectProperties | Export-Excel -ExcelPackage $Excel -AutoSize -WorksheetName $WorkSheet -Show -NoNumberConversion *
Close-ExcelPackage -ExcelPackage $Excel
}
#------------------------------------------------------------------------------
# Connect to Cluster
#------------------------------------------------------------------------------
#Create credentials
$ssPass = ConvertTo-SecureString -String $Pass -AsPlainText -Force
$Credential = New-Object System.Management.Automation.PSCredential -Argumentlist $User,$ssPass
#Connect to cluster using credentials
Connect-NcController -Name $HostIP -HTTPS -Credential $Credential
#------------------------------------------------------------------------------
# Set the authentication header.
#------------------------------------------------------------------------------
# Base64 technique to encode ('encrypt') credentials
$EncodedAuthorization = [System.Text.Encoding]::UTF8.GetBytes($Credential.username + ':' + $Credential.GetNetworkCredential().password)
$EncodedPassword = [System.Convert]::ToBase64String($EncodedAuthorization)
$Headers = @{"Authorization"="Basic $($EncodedPassword)"}
#------------------------------------------------------------------------------
# Cluster properties
#------------------------------------------------------------------------------
# Set URI variable for retrieving properties
[String]$Uri = "https://$HostIP/api/cluster?fields=**&return_records=true&return_timeout=15"
$WorkSheet = "Cluster"
Try{
# Trigger REST API call and store in a variable
$UriResponse = Invoke-RestMethod -Method GET -Uri $Uri -Headers $Headers -ErrorAction Stop
# Select properties and format PSCustomObject which otherwise display 'System.Object[]' and store as a variable
$ObjectProperties = $UriResponse | Select-Object -Property name,location,
@{Name='version';Expression={$_.version.full}},
@{Name='dns_domains';Expression={[string]::join(";",($_.dns_domains))}},
@{Name='name_servers';Expression={[string]::join(";",($_.name_servers))}},
@{Name='ntp_servers';Expression={[string]::join(";",($_.ntp_servers))}},
@{Name='timezone';Expression={$_.timezone.name}}
}
Catch{
Write-Warning -Message $("Failed enumerating properties from storage using URI ""$uri"". Error " + $_.Exception.Message + ". Status Code " + $_.Exception.Response.StatusCode.value__)
Break;
}
# FUNCTION: Open Excel, add new worksheet and write out properities
Add-ExcelWorksheet
Thanks in advance