Powershell代码无法识别当前位置的所有excel文件(*.xlsm)

Question

我目前正在Powershell上处理excel文件(*.xlsm(。问题是下面的代码只能读取"test.xlsm"。当名称不是像"this.xlsm"那样测试时，该代码无法读取文件。有什么帮助吗。。。？感谢您提前回答：(

$destination = "C:JJ"
$dirName = Get-ChildItem -Name -Filter *.xlsm
$saveAs = $destination + "new"
foreach($z in $dirName){
$excel=New-Object -ComObject Excel.Application
$excel.visible=$false
$excel.Displ`ayAlerts=$false
$book=$excel.Workbooks.Open($destination + $z)
$sheet=$book.Worksheets.item(1)  
$sheet.Cells.Item(1,5)="=max(B2:B6)"
$book.SaveAs($saveAs + $z)
$excel.Quit()
$excel=$null
}

Answer 1

您在代码中使用了令人困惑的变量名。。为什么要调用源路径$destination？？

无论如何，您应该在Get-ChildItemcmdlet上使用-File开关，以确保它只返回FileInfo对象，而不返回DirectoryInfo对象。(这些是对象，而不是字符串(

然后，有一种更好的方法来构建路径。使用Join Path，而不是像使用$destination + $z那样串联。

最后，我只会在循环之前创建一次Excel对象，然后清理内存。现在，您在每次迭代中都要创建新的COM对象，并且永远不会从内存中释放它们。

下面的代码应该做你想做的：

$source = "C:JJ"
$destination = Join-Path -Path $source -ChildPath 'new'
# test if the destination path already exists and if not, create it
if (!(Test-Path -Path $destination -PathType Container)) {
$null = New-Item -Path $destination -ItemType Directory
}
# create the Excel COM object outside the loop
$excel = New-Object -ComObject Excel.Application
$excel.visible = $false
$excel.DisplayAlerts = $false
# get all *.xlsm files inside the source folder
Get-ChildItem -Path $source -Filter '*.xlsm' -File | ForEach-Object {
# The $_ automatic variable represents 1 FileInfo object in each iteration
$book  = $excel.Workbooks.Open($_.FullName)
$sheet = $book.Worksheets.item(1)  
$sheet.Cells.Item(1,5) = "=max(B2:B6)"
# join the destination path and the file name for output
$saveAs = Join-Path -Path $destination -ChildPath $_.Name
$book.SaveAs($saveAs)
$book.Close()
}
# cleanup Com objects
$excel.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($book)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
$excel = $null

注意，如果您的PowerShell版本低于3.0，则-File开关不可用。相反，使用

Get-ChildItem -Path $source -Filter '*.xlsm' | Where-Object {!$_.PSIsContainer} | ForEach-Object { ... }

Answer 2

# remove -name to get a directory object instead of a string
$dirName = Get-ChildItem -Filter *.xlsm
# you need the file name from the directory object listing
foreach($z in $dirName.name){ ...... }