Geekly articles weekly

تفريغ البيانات في Excel. متحضر

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





ما هي احتمالات بناء ملفات Excel الموجودة من حيث المبدأ؟

  1. وحدات ماكرو VBA. في الوقت الحاضر ، لأسباب أمنية ، غالبًا ما تكون فكرة استخدام وحدات الماكرو غير مناسبة.
  2. أتمتة Excel مع برنامج خارجي من خلال API. يتطلب Excel على نفس الجهاز الذي يعمل فيه البرنامج على إنشاء تقارير Excel. في الوقت الذي كان فيه العملاء كثيرين ومكتوبين في شكل تطبيقات Windows لسطح المكتب ، كانت هذه الطريقة مناسبة (على الرغم من أنها لا تختلف في السرعة والموثوقية) ، في الواقع الحالي ، هذه حالة لا يمكن تحقيقها.
  3. إنشاء ملف XML-Excel مباشرة. كما تعلم ، يدعم Excel تنسيق XML لحفظ المستند ، والذي من المحتمل أن يتم إنشاؤه / تعديله باستخدام أي وسيلة للعمل مع XML. يمكن حفظ هذا الملف بامتداد .xls ، وعلى الرغم من أنه ليس ملف xls ، إلا أن Excel يفتحه جيدًا. هذا النهج شائع جدًا ، لكن العيوب تشمل حقيقة أن أي حل يعتمد على التحرير المباشر لتنسيق XML-Excel هو "اختراق" لمرة واحدة يخلو من العمومية.
  4. أخيرًا ، من الممكن إنشاء ملفات Excel باستخدام مكتبات مفتوحة المصدر ، والتي تشتهر بها Apache POI بشكل خاص. قام مطورو Apache POI بعمل عملاق على تنسيقات مستندات MS Office الثنائية الهندسية العكسية ، ويستمرون في صيانة هذه المكتبة وتطويرها على مر السنين. يتم استخدام نتيجة هذه الهندسة العكسية ، على سبيل المثال ، في Open Office لتنفيذ حفظ المستندات بتنسيقات متوافقة مع MS Office.

في رأيي ، إنها آخر الطرق المفضلة الآن لإنشاء مستندات متوافقة مع MS Office. من ناحية ، لا يتطلب تثبيت أي برنامج خاص على الخادم ، ومن ناحية أخرى ، فإنه يوفر واجهة برمجة تطبيقات غنية تسمح لك باستخدام جميع وظائف MS Office.

لكن الاستخدام المباشر لـ Apache POI له عيوبه. أولاً ، هذه مكتبة جافا ، وإذا كان تطبيقك مكتوبًا بأكثر من لغة من لغات JVM ، فلا يمكنك استخدامه. ثانيًا ، إنها مكتبة منخفضة المستوى تعمل مع مفاهيم مثل "خلية" ، "عمود" ، "خط". لذلك ، فإن "الإجراءات المباشرة" المكتوبة لإنشاء مستند تتحول بسرعة إلى "شعيرية" وفيرة من التعليمات البرمجية التي يصعب قراءتها ، حيث لا يوجد فصل في نموذج البيانات والعرض التقديمي ، فمن الصعب إجراء تغييرات ، وبوجه عام ، الألم والخجل. ومناسبة ممتازة لتفويض المهمة للمبرمج الأكثر خبرة - دعه يختار.

ولكن يمكن أن يكون مختلفًا تمامًا. يعتمد مشروع Xylophone تحت LGPL ، المبني على أساس Apache POI ، على فكرة لها تاريخ يقدر بـ 15 عامًا. في المشاريع التي شاركت فيها ، تم استخدامها بالاشتراك مع مجموعة متنوعة من المنصات واللغات - وبسبب تنوع الأشكال التي تم تقديمها بمساعدتها في مجموعة متنوعة من المشاريع ، ربما يكون هناك بالفعل الآلاف. هذا مشروع جافا يمكن أن يعمل كأداة مساعدة لسطر الأوامر ومكتبة (إذا كان لديك رمز بلغة JVM ، يمكنك توصيله باعتمادية Maven).

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


قالب المستند (قالب xls / xlsx) يبدو كالتالي:


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

عند الانتهاء من عمل "التصميم" ، يبقى المطور

  1. قم بإنشاء إجراء لتنزيل البيانات الضرورية بتنسيق XML.
  2. إنشاء واصف يصف إجراء اجتياز عناصر ملف XML ونسخ أجزاء القالب إلى التقرير الناتج
  3. ربط خلايا القالب بعناصر ملف XML باستخدام تعبيرات XPath .

مع التحميل إلى XML ، يصبح كل شيء أكثر أو أقل وضوحًا: ما عليك سوى تحديد تمثيل XML المناسب للبيانات المطلوبة لملء النموذج. ما هو الواصف؟

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

<element name="root"> <output range="A1:Z100"/> </element>

هنا الجذر هو اسم العنصر الجذر لملف بيانات XML ، والنطاق A1: Z100 هو النطاق المستطيل للخلايا من القالب الذي سيتم نسخه إلى النتيجة. علاوة على ذلك ، كما يتضح من الرسم التوضيحي السابق ، فإن حقول أحرف البدل التي يتم استبدال قيمها ببيانات من ملف XML لها التنسيق ~{XPath-} (التلدة ، القوس المجعد ، تعبير XPath بالنسبة إلى عنصر XML الحالي ، إغلاق القوس المتعرج).

ماذا لو احتجنا إلى تكرار العناصر في التقرير؟ وبطبيعة الحال ، يمكن تمثيلها كعناصر لملف بيانات XML ، ويساعد الواصف على المساعدة في تكوينها بالطريقة الصحيحة. يمكن أن يكون لتكرار العناصر في التقرير اتجاه رأسي (عندما ندرج سطور الفاتورة ، على سبيل المثال) ، وأفقيًا (عندما ندرج أعمدة التقرير التحليلي). في الوقت نفسه ، يمكننا استخدام تداخل عناصر XML لتعكس التداخل العميق التعسفي لعناصر التقرير المتكررة ، كما هو موضح في الرسم التخطيطي:


تحدد المربعات الحمراء الخلايا التي ستكون الزاوية العلوية اليسرى للجزء المستطيل التالي الذي يقوم مرسِل التقارير بإرساءه.

هناك خيار آخر ممكن لتكرار العناصر: الأوراق في مصنف Excel. تتوفر أيضًا القدرة على تنظيم مثل هذا التكرار.

فكر في مثال أكثر تعقيدًا قليلاً. لنفترض أننا بحاجة إلى الحصول على تقرير موجز مثل ما يلي:


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

testdata.xml
 <?xml version="1.0" encoding="UTF-8"?> <report> <column year="2016"/> <column year="2017"/> <column year="2018"/> <item name=" 1"> <year amount="365"/> <year amount="286"/> <year amount="207"/> </item> <item name=" 2"> <year amount="95"/> <year amount="606"/> <year amount="840"/> </item> <item name=" 3"> <year amount="710"/> <year amount="437"/> <year amount="100"/> </item> <totals> <year amount="1170"/> <year amount="1329"/> <year amount="1147"/> </totals> </report>


نحن أحرار في اختيار أسماء العلامات حسب رغبتك ، يمكن أن يكون الهيكل أيضًا تعسفيًا ، ولكن مع مراعاة سهولة التحويل إلى التقرير. على سبيل المثال ، عادةً ما أكتب القيم المعروضة على الورقة في سمات ، لأنها تبسط تعبيرات XPath (وهي مناسبة عندما تبدو مثل @ ).

سيبدو نموذج هذا التقرير كما يلي (قارن تعبيرات XPath مع أسماء السمات للعلامات المقابلة):


الآن يأتي الجزء الأكثر إثارة للاهتمام: إنشاء مقبض. نظرًا لأن هذا التقرير يتم تجميعه ديناميكيًا بالكامل تقريبًا ، يكون الوصف معقدًا إلى حد ما ، من الناحية العملية (عندما يكون لدينا فقط "رأس" المستند وخطوطه و "تذييله") كل شيء عادة ما يكون أبسط بكثير. هنا هو واصف اللازمة في هذه الحالة:

descriptor.xml
 <?xml version="1.0" encoding="UTF-8"?> <element name="report"> <!--   --> <output worksheet="" sourcesheet="1"/> <!--        --> <iteration mode="horizontal"> <element name="(before)"> <!--        --> <output range="A1"/> </element> <element name="column"> <output range="B1"/> </element> </iteration> <!--  :     ,   --> <iteration mode="vertical"> <element name="item"> <!--    -   --> <iteration mode="horizontal"> <element name="(before)"> <!--   --> <output range="A2"/> </element> <!--         --> <element name="year"> <output range="B2"/> </element> </iteration> </element> </iteration> <iteration> <element name="totals"> <iteration mode="horizontal"> <element name="(before)"> <!--   --> <output range="A3"/> </element> <!--         --> <element name="year"> <output range="B3"/> </element> </iteration> </element> </iteration> </element>


يتم وصف عناصر الواصف بالكامل في الوثائق . باختصار ، العناصر الأساسية للواصف تعني ما يلي:

  • العنصر - الانتقال إلى وضع القراءة لعنصر من ملف XML. يمكن أن يكون إما العنصر الجذر للواصف ، أو أن يكون داخل iteration . يمكن استخدام سمة name لتعيين عوامل تصفية مختلفة للعناصر ، على سبيل المثال
    • name="foo" - العناصر التي تحمل اسم العلامة foo
    • name="*" - جميع العناصر
    • name="tagname[@attribute='value']" - عناصر لها اسم محدد وقيمة سمة
    • name="(before)" ، name="(after)" - عناصر "افتراضية" تسبق التكرار وتغلق التكرار.
  • التكرار - الانتقال إلى وضع التكرار. يمكن أن يكون فقط داخل element . يمكن تعيين معلمات مختلفة ، على سبيل المثال
    • mode="horizontal" - وضع الإخراج mode="horizontal" (عمودي بشكل افتراضي)
    • index=0 - قصر التكرار على العنصر الأول الذي تمت مواجهته
  • الإخراج - التبديل إلى وضع الإخراج. السمات الرئيسية هي كما يلي:
    • ورقة sourcesheet - ورقة كتاب القالب التي يتم أخذ نطاق الإخراج منها. إذا لم يكن محددًا ، فسيتم تطبيق الورقة الحالية (آخر استخدام).
    • range - نطاق القالب المراد نسخه إلى المستند الناتج ، على سبيل المثال ، "A1: M10" أو "5: 6" أو "C: C". (يؤدي استخدام نطاقات الصفوف من النوع "5: 6" في وضع الإخراج الأفقي ونطاقات الأعمدة من النوع "C: C" في وضع الإخراج الرأسي إلى حدوث خطأ).
    • worksheet - إذا تم تعريفها ، يتم إنشاء ورقة جديدة في ملف الإخراج ويتم نقل موضع الإخراج إلى الخلية A1 من هذه الورقة. يتم استبدال قيمة هذه السمة ، والتي تساوي ثابت أو تعبير XPath ، باسم الورقة الجديدة.

في الواقع ، هناك المزيد من الخيارات في الواصف ، راجع الوثائق.

حسنًا ، حان الوقت الآن لتنزيل Xylophone وبدء الإبلاغ.
خذ الأرشيف من bintray أو Maven Central (ملحوظة: في وقت قراءة هذا المقال ، هناك إصدارات أحدث ممكنة). في مجلد / bin يوجد نص برمجي shell ، عندما تقوم بتشغيله بدون معلمات ، سترى مطالبة حول معلمات سطر الأوامر. للحصول على النتيجة ، نحتاج إلى "تغذية" هاتف إكسيليفون بجميع المكونات المعدة مسبقًا:

 xylophone -data testdata.xml -template template.xlsx -descr descriptor.xml -out report.xlsx

افتح ملف report.xlsx وتأكد من حصولنا على ما نحتاجه بالضبط:


نظرًا لأن ru.curs: تتوفر مكتبة إكسيليفون على Maven Central بموجب ترخيص LGPL ، فيمكن استخدامها بدون مشاكل في البرامج بأي لغة JVM. ربما تم الحصول على المثال الأكثر عملًا بالكامل في Groovy ، ولا يحتاج الرمز إلى تعليقات:

 @Grab('ru.curs:xylophone:6.1.3') import ru.curs.xylophone.XML2Spreadsheet baseDir = '.' new File(baseDir, 'testdata.xml').withInputStream { input -> new File(baseDir, 'report.xlsx').withOutputStream { output -> XML2Spreadsheet.process(input, new File(baseDir, 'descriptor.xml'), new File(baseDir, 'template.xlsx'), false, output) } } println 'Done.'

XML2Spreadsheet فئة XML2Spreadsheet على العديد من الإصدارات المحملة بشكل زائد من طريقة process الثابتة ، ولكن جميعها تختصر لنقل "المكونات" نفسها اللازمة لإعداد التقرير.

أحد الخيارات المهمة التي لم أذكرها بعد هو القدرة على الاختيار بين موزعي DOM و SAX في مرحلة تحليل ملف ببيانات XML. كما تعلم ، يقوم محلل DOM بتحميل الملف بأكمله في الذاكرة ، وبناء تمثيل الكائن الخاص به ويجعل من الممكن تجاوز محتوياته بطريقة عشوائية (بما في ذلك العودة بشكل متكرر إلى نفس العنصر). لا يضع المحلل اللغوي لـ SAX مطلقًا ملف البيانات بالكامل في الذاكرة ؛ بدلاً من ذلك ، يقوم بمعالجته كـ "دفق" من العناصر ، مما يمنعه من العودة إلى العنصر مرة أخرى.

يمكن أن يكون استخدام وضع SAX في Xylophone (عبر -sax سطر الأوامر -sax أو تعيين معلمة useSax لطريقة useSax على useSax ) مفيدًا للغاية عندما تحتاج إلى إنشاء ملفات كبيرة جدًا. نظرًا لسرعة وربحية موارد محلل SAX ، تزداد سرعة إنشاء الملفات عدة مرات. يتم تقديم هذا على حساب بعض القيود الصغيرة على الواصف (الموضحة في الوثائق) ، ولكن في معظم الحالات تستوفي التقارير هذه القيود ، لذلك أوصي باستخدام وضع SAX حيثما أمكن.

آمل أن تكون قد أعجبت بطريقة التحميل إلى Excel عبر Xylophone وستوفر الكثير من الوقت والأعصاب - لأنك أنقذتنا.

وأخيرًا ، يربط مرة أخرى:

Source: https://habr.com/ru/post/ar422059/

More articles:


All Articles