As an overview I am attempting to add
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 <a href="https://epplus.codeplex.com/" rel="nofollow">EPPlus</a> library to Export. I am attempting to implement Dynamic LINQ functionality in my MVC5/EF Code-First application based on <a href="https://stackoverflow.com/questions...ect-particular-column-based-on-check-box-list">THIS</a> example, but not having much luck so far.
</pre>
My code is exporting my selected columns values from my
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
in
no matter how many fields I specify for output.
Can anyone with more experience or who has used
weigh in on this?
Code:
Export()
Code:
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
Code:
MultiSelectList
Code:
0
Code:
A2:A180Can anyone with more experience or who has used
Code:
System.Linq.Dynamic