PowerShell XLS到CSV的转换问题 [英] powershell XLS to CSV conversion issue

问题描述

我正在运行下面的代码，将XLS转换为CSV，但收到奇怪的错误。谁能给我指个正确的方向，知道是什么导致了这个问题？ 我使用的是Powershell3.0版。不确定我在这里遗漏了什么。

PARAM
    (
        [parameter(Mandatory = $true)]
        [string]
        $excelFilePath = $(throw "Must give a valid Excel (*.xls) file path.")
        ,

        [parameter(Mandatory = $true)]
        [int]
        $leadingRowsToDelete = $(throw "Must specify how many leading rows to delete.")
        ,

        [parameter(Mandatory = $false)]
        [string]
        $csvFilePath = $null
        ,

        [parameter(Mandatory = $false)]
        [int]
        $xlCSV = 6
    )


    if(! $csvFilePath)
    {
        $csvFilePath = $excelFilePath -replace ".xls$", ".csv"
    }

    $Excel = New-Object -Com Excel.Application -Property @{Visible = $false} 
    $Excel.displayalerts=$False

    $WorkBook = $Excel.Workbooks.Open($excelFilePath) # Open the file
    $Sheet = $WorkBook.Sheets.Item(1) # Activate the first worksheet

    # Delete the first $leadingRowsToDelete rows
    for($i = 1; $i -le $leadingRowsToDelete; $i ++)
    {
        [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
    }

    $WorkBook.SaveAs($csvFilePath, $xlCSV)

    $Excel.quit()

我收到的错误消息是：

Exception calling "Open" with "1" argument(s): "The server threw an exception. (Exception from HRESULT: 0x80010105
(RPC_E_SERVERFAULT))"
At \multinasdub301SoftwareSysAdminWebDownloadsXLS-To-CSV.ps1:74 char:1
+ $WorkBook = $Excel.Workbooks.Open($excelFilePath) # Open the file
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

You cannot call a method on a null-valued expression.
At \multinasdub301SoftwareSysAdminWebDownloadsXLS-To-CSV.ps1:75 char:1
+ $Sheet = $WorkBook.Sheets.Item(1) # Activate the first worksheet
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \multinasdub301SoftwareSysAdminWebDownloadsXLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \multinasdub301SoftwareSysAdminWebDownloadsXLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \multinasdub301SoftwareSysAdminWebDownloadsXLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \multinasdub301SoftwareSysAdminWebDownloadsXLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \multinasdub301SoftwareSysAdminWebDownloadsXLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \multinasdub301SoftwareSysAdminWebDownloadsXLS-To-CSV.ps1:83 char:1
+ $WorkBook.SaveAs($csvFilePath, $xlCSV)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

推荐答案

根据OP的评论，问题是由目标EXCEL文件上的错误权限引起的。

脚本在具有完全权限的本地文件上运行正常。

这篇关于PowerShell XLS到CSV的转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！