Pass all selected MultiSelectList values to MVC Controller for EPPlus Export to Excel?

Question

I'm attempting to add an Export() functionality to my MVC5 Code-First application using the EPPlus library. On my View I have a MultiSelectList with all the values for my main Model properties:

@Html.ListBox("PropertyList", typeof(InventoryTracker.Models.INV_Assets).GetProperties().Select(p => new SelectListItem { Text = p.Name, Value = p.Name, Selected = false }), new { @Id = "exportListBox" })

This renders the following HTML:

<select Id="exportListBox" id="PropertyList" multiple="multiple" name="PropertyList"><option value="Id">Id</option>
<option value="Model_Id">Model_Id</option>
<option value="Model">Model</option>
<option value="Manufacturer_Id">Manufacturer_Id</option>
<option value="Manufacturer">Manufacturer</option>
<option value="Type_Id">Type_Id</option>
<option value="Type">Type</option>
<option value="Location_Id">Location_Id</option>
<option value="Location">Location</option>
<option value="Vendor_Id">Vendor_Id</option>
<option value="Vendor">Vendor</option>
<option value="Status_Id">Status_Id</option>
<option value="Status">Status</option>
<option value="ip_address">ip_address</option>
<option value="mac_address">mac_address</option>
<option value="note">note</option>
<option value="owner">owner</option>
<option value="cost">cost</option>
<option value="po_number">po_number</option>
<option value="description">description</option>
<option value="invoice_number">invoice_number</option>
<option value="serial_number">serial_number</option>
<option value="asset_tag_number">asset_tag_number</option>
<option value="acquired_date">acquired_date</option>
<option value="disposed_date">disposed_date</option>
<option value="verified_date">verified_date</option>
<option value="created_date">created_date</option>
<option value="created_by">created_by</option>
<option value="modified_date">modified_date</option>
<option value="modified_by">modified_by</option>
</select>

This is the setup for my [Export] button (hyperlink):

@*<a href="/Export/ExportUsingEPPlus" class="btn btn-default btn-sm noDecoration exportBtn"><span class="glyphicon glyphicon-export"> Export - EPPlus</span></a>*@
<a href="#" class="btn btn-default btn-sm noDecoration exportBtn"><span class="glyphicon glyphicon-export"> Export - EPPlus</span></a>

What I can't figure out now is how to get all of the selected values in the MultiSelectList and pass them to my Controller to dictate what fields should be exported to Excel.

@section Scripts {

    <script type="text/javascript">
        $(document).ready(function () {

            $("a.exportBtn").on("click", function (e) {
                e.preventDefault();
                alert("Export button clicked!");
                exportSelectedAssets();
            });

            function exportSelectedAssets() {

            }
        });
    </script>
}

This is what I have so far in my Controller using the EPPlus library. Currently it simply creates an .xlsx with one value in the [A1] cell. Once I pass the values from MultiSelectList into this controller, I want to loop through each value of the selected fields in my Table and output them:

    public ActionResult ExportUsingEPPlus()
    {

        //FileInfo newExcelFile = new FileInfo(output);
        ExcelPackage package = new ExcelPackage();
        var ws = package.Workbook.Worksheets.Add("TestExport");
        ws.Cells["A1"].Value = "Sample Export 1";


        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);

    }

I was considering JSON for posting all selected values to my Controller, but I'm not sure if this is the best route for this situation? Can someone with more experience weigh in on this?

EDIT:

Attempting Dawood's suggestion, I created a ViewModel - ExportAssetsViewModel:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace InventoryTracker.Models
{
    public class ExportAssetsViewModel
    {
        public Dictionary<int, string> ListOfExportFields { get; set; }
        public int[] SelectedFields { get; set; }

        public ExportAssetsViewModel() {
            ListOfExportFields = new Dictionary<int, string>() {
                {1, "Model"},
                {2, "Manufacturer"},
                {3, "Type"},
                {4, "Location"},
                {5, "Vendor"},
                {6, "Status"},
                {7, "ip_address"},
                {8, "mac_address"},
                {9, "note"},
                {10, "owner"},
                {11, "cost"},
                {12, "po_number"},
                {13, "description"},
                {14, "invoice_number"},
                {15, "serial_number"},
                {16, "asset_tag_number"},
                {17, "acquired_date"},
                {18, "disposed_date"},
                {19, "verified_date"},
                {20, "created_date"},
                {21, "created_by"},
                {22, "modified_date"},
                {23, "modified_by"},
            };
        }
    }
}

I then placed my MultiSelectList on my ExportController - Index View within an HTML.BeginForm():

@using (Html.BeginForm())
{
    @Html.ListBox("PropertyList", typeof(InventoryTracker.Models.INV_Assets).GetProperties().Select(p => new SelectListItem { Text = p.Name, Value = p.Name, Selected = false }), new { @Id = "exportListBox" })
    <input type="submit" value="ExportUsingEPPlus" />
}

and on my ExportUsingEPPlus() Action modified it as demonstrated:

    [HttpPost]
    public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
    {
        var exportFields = new List<string>();
        foreach(var selectedField in model.SelectedFields)
        {
            exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
        }


        //FileInfo newExcelFile = new FileInfo(output);
        ExcelPackage package = new ExcelPackage();
        var ws = package.Workbook.Worksheets.Add("TestExport");
        ws.Cells["A1"].Value = "Sample Export 1";


        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);

    }

When I click my form input button however, my break point at the start of ExportUsingEPPlus() Controller Action is not being hit? All that occurs is the page appears to refresh and my selects get cleared out from the MultiSelectList...?

EDIT2:

Changed the @model on my Index View from InventoryTracker.Models.INV_Assets to InventoryTracker.Models.ExportAssetsViewModel, but m.SelectedFields and Model.ListOfExportFields in my ListBoxFor() are getting flagged for the model not containing a definiton for them?

@using GridMvc.Html
@using System.Collections.Generic
@using System.Web.Mvc
@using MvcCheckBoxList.Model
@model  InventoryTracker.Models.ExportAssetsViewModel

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Export</h2>

@using (Html.BeginForm("ExportUsingEPPlus", "Export", FormMethod.Post))
{
    @Html.ListBoxFor(m => m.SelectedFields, new MultiSelectList(Model.LisOfExportFields, "Key", "Value"), new { @class = "form-control", style = "height: 250px;" })
    <input type="submit" value="ExportUsingEPPlus" />
}

EDIT3:

I noticed when hovering over the m => m in my ListBoxFor() that my View seemed to think it was still using the model INV_Assets even though I had redefined my @model as InventoryTracker.Models.ExportAssetsViewModel. I retyped my @model definition and now m simply shows as (parameter) TModel m...?

Since I created the ExportAssetsViewModel after my previous attempts using the ExportController with the idea of passing selected values via JS/AJAX, I thought I would make a new controller based upon ExportAssetsViewModel. Attempting this though led to the following error:

ControllerCreationError

Now @Html.ListBoxFor(m => m.SelectedFields, new MultiSelectList(Model.ListOfExportFields, "Key", "Value"), new { @class = "form-control", style = "height: 250px;" }) flags as: "The type arguments for method 'System.Web.Mvc.Html.SelectExtensions.ListBoxFor<.....> cannot be inferred from the usage. Try specifying the type arguments explicitly."

Can anyone help with this?

EDIT4:

Disregard EDIT3. I wasn't passing ExportAssetsViewModel to the View. Fixed below:

Controller - Index Action:

    public ActionResult Index()
    {
        //var assetList = _db.INV_Assets.ToList();
        //return View(assetList);
        ExportAssetsViewModel expViewMod = new ExportAssetsViewModel();
        return View(expViewMod);
    }

Index View:

@using System.Collections.Generic
@using System.Web.Mvc
@model InventoryTracker.Models.ExportAssetsViewModel

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Export</h2>

<p>Please select which Asset fields to Export to Excel:</p>

@using (Html.BeginForm("ExportUsingEPPlus", "Export", FormMethod.Post))
{
    @Html.ListBoxFor(m => m.SelectedFields, new MultiSelectList(Model.ListOfExportFields, "Key", "Value"), new { @class = "form-control", style = "height: 250px;" })
    <input type="submit" value="ExportUsingEPPlus" />
}

Not locked into it, just an idea I was experimenting with. Originally I was simply calling my Controller Action directly (commented in shown code) to initiate the "Test" Export. — Analytic Lunatic, Mar 05 '15 at 15:36

Dawood Awan · Accepted Answer · 2015-03-05T16:25:04.007

3

If you are not using Ajax do this:

Create a ViewModel (With a List Of fields, and an array to store the selected fields):

public class ViewModel
{

    public Dictionary<int, string> LisOfFields { get; set; }
    public int[] SelectedFields { get; set; }


    public ViewModel()
    {
        LisOfFields = new Dictionary<int, string>()
        {
        {1, "Field1"},
        {2, "Field2"},
        {3, "Field3"},
        };

    }
}

Then Create a View where user can select fields

@using (Html.BeginForm())
{

    @Html.ListBoxFor(m => m.SelectedFields, new MultiSelectList(Model.LisOfFields, "Key", "Value"), new { @class = "form-control", style = "height: 250px;" })

    <input type="submit" value="Export" />
}

In the POST Controller: (Note following method has syntax errors)

[HttpPost]
public ActionResult Export(ViewModel model){
var exportFields = new List<string>();

foreach(var selectedfield in model.SelectedFields)
{
exportFields.Add(model.LisOfFields.First(s=> s.Key == selectedField).Value)

}

// EXPORT ALL in exportFields 

}

EDIT: Change your FORM to this:

@using (Html.BeginForm("ExportUsingEPPlus","Export", FormMethod.Post))
{
    @Html.ListBoxFor(m => m.SelectedFields, new MultiSelectList(Model.LisOfFields, "Key", "Value"), new { @class = "form-control", style = "height: 250px;" })
    <input type="submit" value="ExportUsingEPPlus" />
}

edited Mar 05 '15 at 16:25

answered Mar 05 '15 at 15:39

Dawood Awan

7,051
10
56
119

(Please correct me if I'm doing this wrong, not familiar with ViewModels) - On my `Models` folder I clicked `Add -> Class` and created `ExportAssetsViewModel`. The `public Dictionary / public int[] SelectedFields` are fine, but VS2013 is not liking my `public ViewModel() {}` saying method must have a return type? – Analytic Lunatic Mar 05 '15 at 15:50
1

I am named my class 'ViewModel' you named yours 'ExportAssetsViewModel', public ViewModel() is a constructor. You have to name your Constructor = **public ExportAssetsViewModel()** – Dawood Awan Mar 05 '15 at 15:51
Got it, thanks. On the Constructor then, must I manually type out all the desired Export fields of my Model? – Analytic Lunatic Mar 05 '15 at 15:53
That's upto you. This is just an example. You can store the Fields in a List, Database, config file, a separate class etc. Or you can use Reflection to get name of property.... http://stackoverflow.com/questions/2820660/get-name-of-property-as-a-string – Dawood Awan Mar 05 '15 at 15:56
Ok, I've got everything set up as demonstrated, but my break point in Controller is not being hit? Please see EDIT above. – Analytic Lunatic Mar 05 '15 at 16:08
1

@using (Html.BeginForm("ExportUsingEPPlus","Export", FormMehtod.Post)) – Dawood Awan Mar 05 '15 at 16:09
^ Use this as you form header.If you are loading your View with Index() then MVC will look for [HttpPost] public ActionResult Index(ViewModel model) – Dawood Awan Mar 05 '15 at 16:10
Thanks! Now my breakpoint is being hit, but when I move down to `foreach(var selectedField in model.SelectedFields)` F11 flags `foreach`, `model.SelectedFields`, and then `in` gets hit with `An exception of type 'System.NullReferenceException' occurred in InventoryTracker.dll but was not handled in user code. Additional information: Object reference not set to an instance of an object.`...? Fro this test I only selected the `[owner]` value for export. – Analytic Lunatic Mar 05 '15 at 16:13
You are not using the correct: @Html.ListBox Please see my answer. – Dawood Awan Mar 05 '15 at 16:14
m => m.SelectedFields, this will tell MVC to bind selected values to Controller Action – Dawood Awan Mar 05 '15 at 16:15
Sorry, overlooked that. The view is not recognizing the properties `SelectedFields`/`ListOfExportFields`? Please see EDIT2. – Analytic Lunatic Mar 05 '15 at 16:25
Check the spelling of: **Model.ListOfExportFields** != **Model.LisOfFields** – Dawood Awan Mar 05 '15 at 16:27
Right? I declared my own as `ListOfExportFields` / `SelectedFields`. The `ListBox` is not recognizing either `SelectedFields` or `ListOfExportFields`, though it is recognizing... all of my INV_Assets properties? I don't even know why it is recognizing properties for INV_Assets given that I've changed the `@model` to be `ExportAssetsViewModel`? – Analytic Lunatic Mar 05 '15 at 16:45
Please see EDIT3 above. – Analytic Lunatic Mar 05 '15 at 17:16
Disregard EDIT3; See EDIT4 above and thanks for all your help! Would you happen to have any ideas/example of how to take the values added to `exportFields` in `ExportUsingEPPlus` and place them into cells for the new Excel spreadsheet? – Analytic Lunatic Mar 05 '15 at 17:44

hutchonoid · Answer 2 · 2015-03-05T15:59:00.520

Using ajax you could build up an array of selected items and post them with jquery:

function exportSelectedAssets() {
 var selectedValues = [];
 $("#exportListBox :selected").each(function() {
   selectedValues.push($(this).attr('value'));
 });

 $.post("/ExportUsingEPPlus/", { Ids: selectedValues  });
}

Please note I had to remove one of the id's on your rendered select as it had two.

Then you can change your controller action to accept an array of string values and decorate it with [HttpPost]:

[HttpPost]
public ActionResult ExportUsingEPPlus(string[] Ids)
{
   ...
}

jsFiddle [Demonstrates selected values]

Pass all selected MultiSelectList values to MVC Controller for EPPlus Export to Excel?

2 Answers2

Linked