我正在尝试用户powershell将Excel电子表格转换为CSV,以下是我正在使用的功能:
Function ConvertCSV{
Write-Host "Converting the list to CSV..."
$sExcelFile=$inputfile
$sCSVFile="C:Users%username%DesktopCACL.csv"
#Get COM Object
$oExcel = New-Object -ComObject "Excel.Application"
#Should Excel be visible?
$oExcel.Visible = $true
#and open excel file
$oExcelDoc = $oExcel.Workbooks.Open($sExcelFile)
# Open 1st Worksheet
$oWorksheet = $oExcelDoc.Worksheets.item(1)
#Activate, show it
$oWorksheet.Activate()
$oExcelDoc.SaveAs($sCSVFile,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)
$oExcelDoc.Close($false)
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null
$oWorksheet=$null
Start-Sleep 1
#Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null
$oExcelDoc=$null
#Close Excel
$oExcel.Quit()
Start-Sleep 1
#Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null
$oExcel=$null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
}
ConvertCSV
但是,当我运行它并加载电子表格时,它说它无法打开"CALC.csv",因为它已损坏。下面是Powershell报告的错误。
使用"2"参数调用"SaveAs"的异常:"工作簿类的 SaveAs 方法失败">
行:17 字符:5
$oExcelDoc.SaveAs($sCSVFile,[Microsoft.Office.Interop.Excel.XlFileFormat]::x ... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + 类别信息 : 未指定: (:)[], 方法调用异常
FullQualifiedErrorId : ComMethodTargetInvocation
您的问题是PowerShell无法扩展%USERNAME%变量。 PowerShell 使用不同的语法来访问环境变量。 在您的情况下,请尝试将路径从:
$sCSVFile="C:Users%username%DesktopCACL.csv"
自:
$sCSVFile="$env:USERPROFILEDesktopCACL.csv"