如何在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屋！