excel - 内存不足，无法继续执行程序。从 CSV 创建 Xlsx

Question

我有一个脚本，它循环浏览一个文件夹并将多个 CSV 压缩为一个 xlsx 文件，其中 CSV 的名称作为工作表。但是，当脚本作为较大脚本的一部分运行时，它会在刷新查询时失败。

$Query.Refresh()

脚本本身运行良好，但是当添加到较大的脚本时它会失败。谁能告诉我为什么会这样？

以下是我得到的错误:

Insufficient memory to continue the execution of the program.At C:\Temp\Scripts\Shares_Complete.psm1:254 char:13+             $Query.Refresh()+             ~~~~~~~~~~~~~~~~    + CategoryInfo          : OperationStopped: (:) [], OutOfMemoryException    + FullyQualifiedErrorId : System.OutOfMemoryException

I have tried single csv with the same code and still the same result.

$script:SP = "C:\Temp\Servers\"

$script:TP = "C:\Temp\Servers\Pc.txt"
$script:FSCSV = "C:\Temp\Server_Shares\Server Lists\"
$script:Message1 = "Unknown Hosts"
$script:Message2 = "Unable to connect"
$script:Message3 = "Unknown Errors Occurred"
$script:Txt = ".txt"
$script:OT = ".csv"
$script:FSERROR1 = $FSCSV+$Message1+$OT
$script:FSERROR2 = $FSCSV+$Message2+$OT
$script:FSERROR3 = $FSCSV+$Message2+$OT

$script:ERL3 = $E4 + "Shares_Errors_$Date.txt"
$script:ECL1 = $E4 + "Shares_Exceptions1_$Date.txt"
$script:ERL1 = $E4 + "Shares_Errors1_$Date.txt"
$script:ECL3 = $E4 + "Shares_Exceptions_$Date.txt"

function Excel-Write {
    if ($V -eq "1") {
        return
    }

    [System.GC]::Collect()
    $RD = $FSCSV + "*.csv" 
    $CsvDir = $RD 
    $Ma4 = $FSCSV + "All Server Shares for Domain $CH4"
    $csvs = dir -path $CsvDir # Collects all the .csv's from the driectory 
    $FSh = $csvs | Select-Object -First 1
    $FSh = ($FSh -Split "\\")[4]
    $FSh = $FSh -replace ".{5}$"
    $FSh
    $outputxls = "$Ma4.xlsx"
    $script:Excel = New-Object -ComObject Excel.Application
    $Excel.DisplayAlerts = $false
    $workbook = $excel.Workbooks.Add()
    # Loops through each CVS, pulling all the data from each one
    foreach ($iCsv in $csvs) {
        $script:iCsv
        $WN = ($iCsv -Split "\\")[-1]
        $WN = $WN -replace ".{4}$"
        if ($WN.Length -gt 30) {
            $WN = $WN.Substring(0, [Math]::Min($WN.Length, 20))
        }
        $Excel = New-Object -ComObject Excel.Application
        $Excel.DisplayAlerts = $false
        $Worksheet = $workbook.Worksheets.Add()
        $Worksheet.Name = $WN

        $TxtConnector = ("TEXT;" + $iCsv)
        $Connector = $worksheet.Querytables.Add($txtconnector,$worksheet.Range("A1"))
        $query = $Worksheet.QueryTables.Item($Connector.Name)

        $query.TextfileOtherDelimiter = $Excel.Application.International(5)

        $Query.TextfileParseType = 1
        $Query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Column.Count
        $query.AdjustColumnWidth = 1

        $Query.Refresh()
        $Query.Delete()
        $Worksheet.Cells.EntireColumn.AutoFit()
        $Worksheet.Rows.Item(1).Font.Bold = $true
        $Worksheet.Rows.Item(1).HorizontalAlignment = -4108
        $Worksheet.Rows.Item(1).Font.Underline = $true
        $Workbook.Save()
    }
    $Empty = $workbook.Worksheets.Item("Sheet1")
    $Empty.Delete()
    $Workbook.SaveAs($outputxls,51)
    $Workbook.Close()
    $Excel.Quit()
    $ObjForm.Close()
    Delete
}

应该继续编写脚本并创建 xlsx。

Answer 1

查看您的脚本，您最终会耗尽内存并不让我感到惊讶，因为您一直在创建 Com 对象并且从不从内存中释放它们。

每当您创建 Com 对象并完成它们时，使用这些行来释放内存:

$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

另外，好好看看代码。
您正在创建 $Script:Excel = New-Object -ComObject excel.application foreach 之前的对象循环，但你不使用它。相反，您在循环中一遍又一遍地创建新的 Excel 和工作簿对象，而这绝对没有理由这样做，因为您可以重复使用在循环之前创建的对象。

顺便说一句:excel工作表名称中不允许使用以下字符

\/?*[]:

长度限制为 31 个字符。

编辑

我看了你的项目，尤其是 Shares_Complete.psm1文件。
虽然我当然不愿意重写你的整个项目，但我确实有一些评论可以帮助你:

[System.Net.Dns]::GetHostByName()已过时。使用GetHostEntry()

使用 Windows 窗体完成后，使用 $ObjForm.Dispose()从内存中清除它

你做了很多[System.GC]::Collect(); [System.GC]::WaitForPendingFinalizers()无缘无故

为什么不使用 [System.Windows.MessageBox]::Show()而不是使用 Com 对象 $a = new-object -comobject wscript.shell .再一次，你让那个对象在内存中挥之不去..

使用 Join-Path cmdlet 而不是 $RD = $FSCSV + "*.csv"或 $Cop = $FSCSV + "*.csv"构造

从 Excel 工作表名称中删除无效字符 (replace '[\\/?*:[\]]', '')

使用 Verb-Noun为您的函数命名，以便清楚它们的作用。现在你有像 Location 这样的函数。 , Delete和 File没有任何意义

您在调用函数之前调用函数，如在第 65 行调用函数 Shares .那时它还不存在，因为函数本身写在第 69 行

添加 [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null在功能 Excel-Write

无需使用变量 $Excel在脚本范围 ( $Script:Excel = New-Object -ComObject excel.application ) 中，它仅在函数本地使用。

您可能需要查看 Excel specifications and limits

修复代码缩进，以便在循环或 if 开始和结束时清晰可见

我建议使用更有意义的变量名。几个月后，对于局外人甚至您自己来说，两个字母的变量名变得困惑