首页 文章

VB.net/Excel-“Backwards”选项卡索引For Each iteration with textboxes

提问于
浏览 次
1

我有一个包含3个文本框和1个按钮的表单 .

textbox1的选项卡索引为0，而text = 1

textbox2的标签索引为1，文本= 2

textbox3的标签索引为2，文本= 3

我想通过文本框进行迭代并将它们的值放入单元格中，以便...

范围（“A1”） . value = txtbox1.text（即：A1 =“1”）范围（“A2”） . value = txtbox2.text（即：A2 =“2”）范围（“A3”） . 值= txtbox3.text（即：A3 =“3”）

但我得到的是......

范围（“A1”） . value = txtbox1.text（即：A1 =“3”）范围（“A2”） . value = txtbox2.text（即：A2 =“2”）范围（“A3”） . 值= txtbox3.text（即：A3 =“1”）

我试过反转文本框的选项卡索引，但它不会改变“向后迭代” .

有什么我可以做的改变这个，以便循环从最低标签索引到最高？

谢谢！

公共类Form1

Private Sub Button1_Click_1（ByVal sender As System.Object，ByVal e As System.EventArgs）Handles Button1.Click

Dim objExcel As New Microsoft.Office.Interop.Excel.Application 'Declaring the object.
  objExcel.Visible = True 'Setting Excel to visible.

  Dim cntrl As Control

  With objExcel
     .Workbooks.Add() 'Adding a workbook.
     .Range("A1").Select() 'Selecting cell A1.
  End With


  'Form contains 3 text boxes, with one number in each (1,2,3), and one button to fire the code in this sub.

  For Each cntrl In Me.Controls 'For every control on the form...
     If TypeOf (cntrl) Is TextBox Then 'If the control is a textbox, then...
        With objExcel
           .ActiveCell.Value = cntrl.Text 'place the control's text in the active cell and...
           .ActiveCell.Offset(1, 0).Activate() 'offset down one row.
        End With
     End If 'If the control is not a textbox (if it's the button), do nothing.
  Next 'Go to the next control.

  objExcel = Nothing 'Release the object.
  GC.Collect() 'Clean up.

结束子结束类

vb.net excel

2 回答

  • 0

    听起来它可能是Excel迭代控件的方式 . 你试过这个看看输出是什么吗？

    Dim objExcel As New Microsoft.Office.Interop.Excel.Application 'Declaring the object.
  objExcel.Visible = True 'Setting Excel to visible.

  Dim cntrl As Control

  With objExcel
     .Workbooks.Add() 'Adding a workbook.
     .Range("A3").Select() 'Selecting cell A3.
  End With


  'Form contains 3 text boxes, with one number in each (1,2,3), and one button to fire the code in this sub.

  For Each cntrl In Me.Controls 'For every control on the form...
     If TypeOf (cntrl) Is TextBox Then 'If the control is a textbox, then...
        With objExcel
           .ActiveCell.Value = cntrl.Text 'place the control's text in the active cell and...
           .ActiveCell.Offset(-1, 0).Activate() 'offset up one row.
        End With
     End If 'If the control is not a textbox (if it's the button), do nothing.
  Next 'Go to the next control.

  objExcel = Nothing 'Release the object.
  GC.Collect() 'Clean up.
    回复于
  • 0

    使用 For Each 循环意味着您不关心您在执行它们的顺序 .

    我要做的是使用控件的“tag”属性来保存您想要答案的行号：

    For Each cntrl In Me.Controls 
  If TypeOf (cntrl) Is TextBox Then 
    objExcel.ActiveSheet.Cells(cntrl.Tag, 1).Value = cntrl.Text 
  End If 
Next

    您还可以在表单上添加一个仅包含关键文本框的隐藏面板，然后使用传统的FOR循环而不是“For Each”：

    Dim i as integer
Dim myBox as textBox
For i = 1 to 3
    set myBox = Me.Panel1.Controls(i)
    objExcel.ActiveSheet.Cells(myBox.Tag, 1).Value = myBox.Text 
Next i

    它应该以Tab顺序进行 . 你甚至可以跳过类型检查，因为你已经知道它是什么了 .

    回复于

相关问题