从Excel列检索数据写入单个临时文件的VB.NET代码问题

已实现从Excel检索目标数据的功能，但程序运行后会生成大量临时文件。初步定位问题为自定义写入函数每次执行都会新建文件、关闭文件流，而Do Until循环会重复触发写入流程，最终生成多个分散的临时文件。需要实现所有数据写入同一个临时文件、全部写入完成后再关闭文件的逻辑。

按钮点击事件逻辑 #

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        'Set the excel logic. 
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim strFilename As String 
        xlApp = New Excel.Application

        'Select .xlsm file with file dialog box and set to TraceSelection.Text
        If IO.File.Exists(TraceSelection.Text) Then xlWorkBook = xlApp.Workbooks.Open(TraceSelection.Text)
        xlWorkBook = xlApp.ActiveWorkbook

        'Populate Temp File.
        Dim trace As Object
        trace = xlWorkBook.Sheets("2020-12-16_12-07-12_781")

        Dim i As Integer
        Dim testing As String
        'Dim pgn_hex_value As String
        i = 2 'Starting from two since the first row is the header
        Do Until trace.Range("E" & i) Is ""
            testing = trace.Range("E" & i).Value
            strFilename = WriteToTempFile(testing)
            i = i + 1
        Loop
        MessageBox.Show(strFilename)
    End Sub

原临时文件写入函数 #

Public Function WriteToTempFile(ByVal Data As String) As String
        ' Writes text to a temporary file and returns path
        Dim strFilename As String = System.IO.Path.GetTempFileName()
        Dim objFS As New System.IO.FileStream(strFilename, System.IO.FileMode.Append, System.IO.FileAccess.Write)
        ' Opens stream and begins writing
        Dim Writer As New System.IO.StreamWriter(objFS)
        Writer.BaseStream.Seek(0, System.IO.SeekOrigin.End)
        Writer.WriteLine(Data)
        Writer.Flush()
        ' Closes and returns temp path
        Writer.Close()
        Return strFilename
    End Function

• 原WriteToTempFile函数每次被调用时，都会执行System.IO.Path.GetTempFileName()生成全新的临时文件路径，这是产生多文件的核心原因
• 函数每次仅写入单条数据就立刻关闭文件流，没有复用同一个文件句柄
• 循环中每次调用写入函数都会覆盖strFilename变量，最终弹窗只能展示最后一次生成的临时文件路径

核心调整逻辑：

• 进入遍历循环前仅创建一次临时文件，初始化对应的文件流和写入器，不要在单次写入的逻辑中重复创建文件
• 循环遍历Excel行的过程中，直接向已打开的文件流写入数据，不中途关闭流
• 所有数据写入完成后，再统一执行缓冲区刷新、流关闭、资源释放操作
• 额外补充Excel COM对象释放逻辑，避免程序运行后后台残留Excel进程

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    '初始化Excel相关对象
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim strFilename As String 
    xlApp = New Excel.Application

    '打开选中的xlsm目标文件
    If IO.File.Exists(TraceSelection.Text) Then 
        xlWorkBook = xlApp.Workbooks.Open(TraceSelection.Text)
    End If
    xlWorkBook = xlApp.ActiveWorkbook

    '定位要读取的目标工作表
    Dim trace As Object
    trace = xlWorkBook.Sheets("2020-12-16_12-07-12_781")

    '循环前一次性创建临时文件，初始化可复用的文件写入流
    strFilename = System.IO.Path.GetTempFileName()
    Dim objFS As New System.IO.FileStream(strFilename, System.IO.FileMode.Append, System.IO.FileAccess.Write)
    Dim Writer As New System.IO.StreamWriter(objFS)

    Dim i As Integer = 2 '从第2行开始读取，跳过第1行表头
    Do Until trace.Range("E" & i).Value Is Nothing
        Dim testing As String = trace.Range("E" & i).Value.ToString()
        '向已打开的流写入当前行数据，不新建文件、不关闭流
        Writer.WriteLine(testing)
        i = i + 1
    Loop

    '所有数据写入完成后，统一刷新缓冲区、关闭文件流
    Writer.Flush()
    Writer.Close()
    objFS.Close()

    '释放Excel COM资源，避免后台残留Excel进程
    ReleaseComObject(trace)
    xlWorkBook.Close(False)
    ReleaseComObject(xlWorkBook)
    xlApp.Quit()
    ReleaseComObject(xlApp)

    MessageBox.Show($"数据写入完成，临时文件路径：{strFilename}")
End Sub

'COM对象释放辅助方法
Private Sub ReleaseComObject(ByVal obj As Object)
    Try
        If obj IsNot Nothing Then
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        End If
    Catch
        obj = Nothing
    Finally
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try
End Sub

注：原有WriteToTempFile函数可直接移除，文件创建和写入逻辑已经整合到按钮点击事件的完整流程中，避免重复创建文件的问题。

内容的提问来源于stack exchange，提问作者Lord45