我目前正在開發一個將 excel 檔案從 .xls 轉換為 .xlsx 的 powershell 腳本
準確地說，我需要它以某種方式作業：
我需要從檔案夾中捕獲 .xls 檔案并將其復制到備份檔案夾
將它們轉換為 .xlsx 并將它們上傳到上傳檔案夾
將它們從檔案夾轉換并將它們上傳到另一個檔案夾作業正常，但我嘗試添加一些功能，現在我被卡住了。
這是我嘗試運行時的錯誤：
At C:\Users\Test\Conv_XLS_V2.ps1:40 char:2
}
~ The Try statement is missing its Catch or Finally block. At C:\Users\Test\Conv_XLS_V2.ps1:20 char:16
ForEach-Object { ~
Missing closing '}' in statement block or type definition.
CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
FullyQualifiedErrorId : MissingCatchOrFinally
我的代碼：
# set folders
$downloadfolder = "C:\Users\Test"
#$downloadfolder = "folder that gets the .xls files"
$uploadfolder = "C:\Users\Test\Upload"
#$uploadfolder = "folder that uploads the .xlsx files"
$backupfolder = "C:\Users\Test\Backup"
#$backupfolder = "folder that has .xls files as backup"
#open and convert xls to xlsx
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype -recurse |
ForEach-Object {
try {
$xlsfilename = $_.fullname
#copy file to backup folder
Copy-Item -Path $xlsfilename -Destination $backupfolder
# open the xls
Write-Output "Converting $xlsfilename"
$workbook = $excel.workbooks.open($xlsfilename)
# save converted file (as xlsx)
$xlsxfilename = $xlsfilename "x"
$workbook.saveas($xlsxfilename, $xlFixedFormat)
$workbook.close()
#remove old file
Write-Output "delete & move file(s)"
Remove-Item -Path $xlsfilename -Force
Move-Item -Path $xlsxfilename -Destination $uploadfolder -Force
# garbage collection
[gc]::collect()
[gc]::WaitForPendingFinalizers()
}
# close excel
$excel.Quit()
$excel = $null
請問有人可以看看嗎？
uj5u.com熱心網友回復：
錯誤資訊很清楚。您忘記try{..}用結束括號關閉塊，}并且 atry{..}后面應該跟一個或多個catch{..}塊和可選的finally{..}塊。
你可以閱讀關于Try Catch finally 的內容。
然后，您的代碼中還有其他一些錯誤和/或可以改進的地方。
$folderpath未定義，應為源檔案夾$downloadfolder- 使用
-Filter而不是-Include因為它要快得多。您還遺漏了“*.xls”中的點 - 將開關附加
-File到 Get-ChildItem cmdlet 以確保您不會接收并嘗試處理目錄 - 您可以將轉換后的.xlsx檔案直接保存到upload檔案夾中，無需先創建再移動
- 要洗掉使用過的 COM 物件，首先將它們從記憶體中釋放，然后啟動垃圾收集。在退出 Excel后
執行此操作。
# set folders
$downloadfolder = "C:\Users\Test" # folder where the .xls files are
$uploadfolder = "C:\Users\Test\Upload" # folder that uploads the .xlsx files
$backupfolder = "C:\Users\Test\Backup" # folder that has .xls files as backup
# open and convert xls to xlsx
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false # it is much faster if Excel is not visible
# loop through the .xls files and process them
Get-ChildItem -Path $downloadfolder -Filter '*.xls' -Recurse -File |
ForEach-Object {
try {
$xlsfilename = $_.FullName
#copy file to backup folder
Copy-Item -Path $xlsfilename -Destination $backupfolder -Force
# open the xls
Write-Host "Converting $xlsfilename"
$workbook = $excel.Workbooks.Open($xlsfilename)
# save converted file (as xlsx) directly to the upload folder
$newfilename = Join-Path -Path $uploadfolder -ChildPath ('{0}.xlsx' -f $_.BaseName)
$workbook.SaveAs($newfilename, $xlFixedFormat)
$workbook.Close()
#remove old file
Write-Host "Delete old file '$xlsfilename'"
Remove-Item -Path $xlsfilename -Force
}
catch {
# write out a warning as to why something went wrong
Write-Warning "Could not convert '$xlsfilename':`r`n$($_.Exception.Message)"
}
}
# close excel
$excel.Quit()
# garbage collection
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
uj5u.com熱心網友回復：
該錯誤描述了語法問題。您已經包含了一個try {陳述句，但沒有用} catch {}塊來關閉它。就這樣。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/366208.html