Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form3
Private Sub ButtonImport_Click(sender As Object, e As EventArgs) Handles ButtonImport.Click
Dim openFileDialog As New OpenFileDialog()
openFileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm"
If openFileDialog.ShowDialog() = DialogResult.OK Then
ImportExcelToDataGridView(openFileDialog.FileName)
End If
End Sub
Private Sub ImportExcelToDataGridView(ByVal filePath As String)
Dim excelApp As New Excel.Application()
Dim excelWorkbook As Excel.Workbook = excelApp.Workbooks.Open(filePath)
Dim excelWorksheet As Excel.Worksheet = CType(excelWorkbook.Sheets(1), Excel.Worksheet)
Dim excelRange As Excel.Range = excelWorksheet.UsedRange
' قراءة السطر الأول كعناوين الأعمدة
For col As Integer = 1 To 15
DataGridView1.Columns.Add(Convert.ToString(excelRange.Cells(1, col).Value2), Convert.ToString(excelRange.Cells(1, col).Value2))
Next
' قراءة البيانات من السطر الثاني
For row As Integer = 2 To excelRange.Rows.Count
Dim isRowEmpty As Boolean = True
Dim rowData(14) As String ' مصفوفة لاستيعاب بيانات الصف
For col As Integer = 1 To 15
Dim cellValue As String = Convert.ToString(excelRange.Cells(row, col).Value2)
rowData(col - 1) = cellValue
If Not String.IsNullOrWhiteSpace(cellValue) Then
isRowEmpty = False
End If
Next
If Not isRowEmpty Then
DataGridView1.Rows.Add(rowData)
End If
Next
excelWorkbook.Close(False)
excelApp.Quit()
ReleaseObject(excelRange)
ReleaseObject(excelWorksheet)
ReleaseObject(excelWorkbook)
ReleaseObject(excelApp)
End Sub
Private Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click
SaveDataGridViewToDatabase()
End Sub
Private Sub ButtonLoad_Click(sender As Object, e As EventArgs) Handles ButtonLoad.Click
LoadDataFromDatabase()
End Sub
Private Sub ButtonExport_Click(sender As Object, e As EventArgs) Handles ButtonExport.Click
ExportDataGridViewToExcel()
End Sub
Private Sub SaveDataGridViewToDatabase()
Dim connectionString As String = "Data Source=.;Initial Catalog=DATABASE;Integrated Security=True"
Using cn As New SqlConnection(connectionString)
cn.Open()
For Each row As DataGridViewRow In DataGridView1.Rows
If Not row.IsNewRow Then
Using cm As New SqlCommand("INSERT INTO Item_Tbl (ItemBarcode , ItemNameA , ItemNameE , ..., Column15) VALUES (@ItemBarcode , @ItemNameA , @ItemNameE, ..., @val15)", cn)
cm.Parameters.AddWithValue("@ItemBarcode", row.Cells(0).Value)
cm.Parameters.AddWithValue("@ItemNameA", row.Cells(1).Value)
cm.Parameters.AddWithValue("@ItemNameE", row.Cells(2).Value)
'...
cm.Parameters.AddWithValue("@val15", row.Cells(14).Value)
cm.ExecuteNonQuery()
End Using
End If
Next
End Using
MessageBox.Show("Data saved successfully!")
End Sub
Private Sub LoadDataFromDatabase()
Dim connectionString As String = "Data Source=.;Initial Catalog=DATABASE;Integrated Security=True"
Using cn As New SqlConnection(connectionString)
Dim query As String = "SELECT * FROM Item_Tbl"
Dim da As New SqlDataAdapter(query, cn)
Dim dt As New DataTable()
da.Fill(dt)
DataGridView1.DataSource = dt
End Using
End Sub
Private Sub ExportDataGridViewToExcel()
Dim excelApp As New Excel.Application()
Dim excelWorkbook As Excel.Workbook = excelApp.Workbooks.Add()
Dim excelWorksheet As Excel.Worksheet = CType(excelWorkbook.Sheets(1), Excel.Worksheet)
' إضافة عناوين الأعمدة
For col As Integer = 0 To DataGridView1.Columns.Count - 1
excelWorksheet.Cells(1, col + 1).Value = DataGridView1.Columns(col).HeaderText
Next
' إضافة البيانات
For row As Integer = 0 To DataGridView1.Rows.Count - 1
For col As Integer = 0 To DataGridView1.Columns.Count - 1
excelWorksheet.Cells(row + 2, col + 1).Value = DataGridView1.Rows(row).Cells(col).Value
Next
Next
' حفظ الملف
Dim saveFileDialog As New SaveFileDialog()
saveFileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm"
If saveFileDialog.ShowDialog() = DialogResult.OK Then
excelWorkbook.SaveAs(saveFileDialog.FileName)
excelWorkbook.Close()
excelApp.Quit()
MessageBox.Show("Data exported successfully!")
End If
ReleaseObject(excelWorksheet)
ReleaseObject(excelWorkbook)
ReleaseObject(excelApp)
End Sub
Private Sub ReleaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class