[VB.NET] การนำไฟล์ MS Excel มาทำงานเป็นเหมือนฐานข้อมูล (DataBase Excel)

thongkorn

ถามว่าให้ Excel ทำงานเป็นเหมือนฐานข้อมูล หรือ DataBase ได้หรือไม่ แอดมินตอบได้เลยว่าทำได้ครับ แต่ไม่ค่อยเหมาะสมเท่าใดนัก เพราะใช้งานผิดวัตถุประสงค์น่ะครับ แต่ในบางกรณีบางคนอาจจะมีชุดข้อมูลจาก Excel อยู่แล้ว ก็ไม่อยากจะย้ายข้อมูลไปเก็บที่ Access หรืออื่นๆ โค้ดชุดนี้แอดมินจะสาธิตและนำทางให้แบบเบื้องต้น คือ การเพิ่มข้อมูลเข้าไปใน Excel โดยใช้ ADO.NET แทน Excel Object ครับผม ...
มาดูโค้ดกันเถอะ ...

1. ' / --------------------------------------------------------------------------------
   
2. ' / Developer : Mr.Surapon Yodsanga (Thongkorn Tubtimkrob)
   
3. ' / eMail : thongkorn@hotmail.com
   
4. ' / URL: http://www.g2gnet.com (Khon Kaen - Thailand)
   
5. ' / Facebook: https://www.facebook.com/g2gnet (For Thailand)
   
6. ' / Facebook: https://www.facebook.com/commonindy (Worldwide)
   
7. ' / MORE: http://www.g2gnet.com/webboard
   
8. ' /
   
9. ' / Microsoft Excel is able to perform back ends provide.
   
10. ' / Microsoft Visual Basic .NET (2010)
    
11. ' /
    
12. ' / This is open source code under @Copyleft by Thongkorn Tubtimkrob.
    
13. ' / You can modify and/or distribute without to inform the developer.
    
14. ' / --------------------------------------------------------------------------------
    
15. Imports System.Data.OleDb
    
16. 
    
17. Public Class frmExcelDBBackEnd
    
18.     '// Path and File name of Excel
    
19.     Dim strFilename As String
    
20. 
    
21.     ' / --------------------------------------------------------------------------------
    
22.     ' / Get my project path
    
23.     ' / AppPath = C:\My Project\bin\debug
    
24.     ' / Replace "\bin\debug" with ""
    
25.     ' / Return : C:\My Project\
    
26.     Function MyPath(ByVal AppPath As String) As String
    
27.         '/ MessageBox.Show(AppPath);
    
28.         AppPath = AppPath.ToLower()
    
29.         '/ Return Value
    
30.         MyPath = AppPath.Replace("\bin\debug", "").Replace("\bin\release", "")
    
31.         '// If not found folder then put the \ (BackSlash) at the end.
    
32.         If Microsoft.VisualBasic.Right(MyPath, 1) <> "" Then MyPath = MyPath & ""
    
33.     End Function
    
34. 
    
35.     Private Function CreateConnString(ByVal strFilename As String) As String
    
36.         Return "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + strFilename + "; Extended Properties=""Excel 8.0;HDR=YES"";"
    
37.     End Function
    
38. 
    
39.     Private Sub frmExcelDBBackEnd_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
40.         Call ClearScreen()
    
41.         '// Create Excel File
    
42.         strFilename = MyPath(Application.StartupPath) + "Book1.xls"
    
43.         '// Check file exists.
    
44.         If Not (My.Computer.FileSystem.FileExists(strFilename)) Then
    
45.             '// Not found
    
46.             Dim Conn As New OleDbConnection
    
47.             Dim Comm As New OleDbCommand
    
48. 
    
49.             Conn.ConnectionString = CreateConnString(strFilename)
    
50.             Comm.Connection = Conn
    
51.             '// Create a new sheet consisting of three columns.
    
52.             Comm.CommandText = "Create Table MySheet (MemberID Char(255), Firstname Char(255), Lastname Char(255), MemberShip Char(255))"
    
53.             Try
    
54.                 Conn.Open()
    
55.                 Comm.ExecuteNonQuery()
    
56.                 MsgBox("Created MS Excel successful.")
    
57.                 Conn.Close()
    
58.             Catch ex As Exception
    
59.                 MsgBox("Error: " & ex.Message)
    
60.                 Conn.Close()
    
61.             End Try
    
62.         Else
    
63.             MessageBox.Show("The Excel file exists.", "Report status", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
64.         End If
    
65.     End Sub
    
66. 
    
67.     Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    
68.         If Trim(txtMemberID.Text.Length) = 0 Or Trim(txtMemberID.Text) = "" Then
    
69.             MessageBox.Show("Please enter Member ID.", "Report status", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    
70.             txtMemberID.Focus()
    
71.             Return
    
72.         End If
    
73. 
    
74.         Dim Conn As New OleDbConnection
    
75.         Dim Comm As New OleDbCommand
    
76.         Conn.ConnectionString = CreateConnString(MyPath(Application.StartupPath) + "Book1.xls")
    
77.         Comm.Connection = Conn
    
78.         Dim strSQL As String = _
    
79.                 " INSERT INTO [MySheet$] (" & _
    
80.                 " MemberID, Firstname, Lastname, MemberShip) " & _
    
81.                 " VALUES (" & _
    
82.                 "'" & txtMemberID.Text & "'," & _
    
83.                 "'" & txtFirstname.Text & "'," & _
    
84.                 "'" & txtLastname.Text & "'," & _
    
85.                 "'" & cmbMembership.Text & "'" & _
    
86.                 ")"
    
87.         Comm.CommandText = strSQL
    
88.         Try
    
89.             Conn.Open()
    
90.             Comm.ExecuteNonQuery()
    
91.             MsgBox("Inserted new record successful.")
    
92.             Conn.Close()
    
93.             '// Clear screen
    
94.             Call ClearScreen()
    
95. 
    
96.         Catch ex As Exception
    
97.             MsgBox("Error: " & ex.Message)
    
98.             Conn.Close()
    
99.         End Try
    
100.     End Sub
     
101. 
     
102.     Private Sub ClearScreen()
     
103.         '// Clear
     
104.         txtMemberID.Clear()
     
105.         txtFirstname.Clear()
     
106.         txtLastname.Clear()
     
107.         txtMemberID.Focus()
     
108.         '//
     
109.         With cmbMembership
     
110.             .Items.Clear()
     
111.             .Items.Add("Normal")
     
112.             .Items.Add("Level I")
     
113.             .Items.Add("Level II")
     
114.             .Items.Add("Level III")
     
115.             .Items.Add("VIP")
     
116.             .SelectedIndex = 0
     
117.         End With
     
118.     End Sub
     
119. 
     
120.     Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
     
121.         Me.Close()
     
122.     End Sub
     
123. 
     
124.     Private Sub frmExcelDBBackEnd_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
     
125.         Me.Dispose()
     
126.         Application.Exit()
     
127.     End Sub
     
128. 
     
129. End Class

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

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

anuyut1995

Eroror ตรงปุ่มบันทึกนะครับ แต่ ฟอร์ม Load ไม่error

ooddog

อาจารย์ครับ มีวิธีการเปลี่ยนจาก Excel file เป็น csv file ใหมครับ

thongkorn

ooddog ตอบกลับเมื่อ 2021-3-6 18:34
อาจารย์ครับ มีวิธีการเปลี่ยนจาก Excel file เป็น csv file ใหมครับ

ลองดูโค้ดตัวนี้ก่อนครับ แหล่งที่มา ... ปกติผมใช้ความสามารถของ Component เช่น DevExpress หรือของฟรี Syncfusion แทนครับ ง่ายกว่ากันเยอะ

''' <summary>
''' Create a csv file from the active worksheet. You can of course
''' change this by passing in a SheetName
''' </summary>
''' <param name="ExcelFileName">Excel File to work with</param>
''' <param name="CsvFileName">Output file name in csv format</param>
''' <remarks>
''' See also:
''' http://code.msdn.microsoft.com/Basics-of-using-Excel-4453945d
''' Has several examples to assist in modifying this code to accept a SheetName
''' and uses assertion to ensure a passed Sheet exists.
''' </remarks>
''' <example>Usage example:<code>
''' Create_ExcelFile_InCsvFormat(
'''     IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "YourFileName.xlsx"),
'''     IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "YourFileName.txt")
''' )
''' </code>
''' </example>
Public Sub Create_ExcelFile_InCsvFormat(ByVal ExcelFileName As String,
    ByVal CsvFileName As String)

    If IO.File.Exists(ExcelFileName) Then
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False

        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(ExcelFileName)

        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)

        xlWorkBook.SaveAs(CsvFileName, FileFormat:=Excel.XlFileFormat.xlCSVWindows)

        xlWorkBook.Close()

        xlApp.UserControl = True
        xlApp.Quit()

        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If

        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If

        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If

        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If
    Else
        MessageBox.Show("Failed to located " & ExcelFileName)
    End If
End Sub

thongkorn

อันนี้ของฟรีจาก Syncfusion ครับ How to export Excel data to CSV file? ...