Using Dynamic Linq and EPPlus to Export to Excel?

Question

As an overview I am attempting to add Export() functionality to my application -- allowing the user to specify certain model fields and only export the values in those fields by querying with LINQ and using the EPPlus library to Export. I am attempting to implement Dynamic LINQ functionality in my MVC5/EF Code-First application based on THIS example, but not having much luck so far.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using InventoryTracker.DAL;
using OfficeOpenXml;
using InventoryTracker.Models;
using System.Linq.Dynamic;


namespace InventoryTracker.Controllers
{
    public class ExportController : Controller
    {
        InventoryTrackerContext _db = new InventoryTrackerContext();
        public static List<DynamicColumns> DynamicColumnsCollection = new List<DynamicColumns>();

        [HttpPost]
        public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
        {
            //FileInfo newExcelFile = new FileInfo(output);
            ExcelPackage package = new ExcelPackage();
            var ws = package.Workbook.Worksheets.Add("TestExport");  

            var exportFields = new List<string>();
            foreach(var selectedField in model.SelectedFields)
            {
                // Adds selected fields to [exportFields] List<string>
                exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
            }

            //int cnt = 0;
            //foreach(var column in exportFields)
            for (int cnt = 0; cnt < 10; cnt++ )
            {
                DynamicColumnsCollection.Add(new DynamicColumns()
                {
                    Id = cnt,

                    ip_address = "ip_address" + cnt,
                    mac_address = "mac_address" + cnt,
                    note = "note" + cnt,
                    owner = "owner" + cnt,
                    cost = "cost" + cnt,
                    po_number = "po_number" + cnt,
                    description = "description" + cnt,
                    invoice_number = "invoice_number" + cnt,
                    serial_number = "serial_number" + cnt,
                    asset_tag_number = "asset_tag_number" + cnt,
                    acquired_date = "acquired_date" + cnt,
                    disposed_date = "disposed_date" + cnt,
                    verified_date = "verified_date" + cnt,
                    created_date = "created_date" + cnt,
                    created_by = "created_by" + cnt,
                    modified_date = "modified_date" + cnt,
                    modified_by = "modified_by" + cnt
                });
            }

            //var selectStatement = DynamicSelectionColumns(exportFields);
            IQueryable collection = DynamicSelectionColumns(new List<string>() {
                "id",
                "owner",
                "note"
            });

            // Loops to insert column headings into Row 1 of Excel
            for (int i = 0; i < exportFields.Count(); i++ )
            {
                ws.Cells[1, i + 1].Value = exportFields[i].ToString();
            }

            // Process data from [collectin] into Excel???
            ws.Cells["A2"].LoadFromCollection(collection.ToString());

            //    ws.Cells["A2"].LoadFromCollection(selectStatement.ToString());

            var memoryStream = new MemoryStream();
            package.SaveAs(memoryStream);

            string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
            string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            memoryStream.Position = 0;
            return File(memoryStream, contentType, fileName);
        }

        public IQueryable DynamicSelectionColumns(List<string> fieldsForExport)
        {
            using (var db = new InventoryTrackerContext())
            {
                string fieldIds = "," + "4,5,3,2,6,17,11,12" + ",";

                //var taskColum = Enum.GetValues(typeof(EnumTasks)).Cast<EnumTasks>().Where(e => fieldIds.Contains("," + ((int)e).ToString() + ",")).Select(e => e.ToString().Replace("_", ""));
                var taskColum = Enum.GetValues(typeof(EnumTasks)).Cast<EnumTasks>().Where(e => fieldIds.Contains("," + ((int)e).ToString() + ",")).Select(e => e.ToString());

                ////string select = "new (  TaskId, " + (taskColum.Count() > 0 ? string.Join(", ", taskColum) + ", " : "") + "Id )";
                //string select = "new (  " + string.Join(", ", fieldsForExport) + ")";

                ////return db.INV_Assets.ToList().Select(t => new DynamicColumns() { Id = t.Id, TaskId = Project != null ? Project.Alias + "-" + t.Id : t.Id.ToString(), 

                if (!fieldsForExport.Any())
                {
                    return null;
                }

                string select = string.Format("new ( {0} )", string.Join(", ", fieldsForExport.ToArray()));

                var collection = DynamicColumnsCollection.Select(t => new DynamicColumns()
                    {
                        Id = t.Id,
                        //Manufacturer = Convert.ToString(t.Manufacturer.manufacturer_description),
                        //Type = t.Type.type_description,
                        //Location = t.Location.location_room,
                        //Vendor = t.Vendor.vendor_name,
                        //Status = t.Status.status_description,
                        ip_address = t.ip_address,
                        mac_address = t.mac_address,
                        note = t.note,
                        owner = t.owner,
                        cost = t.cost,
                        po_number = t.po_number,
                        description = t.description,
                        invoice_number = t.invoice_number,
                        serial_number = t.serial_number,
                        asset_tag_number = t.asset_tag_number,
                        acquired_date = t.acquired_date,
                        disposed_date = t.disposed_date,
                        verified_date = t.verified_date,
                        created_date = t.created_date,
                        created_by = t.created_by,
                        modified_date = t.modified_date,
                        modified_by = t.modified_by
                    }).ToList().AsQueryable().Select(select);

                return collection;
            }
        }

    }

    public class DynamicColumns : INV_Assets
    {
        public string Model { get; set; }
        public string Manufacturer { get; set; }
        public string Type { get; set; }
        public string Location { get; set; }
        public string Vendor { get; set; }
        public string Status { get; set; }
        public string ip_address { get; set; }
        public string mac_address { get; set; }
        public string note { get; set; }
        public string owner { get; set; }
        public string cost { get; set; }
        public string po_number { get; set; }
        public string description { get; set; }
        public string invoice_number { get; set; }
        public string serial_number { get; set; }
        public string asset_tag_number { get; set; }
        public string acquired_date { get; set; }
        public string disposed_date { get; set; }
        public string verified_date { get; set; }
        public string created_date { get; set; }
        public string created_by { get; set; }
        public string modified_date { get; set; }
        public string modified_by { get; set; }
    }

    public enum EnumTasks
    {
        Model = 1,
        Manufacturer = 2,
        Type = 3,
        Location = 4,
        Vendor = 5,
        Status = 6,
        ip_address = 7,
        mac_address = 8,
        note = 9,
        owner = 10,
        cost = 11,
        po_number = 12,
        description = 13,
        invoice_number = 14,
        serial_number = 15,
        asset_tag_number = 16,
        acquired_date = 17,
        disposed_date = 18,
        verified_date = 19,
        created_date = 20,
        created_by = 21,
        modified_date = 22,
        modified_by = 23
    }





    //https://stackoverflow.com/questions/5796151/export-model-data-to-excel-mvc
    //https://landokal.wordpress.com/2011/04/28/asp-net-mvc-export-to-excel-trick/


}

My code is exporting my selected columns values from my MultiSelectList on my View into Row 1 of the Excel spreadsheet, but I have something amiss with my dynamic linq querying as the data that gets output is simply the value 0 in A2:A180 no matter how many fields I specify for output.

Can anyone with more experience or who has used System.Linq.Dynamic weigh in on this?

Answer 1

Here is something that I am using for some time:

public class ExcelExportHelper
    {
        public static string ExcelContentType { get { return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; } }

        public static DataTable ToDataTable<T>(List<T> data)
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();

            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                //table.Columns.Add(prop.Name, prop.PropertyType);
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); // to avoid nullable types
            }

            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }

                table.Rows.Add(values);
            }
            return table;
        }

        public static byte[] ExportExcel(DataTable dt, string Heading = "", params string[] IgnoredColumns)
        {
            byte[] result = null;
            using (ExcelPackage pck = new ExcelPackage())
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Exported Data");
                int StartFromRow = String.IsNullOrEmpty(Heading) ? 1: 3;

                // add the content into the Excel file
                ws.Cells["A" + StartFromRow].LoadFromDataTable(dt, true);

                // autofit width of cells with small content
                int colindex = 1;
                foreach (DataColumn col in dt.Columns)
                {
                    ExcelRange columnCells = ws.Cells[ws.Dimension.Start.Row, colindex, ws.Dimension.End.Row, colindex];
                    int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
                    if (maxLength < 150)
                        ws.Column(colindex).AutoFit();

                    colindex++;
                }

                // format header - bold, yellow on black
                using (ExcelRange r = ws.Cells[StartFromRow, 1, StartFromRow, dt.Columns.Count])
                { 
                    r.Style.Font.Color.SetColor(System.Drawing.Color.Yellow);
                    r.Style.Font.Bold = true;
                    r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    r.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Black);
                }

                // format cells - add borders
                using (ExcelRange r = ws.Cells[StartFromRow + 1, 1, StartFromRow + dt.Rows.Count, dt.Columns.Count])
                {
                    r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

                    r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                }

                // removed ignored columns
                for (int i = dt.Columns.Count - 1; i >= 0; i--)
                {
                    if (IgnoredColumns.Contains(dt.Columns[i].ColumnName))
                    {
                        ws.DeleteColumn(i + 1);
                    }
                }

                // add header and an additional column (left) and row (top)
                if (!String.IsNullOrEmpty(Heading))
                {
                    ws.Cells["A1"].Value = Heading;
                    ws.Cells["A1"].Style.Font.Size = 20;

                    ws.InsertColumn(1, 1);
                    ws.InsertRow(1, 1);
                    ws.Column(1).Width = 5;
                }

                result = pck.GetAsByteArray();
            }

            return result;
        }

        public static byte[] ExportExcel<T>(List<T> data, string Heading = "", params string[] IgnoredColumns)
        {
            return ExportExcel(ToDataTable<T>(data), Heading, IgnoredColumns);
        }
    }

With these you can export a list of objects or a datatable to an Excel file. Additionally, you can specify a header and columns/properties to be ignored (like ID). Also, there are some formatting added and also small cells will auto fit in width.

Using this code you can use this in your controller action that is expecting a FileResult:

 byte[] filecontent = ExcelExportHelper.ExportExcel(...);
 return File(filecontent, ExcelExportHelper.ExcelContentType, "FileName.xlsx");

I hope this helps!