如何在Excel工作表中搜索文本框值并将col B返回到col F到其他文本框 [英] How to search textbox value in excel sheet col a and return col B to col F to other textboxes
问题描述
我是Vb.Net的新手,我学会了如何从excel获取数据到datagridview。现在有人可以帮助我学习如何将Excel数据导入文本框吗?
我有一个包含6个文本框和一个搜索按钮的表单。当我给出textbox1值并单击搜索按钮时,我想搜索由opendialogfile选择并保存在destination1字符串中的excel文件
Hi,
I'm new to Vb.Net and I have learnt how to get data from excel to a datagridview. Now can anyone help me learn how to get excel data into textboxes?
I have a form with 6 textboxes and a search button. When I give textbox1 value and click on search button, I want to search an excel file which is selected by opendialogfile and saved in destination1 string
destination1 = destipath & Filename.Text & "_" & dtedate.ToShortDateString & ".xlsx" '& Format(TimeOfDay, "h.mm.ss tt")
excel sheet1有ID,Name,Father,母亲,地址,电话和VB.net表单分别有6个/ b $ b文本框。例如,当我在textbox1中输入ID为30时,代码必须在col A中搜索ID 30,如果找到则文本框2,3,4,5,6必须显示姓名,父亲,母亲,地址,电话。
工作表行是动态的,但是col是固定的。我在excel vba中编写了以下代码,但是如何在VB.net中实现类似的呢?
我尝试了什么:
The excel sheet1 has ID,Name,Father,Mother,Address,Phone and VB.net form has 6
textboxes respectively. Example when I enter i.e ID as 30 in textbox1, the code must search ID 30 in col A if found then textbox 2,3,4,5,6 must display Name,Father,Mother,Address,Phone.
The sheet row are dynamic but col are fixed. I have written the following code in excel vba but how can I implement similarly in VB.net?
What I have tried:
Dim FoundRange As Range
Hide Expand Copy Code
Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Public class Test
Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
Dim xlappFile As Excel.Application = Nothing
Dim xlFile_WB As Excel.Workbook = Nothing
Dim xlFile_WS As Excel.Worksheet = Nothing
Dim xlFirstfile_WS1 As Excel.Worksheet = Nothing
Dim FoundRange As Excel.Range
Dim searchID As String
searchID = Textbox1.Text
xlFile_WB = xlappFile.Workbooks(destination1) ' destination1 is selected by 'user
MessageBox.Show(destination1)
xlFile_WS = xlappFile.Worksheets(1)
FoundRange = xlFile_WS.Cells.Find(What:=searchID, LookIn:=Excel.XlFindLookIn.xlFormulas, LookAt:=Excel.XlLookAt.xlWhole)
If FoundRange Is Nothing Then
textbox2.Text = "not found"
textbox3.Text = "not found"
textbox4.Text = "not found"
textbox5.Text = "not found"
textbox6.Text = "not found"
Else
textbox2.Text = FoundRange.Offset(0, 2).Value
textbox3.Text = FoundRange.Offset(0, 3).Value
textbox4.Text = FoundRange.Offset(0, 4).Value
textbox5.Text = FoundRange.Offset(0, 5).Value
textbox6.Text = FoundRange.Offset(0, 6).Value
End If
End Sub
End Class
推荐答案
在VB.NET中你需要使用 Microsoft.Office.Interop。 EXC el namespace() [ ^ ]。
In VB.NET you would need to use the Microsoft.Office.Interop.Excel namespace ()[^].
这篇关于如何在Excel工作表中搜索文本框值并将col B返回到col F到其他文本框的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!