在 vb.net 中的 Excel 工作表单元格中显示文本框结果

Display the textboxes results in Excel sheet cells in vb.net

提问by user2536008

I have two textboxes on a form; one hold the word and the other one holds its numeric value that is calculated based on what the user types. I have a sub that separates each sentence and displays the total for each sentence in Excel. This is the output that being generated from the vb.net code which is listed below. All the words are in A column and all the values in B column. How do I modify the code so it inserts each sentence and its value in two columns? After it reaches the string "Total for sentence 1." it should start inserting the words and its values in columns C & D and so forth. Sentence one and its value should be displayed in columns A & B, sentence two and its value should be displayed in columns C & D and so on. Please see the attached image. Text in red is what I currently have and below it is the desired result. Thanks!

我在一个表单上有两个文本框；一个保存单词，另一个保存根据用户键入的内容计算的数值。我有一个子程序，用于分隔每个句子并在 Excel 中显示每个句子的总数。这是从下面列出的 vb.net 代码生成的输出。所有单词都在 A 列中，所有值都在 B 列中。如何修改代码，使其在两列中插入每个句子及其值？在它到达字符串“Total for sentence 1”之后。它应该开始在 C 列和 D 列中插入单词及其值，依此类推。句子一及其值应显示在 A 列和 B 列中，句子二及其值应显示在 C 列和 D 列中，依此类推。请参阅所附图片。红色文本是我目前所拥有的，其下方是所需的结果。谢谢！

My Code in vb.net is:

我在 vb.net 中的代码是：

Private Sub Export_Click(sender As System.Object, e As System.EventArgs) Handles btnCreate.Click

    xlWorkBook = xlApp.Workbooks.Add

    xlApp.Visible = True

    xlWorkSheet = xlWorkBook.Sheets("Sheet1")
    Dim columnANum As Integer
    Dim columnBNum
    columnBNum = 2
    columnANum = 2
    With xlWorkSheet
        .Range("A1").Value = "Word"
        For Each cellA As String In txtWord.Text.Split(vbLf)

            .Range("A" & columnANum.ToString).Value = cellA
            columnANum += 1
        Next
        .Range("B1").Value = "Value"
        For Each cellB As String In txtValue.Text.Split(vbLf)
            .Range("B" & columnBNum.ToString).Value = Convert.ToInt32(cellB)
            columnBNum += 1
        Next


    End With
End Sub

回答by D_Bester

EDIT: I made significant changes to this code and tested it; it should do what you're looking for

编辑：我对此代码进行了重大更改并对其进行了测试；它应该做你正在寻找的

Sub testExcelColumns()
    Dim xlApp As Object = CreateObject("Excel.Application")
    Dim xlWorkBook As Object
    Dim xlWorkSheet As Object

    xlWorkBook = xlApp.Workbooks.Add
    xlApp.Visible = True

    xlWorkSheet = xlWorkBook.Sheets("Sheet1")
    Dim RowNum As Integer = 1
    Dim ColNum As Integer = 1

    'use .cells(row, column) instead of .range
    xlWorkSheet.cells(RowNum, ColNum).Value = "Word"

    For Each cellA As String In txtWord.Text.Split(vbLf)

        'increment the row
        RowNum += 1

        'set the value
        xlWorkSheet.cells(RowNum, ColNum).value = cellA

        If cellA.StartsWith("Total") Then
            ColNum += 2
            RowNum = 1
            xlWorkSheet.cells(RowNum, ColNum).Value = "Word"
        End If

    Next

    'increment to the second column and first row
    ColNum = 2
    RowNum = 1

    'set title
    xlWorkSheet.cells(RowNum, ColNum).Value = "Value"

    For Each cellB As String In txtValue.Text.Split(vbLf)

        'increment the row
        RowNum += 1

        'set the value
        xlWorkSheet.cells(RowNum, ColNum).value = cellB

        'get value of cell to the left
        Dim t As String = xlWorkSheet.cells(RowNum, ColNum).offset(0, -1).value
        If Not t Is Nothing AndAlso t.StartsWith("Total") Then
            ColNum += 2
            RowNum = 1
            xlWorkSheet.cells(RowNum, ColNum).Value = "Value"
        End If

    Next

End Sub

回答by D_Bester

Try using

尝试使用

Dim RowNum as Integer = 1
Dim ColNum as Integer = 1

use .cells instead of .range

使用 .cells 而不是 .range

xlWorkSheet.Cells(RowNum, ColNum).value = "Word"
xlWorkSheet.Cells(RowNum, ColNum).value = "Value"

update the row and column as needed

根据需要更新行和列

RowNum += 1
ColNum += 1