[ابو ليلى]

بســــم الله الرحمن الرحيم

و الصلاة و السلام على سيدنا محمد و على آله وصحبه اجمعين

الموضوع يتحدث عن التخاطب مع ملفات الاكســـــل من خلال مكتبتي (OpenXml - ClosedXml)
- المكتبة OpenXml معتمدة من قبل Microsoft , رابط مقال
- المكتبة ClosedXml مطورة عن المكتبة الاولى (اسهل و ايسر في التعامل و تشمل الاولى داخلها) - مصدر تعليمي

الموضوع يشمل عمليتي الاستيراد و التصدير من و الى ملفات الاكســــــل وفق المكاتب المذكورة.

---

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

---

البعض منا قد تعامل مع ملفات الاكسل من خلال اللغة و الكثير ممن تعاملوا معها كانو مجبورين على استخدام مراجع Com المملة و المزعجة اذ ان اطار العمل لدينا لم يوفر مراجع للتعامل مع هذه الملفات وغيرها من ملفات الاوفيس (رغم انها من انتاج نفس الشركة)
العيب مع ملفات Com انها فئات جامدة صممت خارج اطار العمل لا يمكن توقع نتائج سلوكياتها بشكل كبير و كذلك محدودية الوصل لبعض الفئات داخلها .... وغيرها الكثير.
من اكثر الامور المزعجة فيها - اختلاف اصدارات العمل (وفق نسخة الاوفيس المثبتة على الجهاز) و بالتالي انت مجبور على تعديل نسخة ملف المكتبة وفق النسخة التي يمتلكها العميل, وحتى لو كنت محترفاً واستخدمت Late Binding لتلافي اختلاف الاصدارات سيبقى الامر مزعجاً و يحتمل الكثير من الاخطاء.

الامر الاكثر ازعاجاً هو بطئ العمليات وفق هذه المكتبة , و خاصة عندما تتعامل مع ملفات كبيرة (فيها حجم كبير من البيانات).

كل التذمر السابق كان يخص المكتبة (MicroSoft.Office.Interop.Excel)

---

نبدأ على بركة الله مع تقديم بسيط .
الجيد في المكاتب المذكورة سابقاً كما يظهر من اسمها انها تتعامل مع الملفات ذات البنية الهرمية (ملفات Xml).
و الجيد اكثر ان ملفات اوفيس ذات اللاحقة المنتهية بالحرف (X) هي ملفات ذات بنية هرمية توافق ملفات Xml
من هذا الكلام يتين ان المكتبة قادرة على التعامل مع اكثر من نوع (Excel - Word) و غيرها ممن يوافق الشروط.
الحديث هنا سيقتصر على ملفات الاكسل فقط-و لك ان تبحر في فضاء المكتبة و تكتشف ما يناسب احتياجاتك.

---

اذا لم تطلع سابقاً على بنية ملفات Xml فهي مشابهة لما يلي

PHP كود :

XML
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
<workbook xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <sheets>
        <sheet name="MySheet1" sheetId="1" r:id="rId1" /> 
        <sheet name="MySheet2" sheetId="2" r:id="rId2" /> 
    </sheets>
</workbook> 


مفهوم الهرمية متبع في الكثير من الفئات و الكائنات , و لتبسيط الامر اكثر كل صغير يحتويه الاكبر منه و هكذا وصولا الى الاكبر.

يشابه هذا المفهوم بل ويطابق مفهوم هرمية السلطة , جماعة (Masson) يفهمون ذلك كثيراً (اعاذنا الله من شرورهم) .

لو نظرت للملف السابق ستجد اشياء مثل:
- (Spreadsheet) الرأس الاكبر في الملف (الاب الروحي) - و يمثل ملف الاكسل كاملاً . 
  و يحتوي بداخله كائنات من النوع (Sheets) و بدورها تحتوي على كائنات من النوع (Sheet) و التي تعرف بالاسم (WorkSheets)          و تكون محتواة بدورها داخل (WorkBook).

في الملف السابق لدينا Spreadsheettml يحتوي على WorkBook و بداخله يحتوي على كائنين من النوع WorkSheet  هما (MySheet1 - MySheet2) 

اما كائن WorkSheet فهو لب الموضوع في عملنا لنلقي نظرة على الملف التالي

PHP كود :

<?xml version="1.0" encoding="UTF-8" ?> 
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <sheetData>
        <row r="1">
            <c r="A1">
                <v>100</v> 
            </c>
        </row>
    </sheetData>
</workshee 


ستجد داخل كائن WorkSheet عناصر من النوع SheetData وهو الكائن الحاوي للبيانات (الرعية امثالنا  )
الكائن SheetData يمثل عملياً جدول الخلايا و في هذا الجدول ستجد الصفوف و كل صف فيه خلية او اكثر و كل خلية فيها قيمة.
تعرف الخلية بالمرجع (رقم الصف و رقم العمود) مثل (A1 , B2 , C5) و هكذا.

لنلقي نظرة على بنية هذا الكائن WorkSheet .

PHP كود :

<?xml version="1.0" encoding="UTF-8" ?> 
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <sheetData>
        <row r="1">
            <c r="A1">
                <v>100</v> 
            </c>
        </row>
    </sheetData>
</worksheet> 


ستجد في الكائن (الجدول) SheetData في الصف الاول الخلية A1 و قيمتها = 100

معهم حق جماعة (Masson) يدوخونا , اذا كنت معي الى هنا سنكمل بعد قليل ان شاء الله...


يتبع........

[ابو ليلى]

بناءً على ما سبق سنبدأ العمل ان شاء الله كالتالي
سيكون لدي ملف اكسل اسمه (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 الخاص بالفجوال استوديو , او يمكنك الحصول عليها من الملف المرفق.

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


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

[sendbad100]

السلام عليكم ورحمة الله وبركاتة

الله يعطيك الصحة والعافية 

ويجزيك الف الف الف خير 

ويتقبل منا ومنكم صالح الاعمال

[الوايلي]

الله يجزاك خير