تقييم الموضوع :
  • 0 أصوات - بمعدل 0
  • 1
  • 2
  • 3
  • 4
  • 5
الاتصال بمصنفات Excel باستخدام تقنية الربط ADO
#1
بسم الله الرحمن الرحيم


أخوانى الكرام أهلا وسهلا بكم ....الكثير منا يود التعامل مع ملفات إكسل فى مشروعة لذلك فى هذا الدرس إن شاء الله تعالى سنتعلم سويا كيفية الاتصال بمصنف إكسل بتقنية ado.....الدرس سيتناول بالشرح النقاط التالية :

1_ الاتصال بملفات إكسل باستخدام تقنية ADO
2_ قراءة البيانات من ملف إكسل
3_حل مشكلة أنواع البيانات فى ورقة عمل إكسل
4_ العمليات على مصنف إكسل إضافة / تعديل / حذف بيانات
5_ إنشاء ورقة عمل إكسل والمصنفات worksheets and workbooks من داخل الفجوال بيسك 6
6_استبدال البيانات
7_ نقل مجموعة سجلات إلى Excel

أولا : الاتصال بملف إكسل بأستخدام تقنية الربط ADO



يمكننا استخدام مزود Microsoft Jet provider ADO للاتصال بمصنف Excel. حيث يمكنا قراءة البيانات من المصنف و الكتابة الية...وعلى عكس كتابة البيانات إلى قواعد بيانات متعددة المستخدمين، ربما قد تنشأ أخطاء إذا حاولت كتابة البيانات إلى مصنف مفتوح حاليا مع مستخدم آخر .....وقبل البدء فى التعرف على نص الاتصال بمصنف إكسل يجب ادراج المكتبة Microsoft ActiveX Data Objects الى المشروع ......ثم نستخدم الكود التالى للاتصال بالمصنف :
PHP كود :
Dim cnn As New ADODB.Connection
Dim rst 
As New ADODB.Recordset

cnn
.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
"Data Source=C:\test.xlsx;" _
"Extended Properties=""Excel 12.0;HDR=Yes"";" 

بالنظر للكود أعلاه سنجد الاتى :

_ قمنا بالاعلان عن متغيرين أحدهما يعبر عن كائن الاتصال cnn و الثانى يعبر عن كائن السجلات rst
_قمنا بتمرير مزود الاتصال Provider=Microsoft.ACE.OLEDB.12.0 الى الكائن cnn
_ قمنا بتحديد مسار مصنف إكسل المراد الاتصال به C:\test.xlsx مع تحديد امتداد المصنف xlsx
_فى اخر سطر من الكود ستجد اننا قمنا بتحديد extended properties أو الخصائص الموسعة المطلوبة للاتصال بمصنف إكسل وهى مايلى :

_ رقم إصدار المصنف :لذلك أستخدمت جملة Excel 12.0 وهو رقم اصدار مصنف إكسل 2007 فما فوق
_ خيار رؤس الاعمدة HDR : لذلك لتحديد ما إذا كان الصف الأول في الجدول اكسل لديها رؤوس الأعمدة (أسماء الحقول)...فيتم تعيين هذا إلى Yes إذا كنا نريد استخدام الصف الأول كأسماء حقول، أو يتم تعينها الى NO أذا كنا نريد أن تدرج في الصف الأول كبيانات. وبعد ذلك يتم تسمية الحقول F1، F2 الخ


ملاحظة :الكود السابق هو لاتصال بمصنف إكسل 2007 فما فوق ....ولكن اذا كنت تريد الاتصال بمصنف إكسل 97 حتى 2003 نستخدم الكود التالى :
PHP كود :
Dim cnn As New ADODB.Connection
Dim rst 
As New ADODB.Recordset

cnn
.Open "Microsoft.Jet.OLEDB.4.0;" _
"Data Source=C:\test.xls;" _

"Extended Properties=""Excel 8.0;HDR=Yes"";" 


ويلاحظ فى الكود إعلاه أننا قمنا فقط بتغير مزود الاتصال الى Microsoft.Jet.OLEDB.4.0 و تغير رقم إصدار مصنف إكسل الى Excel 8.0 وهو ينطبق على كافة المصنفات التي تم إنشاؤها في أي إصدار من Excel97 فصاعدا حتى 2003

ثانيا : قراءة البيانات من مصنف إكسل

بعد ما قمنا بالاتصال بالمنصف فإن الشيء الأبسط والأكثر شيوعا و الذى تريد القيام به هو قراءة البيانات من ملفات اكسل.وذلك من إما من خلال استخدام عبارات SQL أو من خلال أسلوب التنفيذ Execute Method لتحديد البيانات المراد قرائتها ....وذلك على النحو التالى :

أ_ أستخدام عبارات SQL :

وتوجد أكثر من طريقة لبناء جملة أستعلام SQL لتحديد تلك البيانات وذلك على النحو التالى :

1_طريقة استخدام أسم ورقة العمل Worksheet Name

وفيها يتم تحديد بيانات ورقة عمل إكسل بناء على أسمها ....وذلك كما فى الكود التالى :
PHP كود :
rst.Open "SELECT * FROM [Sheet1$];"cnnadOpenStaticadLockReadOnly 

وكما ترون قمنا بإنشاء جملة استعلام عادية مع تحديد اسم ورقة العمل Sheet1 وإضافة علامة الدولار ($) إلى نهاية اسم ...هكذا تم تحديد البيانات المراد قرائتها ....ولكن كيف تتم القراءة ؟......الامر بيسط هو أن تضع مربع نص مثلا للمشروع ثم يتم إسناد عامود بيانات فى ورقة عمل إكسل الى هذا المربع .....كما فى الكود التالى :
PHP كود :
Text1.Text rst![الاسم
و الكود الكامل سيكون كما يلى :
PHP كود :
Dim cnn As New ADODB.Connection
Dim rst 
As New ADODB.Recordset

cnn
.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
"Data Source=C:\tst.xlsx;" _
"Extended Properties=""Excel 12.0;HDR=Yes"";"

rst.Open "SELECT * FROM [Sheet1$];"cnnadOpenStaticadLockReadOnly
Text1
.Text rst![الاسم

2_ طريقة أستخدام النطاق المسمى Named Range

النطاق المسمى فى إكسل عبارة عن مجموعة سجلات مرتبطة بأسم ميعن فى ورقة عمل واحدة كجدول الموظفين ....جدول العملاء...الخ ويمكنك تسمية نطاق فى إكسل بأتباع الخطوات التالية :


1_حدد الخلايا التي تحتوي على البيانات المراد أن تجعل لها نطاق مسمى .

2_انقر بزر الماوس الأيمن داخل النطاق المحدد ثم انقر فوق تسمية نطاق.

3_في مربع الحوار اسم جديد الذى سيظهر لك حدد اسماً للنطاق في مربع الاسم وانقر فوق موافق.

وهكذا نكون قد أنشأنا نطاق و ليكن بأسم ( SalesData ).....و بالتالى فإن جملة الاستعلام المطلوب إنشائها لقراءة بيانات هذا النطاق من خلال الفجوال بيسك ستكون على النحو التالى :
PHP كود :
rst.Open "SELECT * FROM [SalesData];"cnnadOpenStaticadLockReadOnly
Text1
.Text rst![الاسم

ويمكنك ملاحظة أننا أستخدمنا أسم النطاق فقط بدون علامة الدولار ($) إلى نهاية اسم ....


3_ طريقة استخدام النطاق غير المسمى Unnamed Range

النطاق غير المسمى هى مجموعة الخلايا التى يتم تحديدها بالماوس فى ورقة عمل إكسل والتى ينتج عنها قيمة رقيمة مثل A1:B10 فى شريط عنوان إكسل .....أذن جملة الاستعلام المطلوبة لقراءة تلك البيانات ستكون على النحو التالى :
PHP كود :
rst.Open "SELECT * FROM [Sheet1$A1:B10];"cnnadOpenStaticadLockReadOnly
Text1
.Text rst![الاسم


ب _إستخدام أسلوب التنفيذ Execute Method

بدلا من استخدام عبارة SQL السابقة لتحديد السجلات يمكنك استخدام أسلوب التنفيذ Execute Method من كائن الاتصال لإرجاع السجلات......فمثلا أذا اردنا أستخدام أسم ورقة العمل Worksheet Name لتحديد البيانات من المصنف فى تلك الطريقة نستخدم الكود التالى :
PHP كود :
Set rst cnn.Execute("[Sheet1$]")
Text1.Text rst![الاسم

أستخدام النطاق المسمى ....نستخدم الكود التالى :

PHP كود :
Set rst cnn.Execute("[StaffData]"

و استخدام النطاق غير المسمى ....بأستخدام الكود التالى :


PHP كود :
Set rst cnn.Execute("[Sheet1$A1:D50]"

ثالثا : حل مشكلة أنواع البيانات فى ورقة عمل إكسل

مصنفات إكسل ليست قاعدة البيانات، لذلك لا تتوافر معلومات لدى تقنية ADO حول أنواع البيانات لكل عمود / حقل فى ورقة عمل إكسل.......وكل ما هنالك أنه عند فتح جدول Excel، سوف يقوم المزود Jet بفحص الثمانى صفوف الاولى من الجدول ومحاولة لتخمين نوع البيانات المحتواه فى الجدول ......

هذا يعمل بشكل جيد طالما أن البيانات ليست مختلطة، أي ما دام كل عمود يحتوي على بيانات من نوع واحد فقط. أما إذا كان العمود به أكثر من نوع من أنواع البيانات فإن يقوم بتحديد نوع البيانات على اساس الاغلبية ......على سبيل المثال، إذا كانت البيانات به عبارة عن سلاسل نصية و أرقام وأن القيم النصية أكثر من القيم الرقمية فإنه سيتم قراءة البيانات النصية بشكل صحيح ولكن سيتم استبدال القيم الرقيمة بقيم خالية null

ويمكننا حل تلك المشكلة حتى يتسنى لجميع البيانات أن يتم قراءتها كنص وذلك من خلال إعداد خاصية موسعة فيتم إضافة الرمز IMEX = 1 و التى تخبر المزود بأن البيانات ستكون نصية ....و ذلك بالشكل التالى :
PHP كود :
Dim cnn As New ADODB.Connection
Dim rst 
As New ADODB.Recordset

cnn
.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
"Data Source=C:\tst.xlsx;" _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";" 

رابعا : العمليات على مصنف إكسل إضافة / تعديل / حذف بيانات

يمكننا أستخدام عبارات sql للاضافة و التعديل فى مصنف إكسل ....كما يلى :

PHP كود :
cnn.Execute "INSERT INTO [Sheet1$](FirstName, LastName) VALUES ('Value1', 'Value2')" 

أو يمكنك أستخدام أساليب ADO العادية للاضافة و التعديل كما يلى :
PHP كود :
rs.Open "SELECT * FROM [SalesData];"cnadOpenDynamicadLockOptimistic
rs
.AddNew
rs
![الاسم] = Text1.Text
rs
.Update 


و تكون العملية أكثر تقييدا ​​في حذف بيانات Excel من البيانات من مصدر البيانات العلائقية.....وذلك في قاعدة بيانات علائقية، "الصف" ليس له معنى أو وجود وبصرف النظر عن "السجل"؛ في ورقة عمل Excel، ......لذلك يمكنك حذف القيم في الحقول (الخلايا). ومع ذلك، لا يمكنك:حذف سجل بأكمله مرة واحدة ......لذلك سنقوم فقط بحذف سجل عن طريق المسح إلى محتويات كل حقل على حدة.حذف القيمة في خلية تحتوي على صيغة Excel

خامساً : إنشاء ورقة عمل و مصنفات إكسل جديدة

1_ إنشاء ورقة عمل worksheets


يمكنك إضافة أوراق عمل جديدة worksheets إلى ملفات Excel الموجودة لديك و ذلك باستخدام عبارات SQL .....وفيما يلي الشفرة التى توضح كيفية إنشاء ورقة جديدة باسم Sheet2 في الملف المسمى tst.xlsx ونسخ محتويات SHEET1 إلى الورقة الجديدة:

PHP كود :
SQL "SELECT * INTO [C:\tst.xlsx].[Sheet2] FROM [Sheet1$]"
cnn.Execute SQL 


2_ إنشاء مصنف workbooks

مع تعديلات طفيفة،على الشفرة السابقة نستطيع أن نعمل لإنشاء مصنفات جديدة كذلك. نحن بحاجة فقط لإضافة القليل من الشفرات التى تخبر ADO أن قاعدة البيانات الجديدة يجب أن يكون مصنف Excel ....على النحو التالى :

PHP كود :
SQL "SELECT * INTO [Excel 12.0;DATABASE=C:\tst.xlsx].[Sheet1] FROM [Sheet1$]"
cnn.Execute SQL 
وحتى إن لم يكن لديك برنامج إكسل مثبت على جهازك فبالطريقة السابقة يمكنك إنشاء ملف إكسل
منتدى مصر التقنى ....شارك معنا
http://egy-tech.forumegypt.net
الرد }}}
تم الشكر بواسطة: محمود رغمان , kasim alatwani
#2
شكرا و بارك الله فيك
الرد }}}
تم الشكر بواسطة:



التنقل السريع :


يقوم بقرائة الموضوع: بالاضافة الى ( 1 ) ضيف كريم