我需要帮助找到PowerShell脚本中的错误

Question

试图让我的脚本工作，需要一些帮助在这里是我的代码。

#excel 
#open ap 
$XL = new-object -com "Excel.Application" 
$XLbooks = $XL.workbooks 
$netci = [system.Globalization.CompareInfo]"en-us" 
$wkbk = $XLbooks.PSBase.GetType().Invokemember("Add",[Reflection.BindingFlags]::InvokeMethod,$null,$XLbooks,$null,$newci) 
$sheet = $XLbooks.worksheets.item(1) 
$sheet.name = "name" 
$sheet.cells.item($row,1).formulalocal = "Fred Nurk" 
$file = "c\windows\scripts\test.xlsx" 
[void]$wkbk.PSBase.GetType().InvokeMember("SaveAs",[Reflection.BindingFlags]::InvokeMethod,$null,$wkbk,$file,$newci) 
("Close",[Reflection.BindingFlags]::Invokemedthod,$null,$wkbk,0,$newci) 
$XL.quit() 

错误：

Cannot convert the "en-us" value of type "System.String" to type "System.Globalization.CompareInfo". 
At C:\scripts\test.ps1:5 char:44 
+ $netci = [system.Globalization.CompareInfo] <<<< "en-us" 
    + CategoryInfo   : NotSpecified: (:) [], RuntimeException 
    + FullyQualifiedErrorId : RuntimeException 

You cannot call a method on a null-valued expression. 
At C:\scripts\test.ps1:7 char:34 
+ $sheet = $XLbooks.worksheets.item <<<< (1) 
    + CategoryInfo   : InvalidOperation: (item:String) [], RuntimeException 
    + FullyQualifiedErrorId : InvokeMethodOnNull 

Property 'name' cannot be found on this object; make sure it exists and is settable. 
At C:\scripts\test.ps1:8 char:8 
+ $sheet. <<<< name = "name" 
    + CategoryInfo   : InvalidOperation: (name:String) [], RuntimeException 
    + FullyQualifiedErrorId : PropertyNotFound 

You cannot call a method on a null-valued expression. 
At C:\scripts\test.ps1:9 char:18 
+ $sheet.cells.item <<<< ($row,1).formulalocal = "Fred Nurk" 
    + CategoryInfo   : InvalidOperation: (item:String) [], RuntimeException 
    + FullyQualifiedErrorId : InvokeMethodOnNull 

Exception calling "InvokeMember" with "6" argument(s): "Microsoft Excel cannot access the file 'C:\Users\Jared\Document 
s\c\windows\scripts\5ADD7000'. There are several possible reasons: 
The file name or path does not exist. 
The file is being used by another program. 
The workbook you are trying to save has the same name as a currently open workbook." 
At C:\scripts\test.ps1:11 char:42 
+ [void]$wkbk.PSBase.GetType().InvokeMember <<<< ("SaveAs",[Reflection.BindingFlags]::InvokeMethod,$null,$wkbk,$file,$n 
ewci) 
    + CategoryInfo   : NotSpecified: (:) [], MethodInvocationException 
    + FullyQualifiedErrorId : DotNetMethodTargetInvocation 

Answer 1

你需要解决的是创建CompareInfo的主要问题。第一个错误告诉你此行不工作：

$netci = [system.Globalization.CompareInfo]"en-us" 

所以，你需要做的是创建CompareInfo对象是这样的：

$netci = ([system.Globalization.CultureInfo]"en-us").CompareInfo 

虽然不是使用这个疯狂的方式创建一个工作簿：

$wkbk = $XLbooks.PSBase.GetType().Invokemember("Add",[Reflection.BindingFlags]::InvokeMethod,$null,$XLbooks,$null,$newci) 

...尝试，而不是该更理智的方式：d

$wkbk = $XL.workbooks.Add() 

如果你这样做，你不必担心创建CompareInfo对象。

Answer 2

跳出来的问题是$netci = [system.Globalization.CompareInfo]"en-us"是无效的语法。你没有调用System.Globalization.CompareInfo类的任何方法，你只是在它后面放置一个字符串。 PowerShell将[System.Globalization.CompareInfo]解释为类型转换运算符，并且抱怨字符串“en-us”无法转换为数据类型System.Globalization.CompareInfo - 因为该数据类型不存在。

您需要调用在“en-us”上运行的方法。您可以从MSDN获得的方法列表：

http://msdn.microsoft.com/en-us/library/system.globalization.compareinfo.aspx

假设你想用的方法GetCompareInfo（似乎最有可能，我 - 它返回一个CompareInfo对象）时，你会写这行是这样的：

$netci = [System.Globalization.CompareInfo]::GetCompareInfo('en-us') 

注意，顺便说一句，你有这样的变量作为$netci当您创建它，但作为脚本的其余部分$newci。

我没有看太深的其他错误，但他们可能是由于未能正确创建$ newci而产生的级联效应，所以我怀疑如果你修复这个错误，其他错误将会消失。