[ابو ليلى]

بناءً على ما سبق سنبدأ العمل ان شاء الله كالتالي
سيكون لدي ملف اكسل اسمه (Test.xlsx) , و يحتوي الملف على 3 اوراق عمل (Sheet1-Sheet2-Sheet3)
الورقة الاولى فارغة لا يوجد بها شيئ
الورقة الثانية تحتوي على جدول واحد , في اربع اعمدة وخالي من البيانات, كما يلي:

 EmpID | EmpName |  EmpPhone | EmpAdress
                  |                 |               |
                  |                 |               |

الورقة الثالثة تحتوي على جدول واحد ايضاً نفس بنية الجدول السابق لكنه يحتوي على بيانات (9 صفوف)

----------------------------------------------------
من جهة اخرى لدي قاعدة بيانات اكسس فيها جدول واحد (Table1) في نفس الاعمدة السابقة و يحتوي على بيانات (6 صفوف)
ستكون وظيفتنا كالتالي:
-تامين اتصال مع القاعدة و جلب البيانات لعرضها على الواجهة ضمن (DataGridView)

-تصدير البيانات الى ملف اكسل جديد (صناعة ملف جديد) و وعمل ورقة بنفس اسم الجدول فيها بيانات الجدول عن طريق المكتبة OpenXml  (تصدير الاعمدة مع البيانات).

-انشاء ورقة عمل جديدة في الملف المحدد باسم الجدول و انشاء جدول داخل الورقة ثم تصدير البيانات الى الجدول باستخدام ClosedXml.

-تصدير بيانات الجدول الى ورقة عمل موجودة في الملف (البيانات فقط دون الاعمدة) باستخدام ClosedXml.

-تصدير البيانات الى جدول موجود في ورقة عمل (البيانات ستضاف كصفوف فقط)باستخدام ClosedXml.

-استيراد البيانات من  ورقة عمل الى جدول داخل البرنامج و عرضه على الواجهة باستخدام ClosedXml.

للتنويه فقط تم مجمل العمل وفق المكتبة ClosedXml لسهولة التعامل معها و لانها مطورة عن المكتبة الاولى , و تختصر الكثير من الوقت و الجهد , موسعة بشكل اكبر و دعم فئات اكثر (كود قليل و نظيف) ستكتشف ذلك من خلال العمل.
---------------------------------------------------------------------
استورد المراجع الخاصة بالمكتبتين الى مشروعك اولاً
استورد المرجع Windows Base من اطار العمل الى مشروعك
----------------------------------------------------------------------
في البداية سنفصل العمليات الخاصة بالقاعدة من فتح اتصال و جلب البيانات في كلاس منفصل ليبقى الكود انظف و اسهل للفهم
الكلاس التالي يحتوي على بعض العمليات المطلوبة.

PHP كود :

Public Class Cls_Emp
    Protected Shared ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ExcelDb.accdb"
    Public Shared Con As OleDb.OleDbConnection

    'عند انشاء كائن جديد يتم تزويد كائن الاتصال بنص الاتصال
    Public Sub New()
        Con = New OleDb.OleDbConnection(ConnectionString)
    End Sub

    'فتح الاتصال
    Public Shared Sub OpenConnection()
        If Con.State = ConnectionState.Closed Then
            Con.Open()
        End If
    End Sub

    'اغلاق الاتصال
    Public Shared Sub CloseConnection()
        If Con.State = ConnectionState.Open Then
            Con.Close()
        End If
    End Sub

    'الحصول على كائن الاتصال
    Public ReadOnly Property GetOledbConnection() As OleDb.OleDbConnection
        Get
            If String.IsNullOrEmpty(Con.ConnectionString) Then
                Con.ConnectionString = ConnectionString
            End If
            If Not (Con) Is Nothing Then
                Return Con
            Else
                Con = New OleDb.OleDbConnection(ConnectionString)
                Return Con
            End If
        End Get
    End Property


    'دالة تعيد جدول الموظفين
    Public Function GetEmpData() As DataTable
        Dim SqlText As String = "Select EmpID,EmpName,EmpPhone,EmpAdrees From Table1"
        Dim EmpTable As New DataTable With {.TableName = "EmpTable"}

        Con = GetOledbConnection

        Using Cmd As New OleDb.OleDbCommand With {.CommandText = SqlText, .Connection = Con}
            OpenConnection()
            Dim Reader As OleDb.OleDbDataReader = Cmd.ExecuteReader
            EmpTable.Load(Reader)
            Reader.Close()
            CloseConnection()
        End Using
        Return EmpTable

    End Function

End Class 


الان لدينا نموذج نضع عليه DataGridView1 و نسميها EmpDataGrid
نضع 5 ازرار على النموذج زر لكل عملية سنقوم بها.


نبدأ مع العملية الاولى:

-تصدير البيانات الى ملف اكسل جديد (صناعة ملف جديد) و وعمل ورقة بنفس اسم الجدول فيها بيانات الجدول عن طريق المكتبة OpenXml  (تصدير الاعمدة مع البيانات).

PHP كود :

   'Using OpenXml
    'تصدير جدول الى اكسل 
    'انشاء ملف اكسل جديد في المسار المحدد و انشاء ورقة عمل باسم الجدول و ادراج بيانات داخل ورقة العمل
    'اذا كان لديك اكثر من جدول في حاوية يمكنك المرور عليها عبر حلقة
    Private Sub ExportDataTableWithOpenXml(ByVal table As DataTable, ByVal destination As String)
        Using workbook As SpreadsheetDocument = SpreadsheetDocument.Create(destination, OpenXml.SpreadsheetDocumentType.Workbook)
            Dim workbookPart = workbook.AddWorkbookPart()
            workbook.WorkbookPart.Workbook = New OpenXml.Spreadsheet.Workbook()
            workbook.WorkbookPart.Workbook.Sheets = New OpenXml.Spreadsheet.Sheets()
            'اذا كان لديك اكثر من جدول
            'For Each table As System.Data.DataTable In DS.Tables
            Dim sheetPart = workbook.WorkbookPart.AddNewPart(Of WorksheetPart)()
            Dim sheetData = New OpenXml.Spreadsheet.SheetData()
            sheetPart.Worksheet = New OpenXml.Spreadsheet.Worksheet(sheetData)
            Dim sheets As OpenXml.Spreadsheet.Sheets = workbook.WorkbookPart.Workbook.GetFirstChild(Of OpenXml.Spreadsheet.Sheets)()
            Dim relationshipId As String = workbook.WorkbookPart.GetIdOfPart(sheetPart)
            Dim sheetId As UInteger = 1
            If sheets.Elements(Of OpenXml.Spreadsheet.Sheet)().Count() > 0 Then
                sheetId = sheets.Elements(Of OpenXml.Spreadsheet.Sheet)().[Select](Function(s) s.SheetId.Value).Max() + 1
            End If

            Dim sheet As OpenXml.Spreadsheet.Sheet = New OpenXml.Spreadsheet.Sheet() With {.Id = relationshipId, .SheetId = sheetId, .Name = table.TableName}
            sheets.Append(sheet)
            Dim headerRow As OpenXml.Spreadsheet.Row = New OpenXml.Spreadsheet.Row()
            Dim columns As List(Of String) = New List(Of String)()
            For Each column As System.Data.DataColumn In table.Columns
                columns.Add(column.ColumnName)
                Dim cell As OpenXml.Spreadsheet.Cell = New OpenXml.Spreadsheet.Cell()
                cell.DataType = OpenXml.Spreadsheet.CellValues.String
                cell.CellValue = New OpenXml.Spreadsheet.CellValue(column.ColumnName)
                headerRow.AppendChild(cell)
            Next

            sheetData.AppendChild(headerRow)
            For Each dsrow As System.Data.DataRow In table.Rows
                Dim newRow As OpenXml.Spreadsheet.Row = New OpenXml.Spreadsheet.Row()
                For Each col As String In columns
                    Dim cell As OpenXml.Spreadsheet.Cell = New OpenXml.Spreadsheet.Cell()
                    cell.DataType = OpenXml.Spreadsheet.CellValues.String
                    cell.CellValue = New OpenXml.Spreadsheet.CellValue(dsrow(col).ToString())
                    newRow.AppendChild(cell)
                Next

                sheetData.AppendChild(newRow)
            Next
            'Next
        End Using
    End Sub 


كما ترى كم الكود كبير و يحتاج الى تتبع دقيق


العملية الثانية:

-انشاء ورقة عمل جديدة في الملف المحدد باسم الجدول و انشاء جدول داخل الورقة ثم تصدير البيانات الى الجدول باستخدام ClosedXml.

PHP كود :

   'Using ClosedXml
    'انشاء ورقة عمل جديدة في الملف المحدد 
    'انشاء جدول جديد داخل ورقة العمل
    'تصدير البيانات الى الجدول
    Private Sub ExportDataTableWithClosedXml(Path As String)
        Dim EmpTable As DataTable = Emp.GetEmpData
        Dim Xworbook As New XLWorkbook(Path)
        Xworbook.Worksheets.Add(EmpTable, "TestClosedXml")
        Xworbook.SaveAs(Path)
    End Sub 


العملية الثالثة:

-تصدير بيانات الجدول الى ورقة عمل موجودة في الملف (البيانات فقط دون الاعمدة) باستخدام ClosedXml.

PHP كود :

   'Using ClosedXml
    'تصدير بيانات جدول الى ورقة عمل في ملف موجود - البيانات دون الاعمدة
    Private Sub Btn_ExportDataFromTableToExitingSheet_Click(sender As Object, e As EventArgs) Handles Btn_ExportDataFromTableToExitingSheet.Click
        Dim EmpTable As DataTable = Emp.GetEmpData

        Dim Xworbook As New XLWorkbook(FilePath & "\Test.xlsx")
        Dim Sheet1 As IXLWorksheet = Xworbook.Worksheet("Sheet1")
        Dim NewData As IXLCell = Sheet1.Cell(Sheet1.LastRowUsed().RowNumber() + 1, 1)
        NewData.InsertData(EmpTable)
        Xworbook.Save()
    End Sub 


العملية الرابعة:
-تصدير البيانات الى جدول موجود في ورقة عمل (البيانات ستضاف كصفوف فقط)باستخدام ClosedXml.

PHP كود :

   'Using ClosedXml
    'تصدير بيانات جدول الى ورقة عمل في ملف موجود - البيانات دون الاعمدة
    'البيانات ستصدر الى جدول موجود في ورقة العمل
    'اي ان البيانات ستضاف كصفوف في ذلك الجدول
    'الورقة الثانية تحتوي على جدول باربع اعمدة
    Private Sub Btn_ExportDataFromTableToExitingSheetContainTable_Click(sender As Object, e As EventArgs) Handles Btn_ExportDataFromTableToExitingSheetContainTable.Click
        Dim EmpTable As DataTable = Emp.GetEmpData

        Dim Xworbook As New XLWorkbook(FilePath & "\Test.xlsx")
        Dim Sheet2 As IXLWorksheet = Xworbook.Worksheet("Sheet2")

        insertRowsToDt(Sheet2, EmpTable)
        Xworbook.Save()

    End Sub 


PHP كود :

   'وظيفة الاجراء ادخال صفوف في جدول الورقة
    Private Sub insertRowsToDt(Ws As IXLWorksheet, Dt As DataTable)
        Dim str As String() = New String() {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL"}
        'الحصول على اول جدول في الورقة
        Dim firstTable As IXLTable = Ws.Tables.FirstOrDefault()
        'الحصول على اخر صف في الجدول
        Dim LastRow As Integer = Ws.LastRowUsed().RowNumber()
        '
        Dim IndexForInsert As Integer = LastRow + 1
        Pros1.Maximum = Dt.Rows.Count
        Pros1.Value = 0
        For Each DtRow As DataRow In Dt.Rows
            firstTable.InsertRowsBelow(LastRow)
            For i = 0 To Dt.Columns.Count - 1
                Ws.Cell(str(i) & IndexForInsert).Value = DtRow(i)
            Next
            IndexForInsert += 1
            Pros1.Value += 1
        Next
    End Sub 


العملية الخامسة:
-استيراد البيانات من  ورقة عمل الى جدول داخل البرنامج و عرضه على الواجهة باستخدام ClosedXml.

PHP كود :

   'Using ClosedXml
    'استيراد بيانات من ورقة
    Protected Function ImportDataFromExcel(Path As String) As DataTable
        'تعريف جدول فارغ
        Dim DT As New DataTable()

        'فتح الملف وفق المساؤ المحدد
        Using workBook As New XLWorkbook(Path)
            'تحديد ورقة البيانات التي تحوي الجدول
            Dim workSheet As IXLWorksheet = workBook.Worksheet(3)
            'المرور على صفوف الجدول في ورقة البيانات
            Dim FirstRow As Boolean = True
            For Each row As IXLRow In workSheet.Rows()
                'استخدام الصف الاول لبناء الاعمدة
                If FirstRow = True Then
                    For Each cell As IXLCell In row.Cells()
                        DT.Columns.Add(cell.Value.ToString())
                    Next
                    FirstRow = False
                Else
                    'اضافة بيانات الصفوف الى الجدول
                    DT.Rows.Add()
                    Dim i As Integer = 0
                    For Each cell As IXLCell In row.Cells()
                        DT.Rows(DT.Rows.Count - 1)(i) = cell.Value.ToString()
                        i += 1
                    Next
                End If
            Next
        End Using
        Return DT
    End Function 


لم استخدم المكتبة الاولى OpenXml كثيراً نظراً لطول كودها , بدل من ذلك استفدنا من المكتبة المطورة بكود اسهل.
تجد الاشارة الى ان المكتبة تدعم الكثير و الكثير من الامور التي يمكن تتعامل فيها مع الاكسل كل شيئ تقريباً يمكن عمله .

يوجد للمكتبة Wiki خاص بها يمكنك التعلم منه الكثير من الامور , المكتبة مستمرة في التطوير و التحسين.
رابط Wiki الخاص بالمكتبة ClosedXml على موقع Githup

يمكنك تحميل المكتبة من خلال Nuget الخاص بالفجوال استوديو , او يمكنك الحصول عليها من الملف المرفق.

الملف المرفق فيه كل العمليات التي قمنا بها مع المكتبتين داخل المشروع.


بالتوفيق للجميع.