ToExcel() Extension Method

I recently had a migraine inducing problem where I needed to save an object to an Excel file which, at first glance I thought would be something easy and simple to do... However Nu-Get's Excel libraries are not as abundant as one would think. After doing some research I finally found a library worth its salt, presenting ExtremeML.

ExtremeML is a comprehensive and fully-supported library that builds upon the Microsoft OpenXML SDK, offering a simple and intuitive way to generate Excel workbooks from desktop or web-based projects developed in any .Net language. Say goodbye to complex Excel-interop techniques and harness the power of a 100% managed-code solution to dynamically create Excel content like never before.

Still, implementing this with the amount of documentation available was not easy, I finally found a dodgy hard coded way to get some results.

     public byte[] GetExcelReport(Model batch, string templatePath)

        {

            var outputStream = new MemoryStream();

            using (var inputStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read))

            using (var package = SpreadsheetDocumentWrapper.Open(inputStream, outputStream))

            {

                var mappings = new[] { "Parent", "Code", "Target %"};

                var table = package.WorkbookPart.GetTablePart("Model").Table;

                var data = (from item in batch

                            select new object[]

                              {

                    item.Parent,

                    item.Code,

                    item.TargetPercentage.ToString("P3"),

               }).ToArray();

                table.Fill(mappings, data);

            }

            return outputStream.ToArray();

        }

This is fine and it works somewhat, but here's the problem, what happens if your fields change? This solution is not feasible as it contains hard coded fields and can thus be used for this situation only.

This gave me the idea to create an Extension Method to be used for any model, template and table. So let's do this...

I started out by asking my good friend Fanie Reynders for a possible Extension Method to create a DataTable from any object and he graciously provided the following:

      

            public static System.Data.DataTable ToDataTable<T>(this IEnumerable<T> items)

            {

                var dataTable = new System.Data.DataTable(typeof(T).Name);

       

                var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

                foreach (var prop in props)

                {

             

                    dataTable.Columns.Add(prop.Name, prop.PropertyType);

                }

                foreach (T item in items)

                {

                    var values = new object[props.Length];

                    for (int i = 0; i < props.Length; i++)

                    {

                      

                        values[i] = props[i].GetValue(item, null);

                    }

                    dataTable.Rows.Add(values);

                }

      

                return dataTable;

            }

            public static System.Data.DataTable ToDataTable<T>(this T item)

            {

                var items = new[] { item };

                return items.AsEnumerable().ToDataTable();

            }

        

This is quite useful as one of the overloads for filling the table in ExtremeML uses a DataTable. After this I created my ToExcel Extension Methods:

public static byte[] ToExcel<T>(this IEnumerable<T> model, string templatePath, string tableName)

        {

            var dt = ToDataTable(model);

            var outputStream = new MemoryStream();

            tableName = string.IsNullOrEmpty(tableName) ? dt.TableName : tableName;

            using (var inputStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read))

            using (var package = SpreadsheetDocumentWrapper.Open(inputStream, outputStream))

            {

              package.WorkbookPart.GetTablePart(tableName).Table.Fill(dt);

            }

            return outputStream.ToArray();

        }

  public static byte[] ToExcel<T>(this IEnumerable<T> model, string templatePath)

       {

           return model.ToExcel(templatePath, null);

       }

The Extra overload can be used when your Model's name matches your Excel tables' name.

Finally call your Extension Method:

    class Program

    {

        static void Main(string[] args)

        {

            var people = new People();

            

            var templatePath = @"C:\Template.xlsx";

            var bytes = people.Group.ToExcel(templatePath,"People");

            File.WriteAllBytes(@"C:\OutputFile.xlsx", bytes);

            Console.ReadKey();

        }

    }

   

And there you go...A fully functional Model.ToExcel() Extension Method, any ideas on how I could further better this solution or optimize it would be much appreciated. Please be gentle as I am still a Junior :-)

Cheers...

Solution