حل مشكلة توليد ملفات CSV بالعربي برمجياً وفتحها بالأكسل Excel

مشكلة ملف ال CSV العربي مع ال Excel

كثير من الأحيان نحتاج لتوليد ملفات اكسل شيت Excel برمجياً من داخل المشاريع مثلاً لتصدير بعض التقارير، وبدلاً من أن نقوم باستخدام مكتبات خاصة لكي تولد ملفات Excel فنقوم بعمل ملفات CSV والتي يمكن فتحها بسهولة بواسطة الأكسل، أو حتى يمكن قرائتها بواسطة البرمجيات الأخرى.

في هذه المقالة سوف نطبق المثال بلغة سي#، ولكن نفس المفهوم يمكن تطبيقها بأي لغة أخرى مثل جافا او PHP أو بايثون او غيرها من اللغات.

سوف نرى البيانات التي نريد كتابتها على الأكسل شيت وهي Hardcoded حتى نركز على المشكلة والحل:

الآن عادة ما يقوم المبرمج بفتح ملف أو Stream وكتابة هذه البيانات بترميز UTF-8 ووضع علامة الفاصلة , حتى يتم اعتباره Comma Seperate Value، كما هنا:

لاحظ في المثال الأعلى:

• قام المطور في أول 3 أسطر بتجهيز نوع المعامل وهو الفاصلة
• قام بتحديد الترميز في السطر الذي يليه
• في السطر الاخير قمنا بتحويل النصوص الى مصفوفة بايت encoding string حتى نقوم بكتابة البيانات في ملف بشكل binary

الان عند تشغيل المثال في جهاز ويندوز باللغة الانجليزية (المنطقة regional)، وفتح الملف الناتج عن طريق الاكسل شيت، وعن طريق برنامج NotePad++ سوف تجد المخرج التالي:

الملاحظات:

• تم كتابة البيانات بشكل سليم (وذلك لأننا نستخدم ترميز unicode وهو UTF8)
• النوت باد++ قام بقراءة الملف صحيحاً وحدد الترميز السليم
• الاكسل شيت قام بالفصل كل بيانات في عمود بشكل سليم، ولكن الترميز لم يخرج بشكل صحيح.

كما تحدثنا في الموضوع (كل ما يحتاجه المطور معرفته عن الترميز) ذكرنا أن الاكسل شيت حتى يتعرف على الملف النصي بأي من ال unicode encoding يجب أن يتم وضع علامة في الملف تعرف بال BOM ، لذلك لحل المشكلة سوف نحتاج أن نعدل من القائمة في برنامج notepad++ ونقوم بوضع الترميز على UTF-8 BOM وقم بحفظ الملف واعادة فتحه بالاكسل وستجد المشكلة تم حلها.

وضع علامة BOM برمجياً في ملف CSV

الحل اليدوي جيد، لكن نحن نريد حلها برمجياً حتى لا تتطلب أي تدخل من المستخدم، وهكذا سوف نقوم باضافة ال BOM الكود السابق وهي عبارة عن مصفوفة من البايت، وسوف نقوم اولاً بعمل دالة لدمج مصفوفتين البايت التي تخص العلامة BOM مع التي تخص البيانات حتى نكتبها مرة واحدة على الملف:

الأن سوف يكون المثال كالتالي:

ملاحظة: يمكن أن نستخدم الل bytes مباشرة ولكن سوف نستخدم الدالة الجاهزة GetPreamble وهي ستولد ال BOM المناسب.

شاهد النتيجة الآن:

كل شيء الآن على ما يرام وأصبح البرنامج يعمل ويستطيع من يستخدم البرنامج اصدار التقرير وفتحه بالاكسل وحفظه مجدداً اذا اراد أن يحوله ل .xslx

لكن هناك مشكلة في حال أردنا تشغيل نفس الملف على نظام ويندوز عربي، نشاهد الصورة التالية عند فتح الملف في نظام مثل هذا، حيث كل البيانات تظهر في العمود الأول من الملف والأعمدة الثانية فارغة:

المشكلة تكمن في أن النظام ويندوز باللغة العربية معامل الفصل هو ليس الفاصلة الانجليزية Comma وانما تكون ; تستطيع مشاهدة المعامل الافتراضي الذي يقرأه الاكسل من النظام لكي يستخدمه كمعامل للفصل من اعدادت اللغة في الجهاز:

بهذه الطريقة العملاء الذي لديهم ويندوز عربي لن يستطيعوا فتح الملف csv الناتح من موقعك بالاسكل شيت، ويتوجب اما معالجة الملف اولاً بفتحه ب notepad++ واستبدال المعامل بالمعامل الافتراضي في هذا النظام، او تغيير المعامل “فاصلة القائمة” في اعدادات اللغة الى فاصلة وبالتالي سوف يعمل بلا مشاكل.

لكن ماذا في حالة لا نريد المستخدم التغيير في اعدادت النظام، ونريد ملف ال csv أن يعمل على النظام العربي والانجليزي بدون أي تغييرات؟

حل المشكلة بشكل نهائي

الحل المستخدم هو استخدام ال Tab مع ترميز UTF 16 LE ، وسوف نقوم الان بالتغييرات على الكود ونرى المخرج:

public static void Run()
{
string sep = "\t";
Encoding unicode = Encoding.Unicode;
byte[] BOM = unicode.GetPreamble(); 

// Preprate Data
StringBuilder sb = new StringBuilder();
var books = GetBooks();
foreach(var book in books)
{
string line = String.Format("{0}{3}{1}{3}{2}", 
book.Name, book.Author, book.Price, sep);
sb.AppendLine(line);
}

// Write to file
string path = @$"C:\Users\WajdyEssam\Desktop\test";
string fileName = $@"{path}\Report_{Guid.NewGuid().ToString()}.csv";
byte[] encoededData = unicode.GetBytes(sb.ToString());
byte[] combinedBytes = Combined(BOM, encoededData);
File.WriteAllBytes(fileName, combinedBytes); 
}

لاحظ الحل في أول سطرين في الكود أعلاه، قمنا بتديل معامل الفصل الى \t وقمنا بتحويل الترميز الى UTF-16 LE

نشاهد المخرج على النظام الانجليزي:

وعلى النظام العربي:

سوف تكون الاعمدة والبيانات بشكل سليم تماماً.

أخيراً

هناك حلول اخرى موجودة مثلاً كتابة sep=, في أول الملف، لكن بعد التجربة هذه الطريقة سوف تجعل الاكسل شيت يتجاهل ال BOM في الملف، فعلاً لا أدري السبب لماذا ترميز utf-8 به مشاكل، ولكن حالياً هذه الحل مناسب ويؤدي الغرض تماماً.

اذا كانت لديك أي طريقة أخرى يسعدنا مشاركتك لنا، واذا تواجدت اي مشاكل يسعدنا أن نساعدك في حلها.