VB.NET中如何将动态创建文本框的值导入Excel工作表

Got it, let's walk through how to get those dynamic textbox values into Excel. First, I notice your current code for creating textboxes isn't actually adding them to the form—so let's fix that first, then move on to extracting values and writing to Excel.

1. Fix Dynamic TextBox Creation #

Your existing loop declares textboxes but doesn't add them to the form's control collection, which means they won't be visible or accessible later. Here's how to properly create and add them:

Imports excel = Microsoft.Office.Interop.Excel
Imports System.Data.OleDb

Public Class Form4
    Dim x As Integer

    Public Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Validate input to avoid errors
        If Not Integer.TryParse(InputBox("Enter No of factors"), x) Then
            MessageBox.Show("Please enter a valid whole number.")
            Return
        End If

        ' Create and add dynamic textboxes to the form
        For i As Integer = 1 To x
            Dim dynamicTextBox As New TextBox()
            dynamicTextBox.Name = $"DynamicTextBox_{i}" ' Unique name for easy filtering
            dynamicTextBox.Location = New Point(30, 30 + (i * 35)) ' Space vertically
            dynamicTextBox.Size = New Size(250, 22)
            dynamicTextBox.PlaceholderText = $"Factor {i}" ' Optional hint text
            Me.Controls.Add(dynamicTextBox) ' Add to form so it's visible/accessible
        Next
    End Sub

2. Extract Values from Dynamic TextBoxes #

To grab the values, we'll loop through the form's controls, filter for our dynamic textboxes (using the name prefix we set), and collect their text into a list:

Private Function GetDynamicTextBoxValues() As List(Of String)
    Dim values As New List(Of String)()
    For Each ctrl As Control In Me.Controls
        ' Only target our named dynamic textboxes
        If TypeOf ctrl Is TextBox AndAlso ctrl.Name.StartsWith("DynamicTextBox_") Then
            values.Add(DirectCast(ctrl, TextBox).Text)
        End If
    Next
    Return values
End Function

3. Write Values to Excel Worksheet #

Now we'll use Excel Interop to write the collected values to a worksheet. Important: Always clean up COM objects to avoid leaving Excel processes running in the background.

Add a button to your form (e.g., btnExportToExcel) and use this click event:

Private Sub btnExportToExcel_Click(sender As Object, e As EventArgs) Handles btnExportToExcel.Click
    Dim textBoxValues = GetDynamicTextBoxValues()
    If textBoxValues.Count = 0 Then
        MessageBox.Show("No values to export!")
        Return
    End If

    ' Initialize Excel objects
    Dim excelApp As New excel.Application()
    Dim workbook As excel.Workbook = excelApp.Workbooks.Add()
    Dim worksheet As excel.Worksheet = DirectCast(workbook.ActiveSheet, excel.Worksheet)

    ' Write values to Excel (start at cell A1)
    For i As Integer = 0 To textBoxValues.Count - 1
        worksheet.Cells(i + 1, 1).Value = textBoxValues(i)
        worksheet.Cells(i + 1, 1).Font.Size = 11 ' Optional formatting
    Next

    ' Make Excel visible and save (update the path to your desired location)
    excelApp.Visible = True
    workbook.SaveAs("C:\Your\File\Path\DynamicValues.xlsx")

    ' Clean up COM objects critical to avoid leftover Excel processes
    ReleaseComObject(worksheet)
    ReleaseComObject(workbook)
    ReleaseComObject(excelApp)
End Sub

' Helper method to safely release COM objects
Private Sub ReleaseComObject(ByVal obj As Object)
    Try
        If obj IsNot Nothing Then
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        End If
    Catch ex As Exception
        obj = Nothing
    End Try
End Sub

Important Notes #

• Add Excel Reference: Right-click your project > References > Add Reference > COM > Select "Microsoft Excel xx.x Object Library" (match your Excel version).
• Error Handling: You might want to add try-catch blocks around the Excel code to handle file save errors or missing Excel installations.
• Filtering: Using the DynamicTextBox_ prefix ensures you only collect values from the textboxes you created dynamically, not other textboxes on the form.

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