powershell - 您不能使用 excel 在 powershell 中调用空值表达式的方法

Question

我已经开始用 excel 学习 PowerShell 并遇到空值表达式错误

#open excel application

$x1 = New-Object -comobject excel.application 

#open excel to show the result in realtime

$x1.Visible = $true

#open the already existing excel to edit

$test = $x1.Workbooks.Open("C:\Users\tushar.v\OneDrive - HCL Technologies Ltd\Documents\test.xlsx")

#to open a specific worksheet

$test2 = $test.worksheets.Item(1).Activate

$test2.Cells.Item(1,1) = "alphatext"

错误 ：

You cannot call a method on a null-valued expression.
At line:9 char:1
+ $test2.Cells.Item(1,1) = "alphatext"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

另外，我没有在 excel 中得到输出

标签： powershell

Answer 1

首先，我建议使用更好的变量名称，因此在较大的脚本中，每个变量都包含什么内容是很清楚的。$test并且$test2不是真正的描述性..

那么对于你所尝试的：

工作表的Activate()方法不会像您想象的那样返回引用已激活工作表的对象，因此您需要首先在变量中获取工作表对象并使用它来执行 Activate() 方法。

尝试：

# create excel application
$excel = New-Object -comobject excel.application 

# open excel to show the result in realtime
$excel.Visible = $true

# open the already existing excel to edit
$workbook  = $excel.Workbooks.Open("D:\Test\blah.xlsx")
# get a reference to the first worksheet in the file
$worksheet = $workbook.WorkSheets.Item(1)
# make this worksheet active
$worksheet.Activate()
# and add content to the cell
$worksheet.Cells.Item(1,1) = "alphatext"

最后，创建 COM 对象会消耗内存，因此在您的工作完成后，您需要告诉 Windows 它可以清理这些对象：

# Don't forget to quit Excel when done (after saving perhaps?) and to remove the created COM objects from memory:

$excel.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()