在vb.net的Excel工作表单元格中显示文本框结果 [英] Display the textboxes results in Excel sheet cells in vb.net

问题描述

我在表单上有两个文本框；一个保存单词，另一个保存其数值，该数值是根据用户键入的内容计算出来的.我有一个子句，用于分隔每个句子并在Excel中显示每个句子的总数.这是从下面列出的vb.net代码生成的输出.所有单词都在A列中，所有值都在B列中.如何修改代码，以便将每个句子及其值插入两列?在到达字符串总计为句子1"之后.它应开始在C&列中插入单词及其值.D等.句子一及其值应显示在列A&中.B，句子二及其值应显示在列C&中.D等.请参阅所附图片.红色文字是我当前拥有的内容，下面是您想要的结果.谢谢！

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!

我在vb.net中的代码是:

My Code in vb.net is:

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

推荐答案

我对该代码进行了重大更改并对其进行了测试；它应该可以满足您的需求

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

这篇关于在vb.net的Excel工作表单元格中显示文本框结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！