أتمتة المهام ببايثون قراءة مستندات جداول إكسل باستخدام لغة بايثون Python

قد لا نفكر في جداول البيانات بوصفها أدوات برمجية، ولكن يستخدمها الجميع تقريبًا لتنظيم المعلومات في هياكل بيانية ثنائية الأبعاد، وإجراء العمليات الحسابية باستخدام الصيغ، وعرض المخرجات على شكل مخططات، لذا سندمج لغة بايثون مع تطبيقين شائعين خاصين بجداول البيانات وهما: مايكروسوفت إكسل Microsoft Excel وجداول بيانات جوجل Google Sheets.

يُعَد إكسل تطبيق جداول بيانات قوي وشائع الاستخدام لنظام تشغيل ويندوز، وتوجد وحدةٌ برمجية هي وحدة openpyxl التي تسمح لبرامجك المكتوبة بلغة بايثون بقراءة وتعديل ملفات جداول بيانات إكسل. يمكن أن تكون لديك مهمة مملة متمثلة في نسخ بيانات معينة من جدول بيانات ولصقها في جدول بيانات آخر مثلًا، أو قد يتعين عليك أن تمر على آلاف الصفوف واختيار عددٍ قليل منها لإجراء تعديلات بسيطة بناءً على بعض المعايير، أو قد يتعين عليك الاطلاع على مئات جداول البيانات الخاصة بميزانيات الأقسام باحثًا عن الخلايا الملونة باللون الأحمر، وهذه هي مهام جداول البيانات البسيطة والمملة التي يمكن لبايثون تطبيقها نيابةً عنك.

يُعَد برنامج إكسل برنامجًا خاصًا بشركة مايكروسوفت، ولكن توجد بدائل مجانية تعمل على أنظمة تشغيل ويندوز وماك macOS ولينكس Linux، حيث يعمل كل من ليبرأوفيس كالك LibreOffice Calc وأوبن أوفيس كالك OpenOffice Calc بنجاح مع صيغة ملفات جداول البيانات ‎.xlsx الخاصة بإكسل، مما يعني أن الوحدة openpyxl يمكن أن تعمل مع جداول البيانات الخاصة بهذين التطبيقين أيضًا، ويمكنك تنزيلهما من موقعهما الرسمي مباشرةً. قد تجد أن هذين البرنامجين أسهل في الاستخدام من إكسل بالرغم من أن برنامج إكسل مُثبَّتٌ مسبقًا على حاسوبك الشخصي، ولكن لقطات الشاشة الموجودة في هذا المقال جميعها مأخوذة من إكسل 2010 على نظام تشغيل ويندوز 10.

مستندات إكسل

لنتعرّف أولًا على بعض التعريفات الأساسية، حيث يُسمَّى مستند جدول بيانات إكسل بالمصنف Workbook، ويُحفَظ المصنف في ملف امتداده ‎.xlsx، ويمكن أن يحتوي المصنف على أوراق Sheets متعددة (وتسمَّى أوراق عمل Worksheets أيضًا)، كما تُسمَّى الورقة التي يعرضها المستخدم حاليًا -أو المعروضة آخر مرة قبل إغلاق إكسل- بالورقة النشطة Active Sheet.

تحتوي كل ورقة على أعمدة Columns تتعامل معها باستخدام حروف تبدأ بالحرف A، وصفوف Rows تتعامل معها باستخدام أعداد تبدأ بالعدد 1. يسمى المربع الموجود في عمود أو صف معين بالخلية Cell، ويمكن أن تحتوي كل خلية على قيمة عددية أو نصية، وتتشكّل الورقة من شبكةٍ من الخلايا التي تحتوي على البيانات.

تثبيت وحدة openpyxl

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

سنستخدم في هذا المقال الإصدار 2.6.2 من الوحدة OpenPyXL، لذا من المهم أن تثبّت هذا الإصدار من خلال تشغيل الأمرpip install --user -U openpyxl==2.6.2، لأن الإصدارات الأحدث منها غير متوافقة مع المعلومات الموجودة في هذا المقال. أدخِل الأمر التالي في الصدفة التفاعلية Interactive Shell لاختبار ما إذا كان كانت وحدة OpenPyXL مُثبَّتة بصورة صحيحة:

>>> import openpyxl

إذا جرى تثبيت الوحدة بصورة صحيحة، فلن ينتج عن الأمر السابق أيّ رسائل خطأ. تذكّر استيراد الوحدة openpyxl قبل تشغيل أمثلة أوامر الصدفة التفاعلية في هذا المقال، وإلّا فستحصل على الخطأ NameError: name 'openpyxl' is not defined.

ملاحظة: يمكنك العثور على توثيق وحدة OpenPyXL الكامل على موقعها الرسمي.

قراءة مستندات إكسل

سنستخدم في الأمثلة الواردة في هذا المقال جدول بيانات اسمه example.xlsx مُخزَّن في المجلد الجذر، حيث يمكنك إما إنشاء جدول البيانات بنفسك أو تنزيله. يوضّح الشكل التالي تبويبات للأوراق الافتراضية الثلاثة التي اسمها Sheet1 و Sheet2 و Sheet3 التي يوفرها إكسل تلقائيًا للمصنفات الجديدة، ولكن قد يختلف عدد هذه الأوراق الافتراضية بحسب نظام التشغيل وبرنامج جداول البيانات:

توجد التبويبات الخاصة بأوراق المصنف في الزاوية السفلية اليسرى من إكسل

يجب أن تبدو الورقة 1 في مثالنا مثل الجدول التالي، ولكن إن لم تنزّل الملف example.xlsx من الموقع، فيجب أن تدخِل هذه البيانات في الورقة بنفسك:

أصبح جدول البيانات جاهزًا الآن، إذًا لنتعرّف على كيفية التعامل معه باستخدام وحدة openpyxl.

فتح مستندات إكسل باستخدام وحدة OpenPyXL

يمكنك استخدام الدالة openpyxl.load_workbook()‎ بعد استيراد وحدة openpyxl مباشرةً، لذا أدخِل ما يلي في الصدفة التفاعلية:

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> type(wb)
<class 'openpyxl.workbook.workbook.Workbook'>

تأخذ الدالة openpyxl.load_workbook()‎ اسم الملف وتعيد قيمة نوع بيانات المصنف workbook، حيث يمثل الكائن Workbook ملف إكسل، ويشبه ذلك كيفية تمثيل الكائن File ملفًا نصيًا مفتوحًا.

تذكّر أن الملف example.xlsx يجب أن يكون موجودًا في مجلد العمل الحالي لتتمكّن من التعامل معه، إذ يمكنك معرفة مجلد العمل الحالي من خلال استيراد وحدة os واستخدام الدالة os.getcwd()‎، ويمكنك تغيير مجلد العمل الحالي باستخدام الدالة os.chdir()‎.

الحصول على الأوراق من المصنف

يمكنك الحصول على قائمة بجميع أسماء الأوراق في المصنف من خلال الوصول إلى السمة Attribute التي هي sheetnames، لذا أدخِل ما يلي في الصدفة التفاعلية:

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> wb.sheetnames # أسماء الأوراق الخاصة بالمصنف
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb['Sheet3'] # الحصول على ورقة من المصنف
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title # الحصول على عنوان الورقة بوصفه سلسلة نصية
'Sheet3'
>>> anotherSheet = wb.active # الحصول على الورقة النشطة
>>> anotherSheet
<Worksheet "Sheet1">

يمثّل الكائنُ Worksheet الورقة التي يمكنك الحصول عليها باستخدام الأقواس المربعة مع السلسلة النصية التي تمثّل اسم هذه الورقة، حيث يشبه ذلك استخدام مفتاح القاموس، ويمكنك استخدام السمة active للكائن Workbook للحصول على ورقة المصنف النشطة، فالورقة النشطة هي الورقة الموجودة في المقدمة عند فتح المصنف في إكسل. كما يمكنك الحصول على اسم الكائن Worksheet من سمة العنوان title بعد الحصول على هذا الكائن.

الحصول على الخلايا من الأوراق

يمكنك الوصول إلى الكائن Cell باستخدام اسمه بعد الحصول على الكائن Worksheet، لذا أدخِل ما يلي في الصدفة التفاعلية:

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1'] # الحصول على ورقة من المصنف
>>> sheet['A1'] # الحصول على خلية من الورقة
<Cell 'Sheet1'.A1>
>>> sheet['A1'].value # الحصول على القيمة من الخلية
datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> c = sheet['B1'] # الحصول على خلية أخرى من الورقة
>>> c.value
'Apples'
>>> # الحصول على الصف والعمود والقيمة من الخلية
>>> 'Row %s, Column %s is %s' % (c.row, c.column, c.value)
'Row 1, Column B is Apples'
>>> 'Cell %s is %s' % (c.coordinate, c.value)
'Cell B1 is Apples'
>>> sheet['C1'].value
73

يحتوي كائن الخلية Cell على سمة القيمة value التي تحتوي على القيمة المُخزَّنة في هذه الخلية، وتحتوي الكائنات Cell أيضًا على سمات الصف row والعمود column والإحداثيات coordinate التي توفّر معلومات موقع الخلية، فمثلًا يعطينا الوصول إلى السمة value للكائن Cell الخاص بالخلية B1 السلسلة النصية 'Apples'، وتعطينا السمة row العدد الصحيح 1، وتعطينا السمة column العمود 'B'، وتعطي السمة coordinate القيمة 'B1'.

تفسّر الوحدة OpenPyXL تلقائيًا التواريخ الموجودة في العمود A وتعيدها بوصفها قيم datetime بدلًا من إعادتها كسلاسل نصية، حيث سنوضّح لاحقًا نوع البيانات datetime.

يمكن أن يكون تحديد عمود باستخدام حرف أمرًا صعبًا برمجيًا، وخاصةً لأن الأعمدة تبدأ باستخدام حرفين مثل AA و AB و AC بعد العمود Z، لذا يمكنك بدلًا من ذلك الحصول على خلية باستخدام التابع cell()‎ الخاص بالورقة وتمرير أعداد صحيحة لوسطاء الكلمات المفتاحية Keyword Arguments التي هي row و column الخاصة بهذا التابع، ويكون العدد الصحيح للصف أو العمود الأول هو 1 وليس 0. لندخِل ما يلي في الصدفة التفاعلية:

sheet.cell(row=1, column=2)
<Cell 'Sheet1'.B1>
>>> sheet.cell(row=1, column=2).value
'Apples'
>>> for i in range(1, 8, 2): # المرور على جميع الصفوف الأخرى
...     print(i, sheet.cell(row=i, column=2).value)
...
1 Apples
3 Pears
5 Apples
7 Strawberries

لاحظ أن استخدام التابع cell()‎ الخاص بالورقة وتمرير row=1 و column=2 له يعطي كائن Cell للخلية B1 كما فعل استخدام sheet['B1']‎ تمامًا. يمكنك بعد ذلك كتابة حلقة for لطباعة قيم سلسلة من الخلايا باستخدام التابع cell()‎ ووسطاء الكلمات المفتاحية الخاصة به.

لنفترض أنك تريد الانتقال إلى العمود B وطباعة القيمة الموجودة في جميع الخلايا التي يكون رقم صفها عددًا فرديًا، حيث يمكنك الحصول على الخلايا لجميع الصفوف لها أرقام فردية من خلال تمرير القيمة 2 لمعامل "الخطوة step" الخاص بالدالة range()‎. يُمرَّر المتغير i الخاص بالحلقة for إلى وسيط الكلمة المفتاحية row الخاص بالتابع cell()‎، بينما تُمرّر القيمة 2 دائمًا إلى وسيط الكلمة المفتاحية column في هذه الحالة. لاحظ أننا مرّرنا العدد الصحيح 2 ولم نمرّر السلسلة النصية 'B'.

يمكنك تحديد حجم الورقة باستخدام السمتين max_row و max_column للكائن Worksheet كما يلي:

import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet.max_row # الحصول على عدد الصفوف الأكبر
7
>>> sheet.max_column # الحصول على عدد الأعمدة الأكبر
3

لاحظ أن السمة max_column تمثل عددًا صحيحًا ولا تمثّل الحرف الذي يظهر في إكسل.

تحويل حروف الأعمدة إلى أعداد

يمكنك تحويل أسماء الأعمدة من حروف إلى أعداد من خلال استدعاء الدالة openpyxl.utils.column_index_from_string()‎، ويمكنك التحويل من أعداد إلى حروف من خلال استدعاء الدالة openpyxl.utils.get_column_letter()‎. إذًا لندخِل ما يلي في الصدفة التفاعلية:

>>> import openpyxl
>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(1) # ترجمة العمود 1 إلى حرف
'A'
>>> get_column_letter(2)
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> get_column_letter(sheet.max_column)
'C'
>>> column_index_from_string('A') # الحصول على العدد‫ المقابل للحرف A
1
>>> column_index_from_string('AA')
27

يمكنك استدعاء الدالة get_column_letter()‎ وتمرير عدد صحيح لها مثل العدد 27 لمعرفة اسم الحرف في العمود السابع والعشرين بعد استيراد الدالتين السابقتين من الوحدة openpyxl.utils. بينما تطبّق الدالة column_index_string()‎ العكس، حيث تمرّر لها الحرف الذي يمثل اسم العمود، وتعطيك رقم هذا العمود. لا تحتاج إلى تحميل مصنف لاستخدام هذه الدوال، ولكن إن أردت يمكنك تحميل مصنف، ثم الحصول على الكائن Worksheet واستخدام سمته max_column مثلًا للحصول على عدد صحيح، ثم يمكنك تمرير هذا العدد الصحيح إلى الدالة get_column_letter()‎.

الحصول على الصفوف والأعمدة من الأوراق

يمكنك تقسيم الكائنات Worksheet للحصول على كافة الكائنات Cell الموجودة في صف أو عمود أو منطقة مستطيلة من جدول البيانات، ثم يمكنك التكرار على جميع الخلايا الموجودة في كل قسم. أدخِل ما يلي في الصدفة التفاعلية:

   >>> import openpyxl
   >>> wb = openpyxl.load_workbook('example.xlsx')
   >>> sheet = wb['Sheet1']
   >>> tuple(sheet['A1':'C3']) # الحصول على ج‫ميع الخلايا من A1 إلى C3
   ((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), (<Cell
   'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>), (<Cell 'Sheet1'.A3>,
   <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))
➊ >>> for rowOfCellObjects in sheet['A1':'C3']:
➋ ...     for cellObj in rowOfCellObjects:
   ...         print(cellObj.coordinate, cellObj.value)
   ...     print('--- نهاية الصف ---')

   A1 2015-04-05 13:34:02
   B1 Apples
   C1 73
   --- نهاية الصف ---
   A2 2015-04-05 03:41:23
   B2 Cherries
   C2 85
   --- نهاية الصف ---
   A3 2015-04-06 12:46:51
   B3 Pears
   C3 14
   --- نهاية الصف —

حدّدنا في المثال السابق أننا نريد كائنات Cell في المنطقة المستطيلة من الخلية A1 إلى الخلية C3، وحصلنا على كائن Generator الذي يحتوي على كائنات Cell في تلك المنطقة، حيث يمكننا تصوّر الكائن Generator من خلال استخدام الدالة tuple()‎ معه لعرض كائنات Cell الخاصة به ضمن مجموعة Tuple.

تحتوي هذه المجموعة على ثلاث مجموعات أخرى، مجموعة لكل صف من أعلى المنطقة المطلوبة إلى أسفلها، حيث تحتوي كل مجموعة من هذه المجموعات الداخلية الثلاث على كائنات Cell في صف واحد من المنطقة المطلوبة من الخلية الموجودة في أقصى اليسار إلى اليمين. يحتوي قسم الورقة الذي حدّدناه على جميع كائنات Cell في المنطقة المؤلفة من الخلية A1 إلى الخلية C3، بدءًا من الخلية العلوية اليسرى وانتهاءً بالخلية السفلية اليمنى. طبعنا قيم كل خلية في هذه المنطقة باستخدام حلقتي for، حيث تتكرر حلقة for الخارجية على كل صف في القسم ➊، ثم تتكرر حلقة for المتداخلة لكل صف على كل خلية في هذا الصف ➋.

يمكن أيضًا الوصول إلى قيم الخلايا في صف أو عمود معين من خلال استخدام سمة rows و columns الخاصة بكائن Worksheet، ولكن يجب تحويل هذه السمات إلى قوائم باستخدام الدالة list()‎ قبل أن تتمكّن من استخدام الأقواس المربعة والفهرس معها. إذًا لندخِل ما يلي في الصدفة التفاعلية:

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.active
>>> list(sheet.columns)[1] # الحصول على خلايا العمود الثاني
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.
B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
>>> for cellObj in list(sheet.columns)[1]:
        print(cellObj.value)

Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

سيؤدي استخدام السمة rows مع الكائن Worksheet إلى إعطاء مجموعة من عدة مجموعات، وتمثل كل مجموعة من هذه المجموعات الداخلية صفًا، وتحتوي على كائنات Cell الموجودة في هذا الصف. تعطي السمة columns أيضًا مجموعة من عدة مجموعات، حيث تحتوي كل مجموعة من المجموعات الداخلية على كائنات Cell في عمود معين. لدينا في مثالنا الملف example.xlsx الذي يحتوي على 7 صفوف و3 أعمدة، وبالتالي تعطي السمة rows مجموعة مؤلفةً من 7 مجموعات، حيث تحتوي كل منها على 3 كائنات Cell، وتعطي السمة columns مجموعة مكونة من 3 مجموعات، حيث تحتوي كل منها على 7 كائنات Cell.

يمكن الوصول إلى مجموعة معينة من خلال الإشارة إليها باستخدام فهرسها في المجموعة الكبرى، فمثلًا نحصل على المجموعة التي تمثل العمود B من خلال استخدام list(sheet.columns)[1]‎، ونحصل على المجموعة التي تحتوي على كائنات Cell في العمود A من خلال استخدام list(sheet.columns)[0]‎. يمكنك بعد أن يكون لديك مجموعة تمثل صفًا أو عمودًا واحدًا التكرار على كائنات Cell الخاصة بها وطباعة قيمها.

المصنفات والأوراق والخلايا

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

1. استيراد وحدة openpyxl.
2. استدعاء الدالة openpyxl.load_workbook()‎.
3. الحصول على كائن Workbook.
4. استخدام السمة active أو السمة sheetnames.
5. الحصول على كائن Worksheet.
6. استخدم طريقة الفهرسة أو تابع cell()‎ الخاص بالورقة مع وسطاء الكلمات المفتاحية row و column.
7. الحصول على كائن Cell.
8. قراءة السمة value الخاصة بالكائن Cell.

تطبيق عملي: قراءة البيانات من جدول بيانات

لنفترض أن لديك جدول بيانات يمثّل الإحصاء السكاني للولايات المتحدة الأمريكية لعام 2010، ولديك مهمة مملة تتمثل في استعراض آلاف الصفوف لحساب إجمالي عدد السكان وعدد المناطق الإحصائية Census Tracts لكل مقاطعة County، فالمنطقة الإحصائية هي ببساطة منطقة جغرافية محددة لأغراض الإحصاء السكاني، ويمثل كل صف في جدول البيانات منطقةً إحصائية واحدة. سنسمّي ملف جدول البيانات censuspopdata.xlsx الذي يمكنك تنزيله، وتبدو محتوياته كما يلي:

جدول بيانات censuspopdata.xlsx

يستطيع إكسل حساب مجموع خلايا محددة متعددة، ولكن يجب عليك أيضًا تحديد الخلايا التي تمثل المقاطعات التي يزيد عدد سكانها عن 3000 نسمة. كما قد يستغرق حساب عدد سكان المقاطعة يدويًا بضع ثوانٍ فقط، ولكنه قد يستغرق ساعات لجدول البيانات بأكمله.

ستكتب في هذا التطبيق العملي سكربتًا يمكنه القراءة من ملف جدول بيانات الإحصاء السكاني وحساب إحصائيات كل مقاطعة في غضون ثوانٍ، إذ سيفعل برنامجك ما يلي:

1. يقرأ البيانات من جدول بيانات إكسل.
2. يحسب عدد المناطق الإحصائية في كل مقاطعة.
3. يحسب إجمالي عدد السكان في كل مقاطعة.
4. يطبع النتائج.

وهذا يعني أن شيفرتك البرمجية ستحتاج ما يلي:

1. فتح وقراءة خلايا مستند إكسل باستخدام وحدة openpyxl.
2. حساب جميع بيانات المناطق الإحصائية وعدد السكان وتخزينها في هيكل بيانات.
3. كتابة هيكل البيانات في ملف نصي له الامتداد ‎.py باستخدام الوحدة pprint.

الخطوة الأولى: قراءة بيانات جدول البيانات

توجد ورقة واحدة فقط في جدول البيانات censuspopdata.xlsx، تسمى "عدد السكان حسب المنطقة الإحصائية" 'Population by Census Tract'، ويحتوي كل صف على بيانات منطقة إحصائية واحدة، والأعمدة هي رقم المنطقة (A) واختصار الولاية (B) واسم المقاطعة (C) وعدد سكان المنطقة (D).

افتح تبويبًا جديدًا لإنشاء ملف جديد في محرّرك وأدخِل الشيفرة البرمجية التالية، واحفظ الملف بالاسم readCensusExcel.py:

#! python3
   # readCensusExcel.py - جدول عدد السكان وعدد المناطق الإحصائية لكل مقاطعة

➊ import openpyxl, pprint
   print('Opening workbook...')
➋ wb = openpyxl.load_workbook('censuspopdata.xlsx')
➌ sheet = wb['Population by Census Tract']
   countyData = {}

   # املأ بي‫انات المقاطعة countyData بعدد سكان كل مقاطعة ومناطقها الإحصائية
   print('Reading rows...')
➍ for row in range(2, sheet.max_row + 1):
       # يحتوي كل صف في جدول البيانات على بياناتٍ لمنطقة إحصائية واحدة
       state  = sheet['B' + str(row)].value
       county = sheet['C' + str(row)].value
       pop    = sheet['D' + str(row)].value

# افتح ملفًا نص‫يًا جديدًا واكتب محتويات بيانات المقاطعة countyData فيه

تستورد الشيفرة البرمجية السابقة الوحدة openpyxl والوحدة pprint التي ستستخدمها لطباعة بيانات المقاطعة النهائية ➊، ثم تفتح الملف censuspopdata.xlsx ➋، وتحصل على الورقة التي تحتوي على البيانات الإحصائية ➌، وتبدأ بالتكرار على صفوف هذه الورقة ➍.

لاحظ أنك أنشأتَ أيضًا متغيرًا بالاسم countyData، والذي سيحتوي على عدد السكان وعدد المناطق التي تحسبها لكل مقاطعة، ولكن يجب عليك تحديد كيفية هيكلة البيانات بداخله قبل أن تتمكن من تخزين أيّ شيء فيه.

الخطوة الثانية: ملء هيكل البيانات

يُعَد هيكل البيانات المُخزَّن في المتغير countyData قاموسًا تكون اختصارات أسماء الولايات مفاتيحًا له، حيث سيُربَط اختصار كل ولاية مع قاموس آخر مفاتيحه هي سلاسل نصية تمثّل أسماء المقاطعات في تلك الولاية، وسيُربَط كل اسم مقاطعة بدوره مع قاموسٍ آخر يحتوي على مفتاحين فقط هما 'tracts' و 'pop'، ويُربَط هذان المفتاحان مع عدد المناطق الإحصائية وعدد السكان في المقاطعة، فمثلًا سيبدو القاموس مشابهًا لما يلي:

{'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},
        'Aleutians West': {'pop': 5561, 'tracts': 2},
        'Anchorage': {'pop': 291826, 'tracts': 55},
        'Bethel': {'pop': 17013, 'tracts': 3},
        'Bristol Bay': {'pop': 997, 'tracts': 1},
        --snip–

إذا خُزِّن القاموس السابق في المتغير countyData، فيمكن تقييم التعابير التالية كما يلي:

>>> countyData['AK']['Anchorage']['pop']
291826
>>> countyData['AK']['Anchorage']['tracts']
55

وستكون مفاتيح قاموس countyData كما يلي:

countyData[state abbrev][county]['tracts']
countyData[state abbrev][county]['pop']

عرفتَ كيفية تنظيم هيكل بيانات countyData، ويمكنك الآن كتابة الشيفرة البرمجية التي ستملؤه ببيانات المقاطعة، لذا أضِف الشيفرة البرمجية التالية إلى نهاية برنامجك:

#! python 3
# readCensusExcel.py - جدول عدد السكان وعدد المناطق الإحصائية لكل مقاطعة

--snip--

for row in range(2, sheet.max_row + 1):
     # يحتوي كل صف في جدول البيانات على بيانات لمنطقة إحصائية واحدة
     state  = sheet['B' + str(row)].value
     county = sheet['C' + str(row)].value
     pop    = sheet['D' + str(row)].value

     # تأكد من ‫وجود مفتاح هذه الولاية state
  ➊ countyData.setdefault(state, {})
     # تأكد ‫من وجود مفتاح هذه المقاطعة county في تلك الولاية
  ➋ countyData[state].setdefault(county, {'tracts': 0, 'pop': 0})

     # يمثل كل صف منطقة إحصائية واحدة، لذا يجب زيادة عدد المناطق بمقدار واحد
  ➌ countyData[state][county]['tracts'] += 1
     # زي‫ادة عدد سكان pop المقاطعة بمقدار عدد السكان في هذه المنطقة الإحصائية
  ➍ countyData[state][county]['pop'] += int(pop)

# افتح ملفًا نص‫يًا جديدًا واكتب محتويات بيانات المقاطعة countyData فيه

يجري السطران الأخيران من الشيفرة البرمجية السابقة العمليات الحسابية الفعلية، حيث تزيد قيمة المناطق الإحصائية tracts ➌ وقيمة عدد السكان pop ➍ للمقاطعة الحالية في كل تكرار لحلقة for. بينما سببُ وجود الشيفرة البرمجية المتبقية هو أنه لا يمكنك إضافة قاموس المقاطعة بوصفه قيمةً لمفتاح اختصار الولاية إلّا عند وجود المفتاح نفسه في countyData، إذ ستتسبّب التعليمة countyData['AK']['Anchorage']['tracts'] += 1 في حدوث خطأ إن لم يكن المفتاح 'AK' موجودًا بعد. يمكنك التأكد من وجود مفتاح اختصار الولاية في هيكل بياناتك من خلال استدعاء التابع setdefault()‎ لضبط قيمة الولاية state ➊ إن لم تكن موجودة مسبقًا.

يحتاج قاموس countyData إلى قاموس آخر بوصفه قيمةً لكل مفتاح يمثّل اختصار الولاية، وبالتالي سيحتاج كلٌّ من هذه القواميس إلى قاموس خاص به بوصفه قيمة لكل مفتاح مقاطعة ➋، وسيحتاج كل من هذه القواميس بدوره إلى مفاتيح 'tracts' و 'pop' التي تبدأ بالقيمة الصحيحة 0. إذا شعرت بالضياع عند تتبّع بنية القاموس، فارجع إلى مثال القاموس في بداية هذه الفقرة.

لن يفعل التابع setdefault()‎ شيئًا إذا كان المفتاح موجودًا مسبقًا، وبالتالي يمكنك استدعاؤه في كل تكرار للحلقة for بدون مشاكل.

الخطوة الثالثة: كتابة النتائج في ملف

سيحتوي قاموس countyData بعد انتهاء حلقة for على جميع معلومات عدد السكان والمناطق المرتبطة بمفتاح المقاطعة county والولاية state، ويمكنك عندها برمجة مزيدٍ من الشيفرة البرمجية لكتابة هذه المعلومات في ملف نصي أو جدول بيانات إكسل آخر. لنستخدم الآن الدالة pprint.pformat()‎ لكتابة قيمة قاموس countyData بوصفها سلسلة نصية ضخمة في ملف اسمه census2010.py، لذا أضِف الشيفرة البرمجية التالية إلى نهاية برنامجك، وتأكد من إبقائه بدون مسافة بادئة بحيث يبقى خارج حلقة for:

#! python 3
# readCensusExcel.py - جدول عدد السكان وعدد المناطق الإحصائية لكل مقاطعة

--snip--

for row in range(2, sheet.max_row + 1):
--snip--

# افتح ملفًا نص‫يًا جديدًا واكتب محتويات بيانات المقاطعة countyData فيه
print('Writing results...')
resultFile = open('census2010.py', 'w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')

ينتج عن الدالة pprint.pformat()‎ سلسلةٌ نصية مُنسَّقة كشيفرة بايثون صالحة، والتي يمكنك إخراجها إلى ملف نصي اسمه census2010.py، وبالتالي سيتولد برنامج بايثون من برنامج بايثون الخاص بك. قد يبدو ذلك معقدًا، ولكن تتمثّل الفائدة في أنه يمكنك استيراد الملف census2010.py مثل أي وحدة بايثون أخرى. غيّر مجلد العمل الحالي إلى المجلد الذي يحتوي على الملف census2010.py ثم استورده في الصدفة التفاعلية كما يلي:

>>> import os
>>> import census2010
>>> census2010.allData['AK']['Anchorage']
{'pop': 291826, 'tracts': 55}
>>> anchoragePop = census2010.allData['AK']['Anchorage']['pop']
>>> print('The 2010 population of Anchorage was ' + str(anchoragePop))
The 2010 population of Anchorage was 291826

يُعَد برنامج readCensusExcel.py عديم الجدوى، فلا حاجة لتشغيله مرة أخرى بعد حفظ نتائجه في الملف census2010.py، ويمكنك تشغيل الأمر import census2010 عندما تحتاج إلى بيانات المقاطعة.

سيستغرق حساب هذه البيانات يدويًا ساعات يدويًا، ولكن أنجز هذا البرنامج هذا الأمر في بضع ثوان، ولن تواجه أيّ مشكلة في استخراج المعلومات المحفوظة في جدول بيانات إكسل وإجراء العمليات الحسابية عليها باستخدام وحدة OpenPyXL.

ملاحظة: لا تنسَ أنه يمكنك تنزيل البرنامج الكامل.

أفكار لبرامج مماثلة

تستخدم العديد من الشركات والمكاتب برنامج إكسل لتخزين أنواع مختلفة من البيانات، وتصبح جداول البيانات كبيرة الحجم وغير عملية بسهولة. تمتلك البرامج التي تحلّل جدول بيانات إكسل بنية مماثلة، فهي تحمّل ملف جدول البيانات، وتجهّز بعض المتغيرات أو هياكل البيانات، ثم تتكرر على كل صف من الصفوف في جدول البيانات، حيث يمكن أن تفعل مثل هذه البرامج ما يلي:

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

الخلاصة

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

ترجمة -وبتصرُّف- للقسم Reading Excel Documents من مقال Working with Excel Spreadsheets لصاحبه Al Sweigart.

اقرأ أيضًا

• المقال السابق: استخراج البيانات من الويب عبر لغة بايثون Python
• قراءة وكتابة الملفات باستخدام لغة بايثون Python
• التعامل مع الملفات والمسارات في بايثون
• كيفية التعامل مع الملفات النصية في بايثون 3
• نظرة عامة على برنامج مايكروسوفت إكسل Microsoft Excel
• النسخة العربية الكاملة من كتاب البرمجة بلغة بايثون