001.Imports System.Data.OleDb
002.Imports System.Linq
003.Imports System.Data.SqlClient
004.Imports Microsoft.Office.Core
005.Imports Excel = Microsoft.Office.Interop.Excel
006.Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat
007.Imports Microsoft.Office.Interop
008.Imports System.IO
009.Imports System.Xml.XPath
010.Imports System.Data
011.Imports System.Xml
012.
013.Public Class Form1
014. Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\MyData.MDB")
015. Dim CMD As OleDbCommand
016. Dim dr As OleDbDataReader
017. Dim da As OleDbDataAdapter
018. Dim ds As DataSet
019. Dim SQL As String
020. Dim DelByid As String
021. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
022.
023. Me.CenterToScreen()
024.
025.
026. End Sub
027.
028. Sub LoadInfo()
029. SQL = "SELECT * FROM Table1"
030. CMD = New OleDbCommand(SQL, Conn)
031. Try
032. If Conn.State = ConnectionState.Open Then Conn.Close()
033. Conn.Open()
034.
035. dr = CMD.ExecuteReader
036. DataGridView1.Rows.Clear()
037. Dim iD As Integer = 1
038. While dr.Read
039. DataGridView1.Rows.Add(iD, dr("XX").ToString, dr("YY").ToString)
040. iD += 1
041.
042. End While
043. Conn.Close()
044. CMD.Dispose()
045.
046. Catch ex As Exception
047. MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
048.
049. End Try
050. End Sub
051.
052. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
053.
054. Call LoadInfo()
055.
056. End Sub
057.
058. Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
059.
060. Dim iconn As OleDbConnection
061. Dim idta As OleDbDataAdapter
062. Dim idts As DataSet
063. Dim excel As String
064. Dim OpenFileDialog As New OpenFileDialog
065.
066. OpenFileDialog1.FileName = ""
067. OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
068. OpenFileDialog1.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
069.
070. If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
071. DataGridView2.Columns.Clear()
072.
073. Dim fi As New FileInfo(OpenFileDialog1.FileName)
074. Dim FileName As String = OpenFileDialog1.FileName
075.
076. excel = fi.FullName
077. iconn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
078. idta = New OleDbDataAdapter("Select * From [Sheet1$]", iconn)
079. idts = New DataSet
080. idta.Fill(idts, "[Sheet1$]")
081. DataGridView2.DataSource = idts
082. DataGridView2.DataMember = "[Sheet1$]"
083. iconn.Close()
084. End If
085.
086. End Sub
087.
088. Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
089. If DataGridView1.Rows.Count > 0 And DataGridView2.Rows.Count > 0 Then
090. DataGridView3.Rows.Clear()
091.
092. Dim row As Integer = DataGridView1.Rows.Count
093. For i As Integer = 0 To row - 1
094. DataGridView3.Rows.Add(DataGridView1.Rows(i).Cells(0).Value, DataGridView1.Rows(i).Cells(1).Value + DataGridView2.Rows(i).Cells(1).Value, DataGridView1.Rows(i).Cells(2).Value + DataGridView2.Rows(i).Cells(2).Value)
095.
096. Next
097. End If
098.
099.
100. End Sub
101.
102. Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
103. If DataGridView3.Rows.Count = 0 Then
104. Exit Sub
105. Else
106. Call SaveToData()
107. End If
108. End Sub
109.
110. Sub SaveToData()
111.
112. Try
113. If Conn.State = ConnectionState.Open Then Conn.Close()
114. Conn.Open()
115.
116. Dim i As Integer = 0
117. For Each row As DataGridViewRow In DataGridView3.Rows
118. If Not row.IsNewRow Then
119. SQL = "SELECT * FROM Table1"
120. CMD = New OleDbCommand(SQL, Conn)
121.
122.
123.
124. CMD.CommandText = "UPDATE Table1 SET XX=" & DataGridView3.Rows(i).Cells(1).Value & ",YY=" & DataGridView3.Rows(i).Cells(2).Value
125. CMD.ExecuteNonQuery()
126.
127. End If
128. Next
129.
130. Conn.Close()
131. CMD.Dispose()
132. MessageBox.Show("Save All Data", "!!!", MessageBoxButtons.OK, MessageBoxIcon.Information)
133.
134. Catch ex As Exception
135. MessageBox.Show(ex.Message, "!!!", MessageBoxButtons.OK, MessageBoxIcon.Error)
136.
137. End Try
138.
139. End Sub
140.
141.End Class
