EPPlus Excel Export - All data values going into Column A for each record?
I am attempting to add Excel Export functionality to my MVC5/Code-First Entity Framework application via the EPPlus library and System.Linq.Dynamic. On my Export View I have filled a MultiSelectList with each of my INV_Assets model properties. These then get passed into my ExportController to designate which fields of the model are to be exported.
I've gotten the Headers to export into Row 1, but I'm still having difficulty getting the data to export. Currently the data for my selected fields all comes across, but each value is exported as a long string into it's own row in Column A. For example, if I select the following fields (Status, ip_address, mac_address, note, owner, cost, po_number, and description) I get the following:
Row1: [Status][ip_address][mac_address][note][owner][cost][po_number][description]
Row2: [{Status=SIGNEDOUT, ip_address=10.10.121.25, mac_address=10.10.134.11, note=, owner=John Smith, cost=35.00, po_number=G348, description=This is a description of the item.}][][][][][][][]
Does anyone know how to break this up to where the values actually go into the correct column cells instead of as one long giant string?
Code below:
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)
{
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);
}
IQueryable selectStatement = DynamicSelectionColumns(exportFields);
// 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();
}
// Insert Data -- Currently all one long string in Column A per record
if (selectStatement.Count() > 0)
{
ws.Cells["A2"].LoadFromCollection(selectStatement.Cast<object>(), true);
}
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("_", ""));
string select = "new ( " + string.Join(", ", fieldsForExport) + ")";
return db.INV_Assets.ToList().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,
//Module = t.Module != null ? t.Module.Name : "",
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);
}
}
}
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 decimal cost { get; set; }
public string po_number { get; set; }
public string description { get; set; }
public int invoice_number { get; set; }
public string serial_number { get; set; }
public string asset_tag_number { get; set; }
public DateTime? acquired_date { get; set; }
public DateTime? disposed_date { get; set; }
public DateTime? verified_date { get; set; }
public DateTime created_date { get; set; }
public string created_by { get; set; }
public DateTime? 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
}
}
EDIT:
Thanks to João Silva's suggestion, the fields now come across into their own individual cells within proper selected columns:
Code:
[HttpPost]
public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
{
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);
}
{
ws.Cells[1, i + 1].Value = exportFields[i].ToString();
}
var membersToShow = typeof(INV_Assets).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();
ws.Cells["A2"].LoadFromCollection(_db.INV_Assets.ToList(), false, TableStyles.None, BindingFlags.Default, membersToShow);
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);
}
Output:
The only issue I still have is the ForeignKey fields within my model. For example, [Status] is defined in my INV_Assets Model as:
[Required]
public int Status_Id { get; set; }
[ForeignKey("Status_Id")]
public virtual INV_Statuses Status { get; set; }
When the current export executes though, instead of getting say AVAILABLE/RECYCLED, the [Status] column in excel contains InventoryTracker.Models.INV_Statuses in every cell for each record exported.
Can anyone offer insight into how to get not only the direct INV_Assets fields exported, but the ForeignKey values for Model, Location, Status, Vendor, Manufacturer, and Type also into Excel?
Added a ToString() override on each of my desired Models to ensure the value came through instead of just the object type:
public class INV_Models
{
public override string ToString()
{
return this.model_description;
}
}
public class INV_Manufacturers
{
public override string ToString()
{
return this.manufacturer_description;
}
}
public class INV_Locations
{
public override string ToString()
{
return this.location_dept + "|" + this.location_room;
}
}
public class INV_Vendors
{
public override string ToString()
{
return this.vendor_name;
}
}
public class INV_Types
{
public override string ToString()
{
return this.type_description;
}
}
public class INV_Statuses
{
public override string ToString()
{
return this.status_description;
}
}
ExportController:
[HttpPost]
public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
{
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);
}
// 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();
}
var membersToShow = typeof(INV_Assets).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();
ws.Cells["A2"].LoadFromCollection(_db.INV_Assets.ToList(), false, TableStyles.None, BindingFlags.Default, membersToShow);
var membersToShow = typeof(INV_Assets).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();
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);
}
And the output Excel file:
