ClosedXML برای کار با فایل های Excel

1 نظر

در این مقاله قصد دارم کتابخانه ای رو معرفی کنم که برای کار با فایل های Excel ارائه شده و بصورت متن باز در اختیار شما قرار داره.یکی از دوستانم در مورد کار با فایل اکسل از من کمک خواست و باعث شد من با این ابزار آشنا بشم. این کتابخانه تحت .net ارائه شده و کارکردی ساده داره. برای دریافت این ابزار،سورس کد و همچنین نمونه کدهای مربوط به شیوه استفاده از اون در سایت codeplex.com به این آدرس مراجعه کنید.

ClosedXML

صفحه گسترها یا Spreadsheet، عموما بعنوان فرمتی برای انتقال اطلاعات در ساختاری جدول مانند، مورد استفاده قرار می گیرند.امکاناتی مانند استفاده از فرمول برای محاسبه مقادیر یک سلول بر اساس سلول های دیگه هم در این نوع برنامه ها وجود داره.برای اطلاعات بیشتر درمورد این نوع برنامه ها و فرمت به این آدرس مراجعه کنید.برنامه های آماری، شبیه سازها و بسیاری از نرم افزارهای مهندسی از اون پشتیبانی میکنند و حتی دارای قابلیتی برای ذخیره اطلاعات خروجی در این فرمت نیز هستند. از این رو ممکنه در برنامه نویسی و پروژهای کاری نیاز پیدا بشه که با انواع این نوع فرمت ها نیاز به تعامل داشته باشید.

برای ما که تو ایران زندگی میکنیم و بدون پرداخت هزینه به نرم افزارهای گرون قیمت دسترسی داریم، برنامه Office چیز ناآشنایی نیست. به همین دالیل یکی از معروفترین صفحه گسترها در بین ما Excel هستش و به دلیل قدرت بالا و کاربرپسند بودن اون، اکثر برنامه هایی که ورودی و خروجی مربوط به صفحه گسترها رو پشتیبانی می کنن از فرمت های ارائه شده توسط اون پشتیبانی می کنن.

در این مقاله قصد دارم برخی از ویژگی های ساده ای ClosedXML رو که میتونه در یک برنامه ساده مورد استفاده قرار بگیره رو با استفاده از کد توضیح بدم. درصورت نیاز و درخواست دوستان مفاهیم پیشرفته تر رو در نگارش های بعدی مقاله مورد برسی قرار خواهم داد.قبل از هر چیز به این نکته توجه داشته باشید که برای کار با این کتابخانه نیاز دارید کتابخانه DocumentFormat.OpenXml.dll رو هم به پروژه خودتون اضافه کنید.

ادعای ارائه شده در مورد این کتابخانه که به نظر من حقیقت داره اینه که به کمک ClosedXML میتونید به راحتی به تولید و دستکاری اسناد نرم افزار Excel نگارش های 2007 و 2010 بپردازید. حالا اجازه بدید چند تا مثال ساده از شیوه کار با این ابزار رو باهم مرور کنیم.


1:  var workbook = new XLWorkbook();

2:  var worksheet = workbook.Worksheets.Add("Sample Sheet");

3:  worksheet.Cell("A1").Value = "Hello World!";

4:  workbook.SaveAs("HelloWorld.xlsx");

این یک مثال کوتاه و ساده است.در خط اول یک ابجکت از نوع XLWorkbook ایجاد شده.این ابجکت به یک سند Excel اشاره دارد.همونطور که میدونید یک فایل اکسل میتونه حاوی تعدادی ورک شیت یا Worksheet باشه.در خط دوم به خصوصیت Worksheets از شیعی ایجاد شده که به مجموعه شیت های موجود در سند اشاره داره(و از نوع IXLWorksheets است که اون هم از IEnumerable<IXLWorksheet> ارث بری کرده)، توسط متد Add() یک شیت با نام Sample Sheet اضافه میشه.مقدارد برگشتی به شیت اظافه شده اشاره داره. حالا میتونیم از متغیر worksheet برای کار با سلول های موجود در شیت استفاده کنیم و این همون کاری هستش که در خط 3 انجام شده.از متد Cell() و پارامترهای اون برای دسترسی به یک سلول مشخص “A1” که همون سطر اول و ستون اول است استفاده میشه. امضای دیگه این متد Cell(int row, int column) هستش که مستقیما شماره سطر و ستون رو دریافت و سلول مورد نظر رو برمی گردونه. بعد از دسترسی به سلول مورد نظر با استفاده از خصوصیت Value میتونیم مقدار اون ستون رو تغیر بدیم و یا حتی بخونیم.در نهایت در خط چهارم با مشخص کردن نام و مسیر فایل بعنوان پارامتر برای متد Save() سند موجود در حافظه را بر روی دیسک سخت ذخیره میکنیم.حالا میتونید فایل رو با اکسل باز کنید و ببینیدی که همه چیز به درستی انجام شده.

 

ClosedXML


 در مثال قبلی یک فایل اکسل را با استفاده از کدهای C# ایجاد و ذخیره کردیم.در این مثال فایل ایجاد شده در مثال قبلی را بازکرده و مقدار موجود در سلول A1 از شیت با نام Sample Sheet را با استفاده از MessageBox نمایش میدهیم.درخط اول با مشخص کردن مسیر فایل برای سازنده کلاس، اون فایل رو باز میکنیم.عملیات خوندن از شیت و سلول مورد نظر در خط سوم انجام شده:

1: var workbook = new XLWorkbook("HelloWorld.xlsx");

2: var worksheet = workbook.Worksheets.Worksheet("Sample Sheet");

3: MessageBox.Show(worksheet.Cell("A1").Value.ToString());

 


این مثال کمی طولانی تر ولی کاربردی تر است. برای اجرای این کد نیاز به یک کلاس با نام Person و یک متد کمکی با نام GetTable() جهت ایجاد یک DataTable و پرکردن آن توسط داده های کمکی داریم که از خط 38 به بعد ارائه شده اند. کلیه عملیات در متد  Create() انجام می شود.خروجی برنامه چیزی شبیه به شکل زیر خواهد بود.

 

ClosedXML

 

پس اجازه بدهید تا به برسی کد بپردازیم:

01: public static void Create()

02:        {

03:            var wb = new XLWorkbook();

04:            var ws = wb.Worksheets.Add("Collections");

 

            // From a list of strings

05:            var listOfStrings = new List<String>();

06:            listOfStrings.Add("House");

07:            listOfStrings.Add("Car");

08:            ws.Cell(1, 1).Value = "Strings";

09:            ws.Cell(1, 1).AsRange().AddToNamed("Titles");

10:            ws.Cell(2, 1).Value = listOfStrings;

 

            // From a list of arrays

11:            var listOfArr = new List<Int32[]>();

12:            listOfArr.Add(new Int32[] { 1, 2, 3 });

13:            listOfArr.Add(new Int32[] { 1 });

14:            listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 });

15:            ws.Cell(1, 3).Value = "Arrays";

16:            ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles");

17:            ws.Cell(2, 3).Value = listOfArr;

 

            // From a DataTable

18:            var dataTable = GetTable();

19:            ws.Cell(6, 1).Value = "DataTable";

20:            ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles");

21:            ws.Cell(7, 1).Value = dataTable.AsEnumerable();

 

            // From a query

22:            var list = new List<Person>();

23:            list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." });

24:            list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." });

25:            list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." });

26:            list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." });

 

27:            var people = from p in list

                         where p.Age >= 21

                         select new { p.Name, p.House, p.Age };

 

28:            ws.Cell(6, 6).Value = "Query";

29:            ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles");

30:            ws.Cell(7, 6).Value = people.AsEnumerable();    // Very Important to call the AsEnumerable method

            // otherwise it won't be copied.

 

 

            // Prepare the style for the titles

31:            var titlesStyle = wb.Style;

32:            titlesStyle.Font.Bold = true;

33:            titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

34:            titlesStyle.Fill.BackgroundColor = XLColor.Cyan;

 

            // Format all titles in one shot

35:            wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle;

 

36:            ws.Columns().AdjustToContents();

 

37:            wb.SaveAs("Collections.xlsx");

        }

 

38:        class Person

        {

            public String House { get; set; }

            public String Name { get; set; }

            public Int32 Age { get; set; }

        }

 

        private static DataTable GetTable()

        {

 

            DataTable table = new DataTable();

            table.Columns.Add("Dosage", typeof(int));

            table.Columns.Add("Drug", typeof(string));

            table.Columns.Add("Patient", typeof(string));

            table.Columns.Add("Date", typeof(DateTime));

 

            table.Rows.Add(25, "Indocin", "David", DateTime.Now);

            table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);

            table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);

            table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);

            table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

            return table;

        }

 

درخط 3  و 4  به ترتیب یک سند اکسل و یک شیت ایجاد می شود.در خط 5 تا 10 یک نمونه از استفاده از لیستی از رشته ها برای پرکردن سلول ها را مشاهده می نمایید.

در خط 5 تا 7 یک لیست از رشته ایجاد و مقداردهی میشود.در خط 8 مقدار سلول A1 برابر Strings قرار می گیرد.از انجا که قرار است این سلول نقش ستون جدول را بازی کند در خط 9 یک تگ برای آن تنظیم می شود.بعدا در خط 35 با اعمال استایل به این عنوان تگ،استایل بر روی این سلول نیز اعمال خواهد شد.در خط 10 با انتصاب متغیر listOfStrings که لیست از رشته ها است به سلول A2 اتفاق جالبی خواهد افتاد.هر مقدار از این لیست به ترتیب در یک ردیف قرار میگیرد.یعنی در نهایت A2 برابر House  و مقدار سلول A3 برابر Car تنظیم خواهد شد(همانند شکل).

خط 11 تا 17 نمونه کد مربوط به استفاده از ارایه برای مقداردهی سلول ها را ارائه میدهد. در ابتدا یک Jagged Arrays ایجاد میشود.این نوع ارایه ها دارای سطر هایی هستند که میتوانند شامل ستونهایی نا برابر باشند.بعنوان مثال سطر اول حاوی 3 عنصر می باشد، درحالی که سطر دوم آرایه تنها یک عنصر و سطر سوم دارای 6 عنصر است.

در خط 13 مقدار سلول برابر Arrays تنظیم میشود که نشان دهنده تیتر جدول خواهد بود. در خط 16،متد Merge() همانطور که از نامش مشخص است سلول های (1,3) تا (1,8) را با هم ادغام می کند.برای اعمال استایل تیتر در ادامه کد، یک تگ به این مجموعه ادغام شده انتصاب داده میشود.

مقادیر آرایه ایجاد شده در خط 17 به سلول (2,3) انصاب داده میشود که همانند مثال قبل در قالب سلول های سطری و ستونی به فایل افزوده خواهند شد.(به شکل مراجعه نمایید)

در طی خطوط 18 تا 21، شما با مثال اضافه کردن اطلاعات موجود در یک شی DataTable به فایل اکسل آشنا میشوید.این مثال میتواند کاربرد بیشتری در عملیات انتقال اطلاعات بازی کند. ابتدا در خط 18 با استفاده از متد کمکی GetTable() که در انتهای کد تعریف شده است، یکسری اطلاعات تستی درقالب یک DataTable ایجاد میشود. خط 18 تا 20 همانند دو مثال قبل تیتر جدول را مشخص و تنظیم می نمایید. خط 21 عملیات اصلی را انجام میدهد و همانند دو نمونه قبلی اطلاعات موجود در جدول را که بصورت سطر و ستون میباشد در قالب مجموعه ای از سلول ها به فایل اکسل اضافه مینماید. در این حالت هر سطر بصورت یک ردیف جدید و هر ستون در آن درقالب یک سلول در ان ردیف درخواهد آمد.

نکته ای که در اینجا مهم است، آن است که میبایست با فراخوانی متد AsEnumerable() شی DataTable را در فرمتی مناسب برای خصوصیت Value ارسال کنید تا عملیات به درستی انجام شود.

خطوط 22 تا 30 عملیاتی مشابه مثال های قبلی ولی اینبار با استفاده از عبارات Linq را ارائه میدهد. در این مثال از کلاس Person که در ادامه کد تعریف شده است استفاده شده است. روال کار ساده است،ابتدا لیستی اولیه حاوی تعدادی رکورد از نوع Person ایجاد می شود. سپس با استفاده از عبارات Linq یک پرس و جو برای بازیابی رکوردهایی با شرط سن بالای 25 سال ایجاد شده است. نتیجه این پرس و جو در خط 30 با فراخوانی متد AsEnumerable() برای انتصاب به فایل اکسل استفاده شده است.

در خطوط 31 الی 35 استایل Titles که قرار بود برای مشخص کردن هدر سایت مورد استفاده قرار گیرد تولید و تنظیم می شود. موادری مانند رنگ، قلم و تراز سازی انجام میشود.در نهایت خط 37 فایل را با عنوان Collections.xlsx ذخیره می نماید.

این ابزار امکان استفاده از فرمول ها را نیز داراست که در حوصله این مقاله نیست.امیدوارم این توضیحات مفید واقع شده باشن.نظرات یادتون نرهcool


سید علیرضا قمصری

سلام. من یک برنامه نویس آشنا به C#.NET3.5,MVC.NET 3 ,ASP.NET3.5,SqlServer 2008,Reporting هستم، ساکن تهران که از سال 1385 به برنامه نویسی مشغول بوده و خوشبختانه در محیط وب و ویندوز دارای تجربیات عملیاتی شده متعددی نیز می باشم. خوشحال خواهم شد که در سایت خودم به آدرس qamsari.com میزبان شما باشم.

برای ثبت نظر می بایست ابتدا وارد شوید در صورتی که عضو نیستید یک حساب جدید ایجاد نمایید.