16-11-17, 12:57 AM
السلام عليكم ورحمة الله وبركاته
الرجاء المساعدة في تضبيط الكود حتي يمكن تصدير الجدول الي الاكسيل
الموجود قي ال Project directory
اسم ملف الاكسيل هو Test
[align=left]Imports
[size=small][font=Consolas][size=small][font=Consolas][color=#000000]System.Data.OleDb[color=#0000ff][color=#0000ff][size=small][font=Consolas]Imports
[size=small][font=Consolas][size=small][font=Consolas][color=#000000]Excel = Microsoft.Office.Interop.Excel[color=#0000ff][color=#0000ff][size=small][font=Consolas]Public
[color=#0000ff][size=small][font=Consolas][color=#0000ff][size=small][font=Consolas][color=#0000ff][size=small][font=Consolas]Class Form1
[size=small][font=Consolas][size=small][font=Consolas]' جملة الربط
[size=small][font=Consolas][size=small][font=Consolas]Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Code.accdb")
[size=small][font=Consolas][size=small][font=Consolas]Dim da As New OleDbDataAdapter ' جدول مصدر المعلومات
[size=small][font=Consolas][size=small][font=Consolas]Dim dt As New DataTable ' مكان التخزين
[size=small][font=Consolas][size=small][font=Consolas]Dim excelLocation As String = "D:\test.xlsx"
[size=small][font=Consolas][size=small][font=Consolas]Dim MyConn As OleDbConnection
[size=small][font=Consolas][size=small][font=Consolas]'Dim da As OleDbDataAdapter
[size=small][font=Consolas][size=small][font=Consolas]Dim ds As DataSet
[size=small][font=Consolas][size=small][font=Consolas]Dim tables As DataTableCollection
[size=small][font=Consolas][size=small][font=Consolas]Dim source1 As New BindingSource
[size=small][font=Consolas][size=small][font=Consolas]Dim APP As New Excel.Application
[size=small][font=Consolas][size=small][font=Consolas]Dim worksheet As Excel.Worksheet
[size=small][font=Consolas][size=small][font=Consolas]Dim workbook As Excel.Workbook
[size=small][font=Consolas][size=small][font=Consolas]Sub Load_Data()
dt.Clear()
da =
[size=small][font=Consolas][size=small][font=Consolas]New OleDbDataAdapter("Select * From Table1", con)
da.Fill(dt)
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Sub save_Data()
[size=small][font=Consolas][size=small][font=Consolas]Dim save As New OleDbCommandBuilder(da)save.QuotePrefix =
[size=small][font=Consolas][size=small][font=Consolas]"["save.QuoteSuffix =
[size=small][font=Consolas][size=small][font=Consolas]"]"
da.Update(dt)
dt.AcceptChanges()
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Public Function Max_ID_From_dt()
[size=small][font=Consolas][size=small][font=Consolas]Dim Number As Integer
[size=small][font=Consolas][size=small][font=Consolas]Try
[size=small][font=Consolas][size=small][font=Consolas]Dim cmd As New OleDbCommand("Select Max(ID) From dt", con)
con.Open()
Number = cmd.ExecuteScalar
con.Close()
[size=small][font=Consolas][size=small][font=Consolas]Catch
Number = 0
con.Close()
[size=small][font=Consolas][size=small][font=Consolas]End Try
[size=small][font=Consolas][size=small][font=Consolas]Return Number
[size=small][font=Consolas][size=small][font=Consolas]End Function
[size=small][font=Consolas][size=small][font=Consolas]' جملة تحميل الجدول الي الداتا جريد فيو
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Load_Data()
DataGridView1.DataSource = dt
workbook = APP.Workbooks.Open(excelLocation)
worksheet = workbook.Worksheets(
[size=small][font=Consolas][size=small][font=Consolas]"sheet1")MyConn =
[size=small][font=Consolas][size=small][font=Consolas]New OleDbConnection
[size=small][font=Consolas][size=small][font=Consolas]'MyConn.ConnectionString = conds =
[size=small][font=Consolas][size=small][font=Consolas]New DataSet
tables = ds.Tables
da =
[size=small][font=Consolas][size=small][font=Consolas]New OleDbDataAdapter("Select * from [code]", MyConn)da.Fill(ds,
[size=small][font=Consolas][size=small][font=Consolas]"table1")
[size=small][font=Consolas][size=small][font=Consolas]Dim view As New DataView(tables(0))
source1.DataSource = view
DataGridView1.DataSource = view
DataGridView1.AllowUserToAddRows =
[size=small][font=Consolas][size=small][font=Consolas]False
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]' مفتاح الاضافة
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
TextBox1.Clear()
TextBox2.Clear()
TextBox3.Clear()
TextBox4.Clear()
TextBox5.Clear()
DateTimePicker1.Value = Now
dt.Rows.Add()
[size=small][font=Consolas][size=small][font=Consolas]Dim pos As String = dt.Rows.Count - 1
DataGridView1.Focus()
[size=small][font=Consolas][size=small][font=Consolas]Dim MaxRows As Integer = dt.Rows.Count
BindingContext(dt).Position = MaxRows
dt.Rows(pos).Item(1) = TextBox1.Text
dt.Rows(pos).Item(2) = TextBox2.Text
dt.Rows(pos).Item(3) = Val(TextBox3.Text)
dt.Rows(pos).Item(4) = DateTimePicker1.Value
dt.Rows(pos).Item(5) = Val(TextBox4.Text)
dt.Rows(pos).Item(6) = TextBox5.Text
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]' كود عرض السطر اللي واقفين عليه في الداتا جريد فيو في التكست بوكس
[size=small][font=Consolas][size=small][font=Consolas]Private Sub DataGridView1_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.SelectionChanged
[size=small][font=Consolas][size=small][font=Consolas]Try
[size=small][font=Consolas][size=small][font=Consolas]Dim pos As String = BindingContext(dt).Position
TxtID.Text = dt.Rows(pos).Item(0)
TextBox1.Text = dt.Rows(pos).Item(1)
TextBox2.Text = dt.Rows(pos).Item(2)
TextBox3.Text = dt.Rows(pos).Item(3)
DateTimePicker1.Value = dt.Rows(pos).Item(4)
TextBox4.Text = dt.Rows(pos).Item(5)
TextBox5.Text = dt.Rows(pos).Item(6)
[size=small][font=Consolas][size=small][font=Consolas]Catch ex As Exception
[size=small][font=Consolas][size=small][font=Consolas]End Try
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]' كود التعديل
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.ClickMsgBox(
[size=small][font=Consolas][size=small][font=Consolas]"Are you Sure ?")
[size=small][font=Consolas][size=small][font=Consolas]Dim pos As String = BindingContext(dt).Position
dt.Rows(pos).Item(1) = TextBox1.Text
dt.Rows(pos).Item(2) = TextBox2.Text
dt.Rows(pos).Item(3) = Val(TextBox3.Text)
dt.Rows(pos).Item(4) = DateTimePicker1.Value
dt.Rows(pos).Item(5) = Val(TextBox4.Text)
dt.Rows(pos).Item(6) = TextBox5.Text
save_Data()
MsgBox(
[size=small][font=Consolas][size=small][font=Consolas]"Already Saved")
clear()
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]' كود الحذف
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
[size=small][font=Consolas][size=small][font=Consolas]If TxtID.Text = "" ThenMsgBox(
[size=small][font=Consolas][size=small][font=Consolas]"لم يتم اختيار سجل لحذفه")
[size=small][font=Consolas][size=small][font=Consolas]Exit Sub
[size=small][font=Consolas][size=small][font=Consolas]End If
[size=small][font=Consolas][size=small][font=Consolas]Dim bc As Integer = BindingContext(dt).Position
[size=small][font=Consolas][size=small][font=Consolas]Dim pos As Integer = DataGridView1.CurrentRow.Index
[size=small][font=Consolas][size=small][font=Consolas]Dim posNm = DataGridView1.Rows(pos).Cells("Name").Value
[size=small][font=Consolas][size=small][font=Consolas]Dim posID = DataGridView1.Rows(pos).Cells("ID").Value
[size=small][font=Consolas][size=small][font=Consolas]If MsgBox("هل تريد الحذف فعلاً " & posNm, MsgBoxStyle.Question + vbYesNo, Title:="تحذير") = vbYes Then
[size=small][font=Consolas][size=small][font=Consolas]Dim cmd As New OleDbCommand("DELETE FROM Table1 WHERE ID =" & TxtID.Text, con)
[size=small][font=Consolas][size=small][font=Consolas]'cmd.Connection = conn
con.Open()
cmd.ExecuteNonQuery()
con.Close()
dt.Clear()
Load_Data()
BindingContext(dt).Position = pos
MsgBox(
[size=small][font=Consolas][size=small][font=Consolas]"تم الحذف")
[size=small][font=Consolas][size=small][font=Consolas]Else
[size=small][font=Consolas][size=small][font=Consolas]Return
[size=small][font=Consolas][size=small][font=Consolas]End If
[size=small][font=Consolas][size=small][font=Consolas]'Dim msg As DialogResult = MessageBox.Show("هل تريد الحذف فعلاً", "تحذير", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
[size=small][font=Consolas][size=small][font=Consolas]'dt.Rows(BindingContext(dt).Position).Delete()
[size=small][font=Consolas][size=small][font=Consolas]'If msg = Windows.Forms.DialogResult.Yes Then
[size=small][font=Consolas][size=small][font=Consolas]' DataGridView1.Rows.Remove(DataGridView1.CurrentRow)
[size=small][font=Consolas][size=small][font=Consolas]' DataGridView1.Refresh()
[size=small][font=Consolas][size=small][font=Consolas]' dt.AcceptChanges()
[size=small][font=Consolas][size=small][font=Consolas]' save_Data()
[size=small][font=Consolas][size=small][font=Consolas]' MsgBox("تم الحذف")
[size=small][font=Consolas][size=small][font=Consolas]'End If
Load_Data()
clear()
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Private Sub clear()
TxtID.Clear()
TextBox1.Clear()
TextBox2.Clear()
TextBox3.Clear()
TextBox4.Clear()
TextBox5.Clear()
DateTimePicker1.Value = Now
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
BindingContext(dt).Position = 0
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button7_Click(ByVal sender [color=#0000ff][size=small][font=Consolas][color=
الرجاء المساعدة في تضبيط الكود حتي يمكن تصدير الجدول الي الاكسيل
الموجود قي ال Project directory
اسم ملف الاكسيل هو Test
[align=left]Imports
[size=small][font=Consolas][size=small][font=Consolas][color=#000000]System.Data.OleDb[color=#0000ff][color=#0000ff][size=small][font=Consolas]Imports
[size=small][font=Consolas][size=small][font=Consolas][color=#000000]Excel = Microsoft.Office.Interop.Excel[color=#0000ff][color=#0000ff][size=small][font=Consolas]Public
[color=#0000ff][size=small][font=Consolas][color=#0000ff][size=small][font=Consolas][color=#0000ff][size=small][font=Consolas]Class Form1
[size=small][font=Consolas][size=small][font=Consolas]' جملة الربط
[size=small][font=Consolas][size=small][font=Consolas]Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Code.accdb")
[size=small][font=Consolas][size=small][font=Consolas]Dim da As New OleDbDataAdapter ' جدول مصدر المعلومات
[size=small][font=Consolas][size=small][font=Consolas]Dim dt As New DataTable ' مكان التخزين
[size=small][font=Consolas][size=small][font=Consolas]Dim excelLocation As String = "D:\test.xlsx"
[size=small][font=Consolas][size=small][font=Consolas]Dim MyConn As OleDbConnection
[size=small][font=Consolas][size=small][font=Consolas]'Dim da As OleDbDataAdapter
[size=small][font=Consolas][size=small][font=Consolas]Dim ds As DataSet
[size=small][font=Consolas][size=small][font=Consolas]Dim tables As DataTableCollection
[size=small][font=Consolas][size=small][font=Consolas]Dim source1 As New BindingSource
[size=small][font=Consolas][size=small][font=Consolas]Dim APP As New Excel.Application
[size=small][font=Consolas][size=small][font=Consolas]Dim worksheet As Excel.Worksheet
[size=small][font=Consolas][size=small][font=Consolas]Dim workbook As Excel.Workbook
[size=small][font=Consolas][size=small][font=Consolas]Sub Load_Data()
dt.Clear()
da =
[size=small][font=Consolas][size=small][font=Consolas]New OleDbDataAdapter("Select * From Table1", con)
da.Fill(dt)
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Sub save_Data()
[size=small][font=Consolas][size=small][font=Consolas]Dim save As New OleDbCommandBuilder(da)save.QuotePrefix =
[size=small][font=Consolas][size=small][font=Consolas]"["save.QuoteSuffix =
[size=small][font=Consolas][size=small][font=Consolas]"]"
da.Update(dt)
dt.AcceptChanges()
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Public Function Max_ID_From_dt()
[size=small][font=Consolas][size=small][font=Consolas]Dim Number As Integer
[size=small][font=Consolas][size=small][font=Consolas]Try
[size=small][font=Consolas][size=small][font=Consolas]Dim cmd As New OleDbCommand("Select Max(ID) From dt", con)
con.Open()
Number = cmd.ExecuteScalar
con.Close()
[size=small][font=Consolas][size=small][font=Consolas]Catch
Number = 0
con.Close()
[size=small][font=Consolas][size=small][font=Consolas]End Try
[size=small][font=Consolas][size=small][font=Consolas]Return Number
[size=small][font=Consolas][size=small][font=Consolas]End Function
[size=small][font=Consolas][size=small][font=Consolas]' جملة تحميل الجدول الي الداتا جريد فيو
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Load_Data()
DataGridView1.DataSource = dt
workbook = APP.Workbooks.Open(excelLocation)
worksheet = workbook.Worksheets(
[size=small][font=Consolas][size=small][font=Consolas]"sheet1")MyConn =
[size=small][font=Consolas][size=small][font=Consolas]New OleDbConnection
[size=small][font=Consolas][size=small][font=Consolas]'MyConn.ConnectionString = conds =
[size=small][font=Consolas][size=small][font=Consolas]New DataSet
tables = ds.Tables
da =
[size=small][font=Consolas][size=small][font=Consolas]New OleDbDataAdapter("Select * from [code]", MyConn)da.Fill(ds,
[size=small][font=Consolas][size=small][font=Consolas]"table1")
[size=small][font=Consolas][size=small][font=Consolas]Dim view As New DataView(tables(0))
source1.DataSource = view
DataGridView1.DataSource = view
DataGridView1.AllowUserToAddRows =
[size=small][font=Consolas][size=small][font=Consolas]False
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]' مفتاح الاضافة
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
TextBox1.Clear()
TextBox2.Clear()
TextBox3.Clear()
TextBox4.Clear()
TextBox5.Clear()
DateTimePicker1.Value = Now
dt.Rows.Add()
[size=small][font=Consolas][size=small][font=Consolas]Dim pos As String = dt.Rows.Count - 1
DataGridView1.Focus()
[size=small][font=Consolas][size=small][font=Consolas]Dim MaxRows As Integer = dt.Rows.Count
BindingContext(dt).Position = MaxRows
dt.Rows(pos).Item(1) = TextBox1.Text
dt.Rows(pos).Item(2) = TextBox2.Text
dt.Rows(pos).Item(3) = Val(TextBox3.Text)
dt.Rows(pos).Item(4) = DateTimePicker1.Value
dt.Rows(pos).Item(5) = Val(TextBox4.Text)
dt.Rows(pos).Item(6) = TextBox5.Text
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]' كود عرض السطر اللي واقفين عليه في الداتا جريد فيو في التكست بوكس
[size=small][font=Consolas][size=small][font=Consolas]Private Sub DataGridView1_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.SelectionChanged
[size=small][font=Consolas][size=small][font=Consolas]Try
[size=small][font=Consolas][size=small][font=Consolas]Dim pos As String = BindingContext(dt).Position
TxtID.Text = dt.Rows(pos).Item(0)
TextBox1.Text = dt.Rows(pos).Item(1)
TextBox2.Text = dt.Rows(pos).Item(2)
TextBox3.Text = dt.Rows(pos).Item(3)
DateTimePicker1.Value = dt.Rows(pos).Item(4)
TextBox4.Text = dt.Rows(pos).Item(5)
TextBox5.Text = dt.Rows(pos).Item(6)
[size=small][font=Consolas][size=small][font=Consolas]Catch ex As Exception
[size=small][font=Consolas][size=small][font=Consolas]End Try
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]' كود التعديل
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.ClickMsgBox(
[size=small][font=Consolas][size=small][font=Consolas]"Are you Sure ?")
[size=small][font=Consolas][size=small][font=Consolas]Dim pos As String = BindingContext(dt).Position
dt.Rows(pos).Item(1) = TextBox1.Text
dt.Rows(pos).Item(2) = TextBox2.Text
dt.Rows(pos).Item(3) = Val(TextBox3.Text)
dt.Rows(pos).Item(4) = DateTimePicker1.Value
dt.Rows(pos).Item(5) = Val(TextBox4.Text)
dt.Rows(pos).Item(6) = TextBox5.Text
save_Data()
MsgBox(
[size=small][font=Consolas][size=small][font=Consolas]"Already Saved")
clear()
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]' كود الحذف
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
[size=small][font=Consolas][size=small][font=Consolas]If TxtID.Text = "" ThenMsgBox(
[size=small][font=Consolas][size=small][font=Consolas]"لم يتم اختيار سجل لحذفه")
[size=small][font=Consolas][size=small][font=Consolas]Exit Sub
[size=small][font=Consolas][size=small][font=Consolas]End If
[size=small][font=Consolas][size=small][font=Consolas]Dim bc As Integer = BindingContext(dt).Position
[size=small][font=Consolas][size=small][font=Consolas]Dim pos As Integer = DataGridView1.CurrentRow.Index
[size=small][font=Consolas][size=small][font=Consolas]Dim posNm = DataGridView1.Rows(pos).Cells("Name").Value
[size=small][font=Consolas][size=small][font=Consolas]Dim posID = DataGridView1.Rows(pos).Cells("ID").Value
[size=small][font=Consolas][size=small][font=Consolas]If MsgBox("هل تريد الحذف فعلاً " & posNm, MsgBoxStyle.Question + vbYesNo, Title:="تحذير") = vbYes Then
[size=small][font=Consolas][size=small][font=Consolas]Dim cmd As New OleDbCommand("DELETE FROM Table1 WHERE ID =" & TxtID.Text, con)
[size=small][font=Consolas][size=small][font=Consolas]'cmd.Connection = conn
con.Open()
cmd.ExecuteNonQuery()
con.Close()
dt.Clear()
Load_Data()
BindingContext(dt).Position = pos
MsgBox(
[size=small][font=Consolas][size=small][font=Consolas]"تم الحذف")
[size=small][font=Consolas][size=small][font=Consolas]Else
[size=small][font=Consolas][size=small][font=Consolas]Return
[size=small][font=Consolas][size=small][font=Consolas]End If
[size=small][font=Consolas][size=small][font=Consolas]'Dim msg As DialogResult = MessageBox.Show("هل تريد الحذف فعلاً", "تحذير", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
[size=small][font=Consolas][size=small][font=Consolas]'dt.Rows(BindingContext(dt).Position).Delete()
[size=small][font=Consolas][size=small][font=Consolas]'If msg = Windows.Forms.DialogResult.Yes Then
[size=small][font=Consolas][size=small][font=Consolas]' DataGridView1.Rows.Remove(DataGridView1.CurrentRow)
[size=small][font=Consolas][size=small][font=Consolas]' DataGridView1.Refresh()
[size=small][font=Consolas][size=small][font=Consolas]' dt.AcceptChanges()
[size=small][font=Consolas][size=small][font=Consolas]' save_Data()
[size=small][font=Consolas][size=small][font=Consolas]' MsgBox("تم الحذف")
[size=small][font=Consolas][size=small][font=Consolas]'End If
Load_Data()
clear()
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Private Sub clear()
TxtID.Clear()
TextBox1.Clear()
TextBox2.Clear()
TextBox3.Clear()
TextBox4.Clear()
TextBox5.Clear()
DateTimePicker1.Value = Now
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
BindingContext(dt).Position = 0
[size=small][font=Consolas][size=small][font=Consolas]End Sub
[size=small][font=Consolas][size=small][font=Consolas]Private Sub Button7_Click(ByVal sender [color=#0000ff][size=small][font=Consolas][color=