[VB.NET] การค้นหาข้อมูลใน Excel ด้วยการใช้ DataTable

thongkorn

ในการนำไฟล์ MS Excel มาทำการบันทึกข้อมูลในแบบ Back End DataBase มันก็คงจะไม่ค่อยเหมาะสมเท่าไหร่นัก แต่หากมีข้อมูลที่ไม่ได้เยอะแยะมากมาย ก็คงจะพอกล้อมแกล้มไปได้ โดยทำการติดต่อกับไฟล์ Excel ในรูปแบบการติดต่อกับไฟล์ฐานข้อมูล (ใช้ ADO.NET) และมอง Sheet เสมือน Table หรือตารางข้อมูลนั่นเอง สำหรับโค้ดชุดนี้จะเป็นการค้นหาข้อมูลใน Sheet ของ Excel โดยนำข้อมูลในแต่ละแถวมาเก็บลง DataTable เอาไว้ก่อน จากนั้นก็ค่อยทำการค้นหาข้อมูลใน DataTable แทน ... [VB.NET] การนำไฟล์ MS Excel มาทำงานเป็นเหมือนฐานข้อมูล (DataBase Excel) ... รอบหน้าค่อยเป็นการอัพเดตข้อมูลในแต่ละแถวรายการครับผม ...

มาดูโค้ดกันเถอะ ...

1. Imports System.Data.OleDb
   
2. 
   
3. Public Class frmSearchExcelDataTable
   
4. 
   
5.     Dim DT As New DataTable
   
6. 
   
7.     ' / --------------------------------------------------------------------
   
8.     ' / เลือกไฟล์ Excel
   
9.     Private Sub btnOpenExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnOpenExcel.Click
   
10.         '/ ประกาศใช้งาน Open File Dialog ในแบบ Run Time
    
11.         Dim dlgOpenFile As OpenFileDialog = New OpenFileDialog()
    
12. 
    
13.         ' / ตั้งค่าการใช้งาน Open File Dialog
    
14.         With dlgOpenFile
    
15.             .InitialDirectory = MyPath(Application.StartupPath)
    
16.             .Title = "เลือกไฟล์ MS Excel"
    
17.             .Filter = "Excel Files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
    
18.             .FilterIndex = 0
    
19.             .RestoreDirectory = True
    
20.         End With
    
21.         '/ หากเลือกปุ่ม OK หลังจากการ Browse ...
    
22.         If dlgOpenFile.ShowDialog() = DialogResult.OK Then
    
23.             txtFileName.Text = dlgOpenFile.FileName
    
24.             Dim strConn As String = _
    
25.                 " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
    
26.                 dlgOpenFile.FileName & ";" & _
    
27.                 " Extended Properties=""Excel 12.0 Xml; HDR=YES"";"
    
28.             Dim Conn As New OleDbConnection(strConn)
    
29.             Conn.Open()
    
30.             '/ มอง WorkSheet ให้เป็นตารางข้อมูล (Table)
    
31.             Dim dtSheets As DataTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
    
32.             Dim drSheet As DataRow
    
33.             '//
    
34.             cmbSheetName.Items.Clear()
    
35.             '/ นำรายชื่อ WorkSheet ทั้งหมด มาเก็บไว้ที่ ComboBox เพื่อรอให้ User เลือกนำไปใช้งาน
    
36.             For Each drSheet In dtSheets.Rows
    
37.                 cmbSheetName.Items.Add(drSheet("TABLE_NAME").ToString)
    
38.             Next
    
39.             Conn.Close()
    
40.         End If
    
41.     End Sub
    
42. 
    
43.     ' / --------------------------------------------------------------------
    
44.     '// เลือก WorkSheet แล้วแสดงผลข้อมูลลงในตารางกริด
    
45.     Private Sub cmbSheetName_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSheetName.SelectedIndexChanged
    
46.         Dim Conn As OleDbConnection
    
47.         Dim Cmd As OleDbCommand
    
48.         Dim DA As New OleDbDataAdapter
    
49.         Dim DS As DataSet
    
50.         Dim strConn As String = _
    
51.             " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
    
52.             txtFileName.Text & ";" & _
    
53.             " Extended Properties=""Excel 12.0 Xml; HDR=YES"";"
    
54.         Try
    
55.             dgvData.DataSource = Nothing
    
56.             DT = New DataTable
    
57.             Conn = New OleDbConnection
    
58.             Conn.ConnectionString = strConn
    
59.             Cmd = New OleDbCommand
    
60.             '/ เสมือน WorkSheet เป็น Table ในฐานข้อมูล
    
61.             Cmd.CommandText = "Select * FROM [" & cmbSheetName.Text & "]"
    
62.             Cmd.Connection = Conn
    
63.             DA.SelectCommand = Cmd
    
64.             DA.Fill(DT)
    
65.             DT.TableName = cmbSheetName.Text.Replace("$", "")
    
66.             dgvData.DataSource = DT
    
67.             '//
    
68.             lblRecCount.Text = "[จำนวน : " & dgvData.Rows.Count & " รายการ]"
    
69.         Catch ex As Exception
    
70.             MessageBox.Show(ex.Message)
    
71.         Finally
    
72.             Conn = Nothing
    
73.             Cmd = Nothing
    
74.             DA = Nothing
    
75.             DS = Nothing
    
76.         End Try
    
77.     End Sub
    
78. 
    
79.     ' / --------------------------------------------------------------------
    
80.     '// การค้นหาข้อมูลจาก DataTable
    
81.     Private Sub txtSearch_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtSearch.KeyPress
    
82.         '// ยังไม่ได้โหลดข้อมูลเข้ามา
    
83.         If DT.Rows.Count = 0 Then
    
84.             txtSearch.Clear()
    
85.             Return
    
86.         End If
    
87.         '// ตัดตัวอักขระที่ไม่พึงประสงค์ ที่มีผลต่อการค้นหาข้อมูล
    
88.         txtSearch.Text = txtSearch.Text.Trim.Replace("'", "").Replace("*", "").Replace("%", "")
    
89.         '// เช็คว่ากด Enter ในช่อง TextBox ที่ใช้ค้นหาหรือไม่
    
90.         If Asc(e.KeyChar) = 13 Then
    
91.             Dim DTSearch As New DataTable
    
92.             dgvData.DataSource = Nothing
    
93.             '// เพิ่มหลัก (Column) เข้ามาใหม่ให้กับ DataTable
    
94.             DTSearch.Columns.Add("ID", GetType(String))
    
95.             DTSearch.Columns.Add("Name", GetType(String))
    
96.             DTSearch.Columns.Add("Point", GetType(Integer))
    
97.             '// ค้นหาข้อมูลด้วย LIKE
    
98.             Dim result() As DataRow = DT.Select("NAME LIKE '*" & txtSearch.Text & "*'")
    
99.             '// หากพบข้อมูลก็ทำการลูปรายการแถวข้อมูล
    
100.             For Each row As DataRow In result
     
101.                 '// เพิ่มรายการแถวข้อมูลเข้าไปยัง DataTable
     
102.                 DTSearch.Rows.Add(row(0).ToString, row(1).ToString, row(2).ToString)
     
103.             Next
     
104.             dgvData.DataSource = DTSearch
     
105.             txtSearch.Clear()
     
106.             lblRecCount.Text = "[จำนวน : " & dgvData.Rows.Count & " รายการ]"
     
107.         End If
     
108.     End Sub
     
109. 
     
110.     Private Sub dgvData_DoubleClick(sender As Object, e As System.EventArgs) Handles dgvData.DoubleClick
     
111.         If dgvData.Rows.Count = 0 Then Return
     
112.         MessageBox.Show("คุณเลือก: " & vbCrLf & _
     
113.                         "ID = " & dgvData.SelectedRows(0).Cells(0).Value.ToString & vbCrLf & _
     
114.                         "NAME = " & dgvData.SelectedRows(0).Cells(1).Value.ToString & vbCrLf & _
     
115.                         "POINT = " & dgvData.SelectedRows(0).Cells(2).Value.ToString)
     
116.     End Sub
     
117. 
     
118.     Private Sub frmSearchExcelDataTable_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
     
119.         Call SetupDGVData()
     
120.         lblRecCount.Text = "[จำนวน : 0 รายการ]"
     
121.     End Sub
     
122. 
     
123.     ' / --------------------------------------------------------------------
     
124.     '// Initialize DataGridView @Run Time
     
125.     Private Sub SetupDGVData()
     
126.         With dgvData
     
127.             .RowHeadersVisible = False
     
128.             .AllowUserToAddRows = False
     
129.             .AllowUserToDeleteRows = False
     
130.             .AllowUserToResizeRows = False
     
131.             .MultiSelect = False
     
132.             .SelectionMode = DataGridViewSelectionMode.FullRowSelect
     
133.             .ReadOnly = True
     
134.             .Font = New Font("Tahoma", 9)
     
135.             ' Autosize Column
     
136.             .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
     
137.             '// Even-Odd Color
     
138.             .AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue
     
139.             ' Adjust Header Styles
     
140.             With .ColumnHeadersDefaultCellStyle
     
141.                 .BackColor = Color.Navy
     
142.                 .ForeColor = Color.Black ' Color.White
     
143.                 .Font = New Font("Tahoma", 9, FontStyle.Bold)
     
144.             End With
     
145.         End With
     
146.     End Sub
     
147. 
     
148.     ' / ------------------------------------------------------------------
     
149.     ' / ฟังค์ชั่นที่เราสามารถกำหนด Path ให้กับโปรแกรมของเราเอง
     
150.     ' / Ex.
     
151.     ' / AppPath = C:\My Project\bin\debug
     
152.     ' / Replace "\bin\debug" with ""
     
153.     ' / Return : C:\My Project\
     
154.     Function MyPath(AppPath As String) As String
     
155.         AppPath = AppPath.ToLower()
     
156.         MyPath = AppPath.Replace("\bin\debug", "").Replace("\bin\release", "")
     
157.         '// If not found folder then put the \ (BackSlash ASCII Code = 92) at the end.
     
158.         If Microsoft.VisualBasic.Right(MyPath, 1) <> Chr(92) Then MyPath = MyPath & Chr(92)
     
159.     End Function
     
160. 
     
161.     Private Sub btnExit_Click(sender As System.Object, e As System.EventArgs) Handles btnExit.Click
     
162.         Me.Close()
     
163.     End Sub
     
164. 
     
165.     Private Sub frmSearchExcelDataTable_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
     
166.         Me.Dispose()
     
167.         GC.SuppressFinalize(Me)
     
168.         Application.Exit()
     
169.     End Sub
     
170. 
     
171. End Class

คัดลอกไปที่คลิปบอร์ด

ดาวน์โหลดโค้ดต้นฉบับ VB.NET (2010) ได้จากที่นี่ ...