Action methods that convert model objects into Excel sheet and text file : The Official Microsoft ASP.NET Forums

Reply

johnjohn1231...

Participant

922 Points

Action methods that convert model objects into Excel sheet and text file

Dec 24, 2012 09:32 AM|LINK

I have the following view inside my asp.net mvc web application:, it simply display the model data inside a table and there are three check boxes at the table Colum heads to select which columns should be extracted to excel (.xls) or text (.csv) files. The view is:-

@model MvcApplication4.Models.SelectedCustomers

@{
    ViewBag.Title = "CustomerDetials";
}
<h3>Select Customers Detials</h3>
<table>
    <tr>
        <th>
            NAME @Html.CheckBox("Name",true)
        </th>
        <th>
            Description @Html.CheckBox("Description",true)
        </th>
        <th>
            Address @Html.CheckBox("Address",true)
        </th>
    </tr>
@foreach (var item in Model.Info) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.description)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.address)
        </td>
    </tr>
}

</table>
<p>
@Html.ActionLink("Back","customer","Home") |
<a href="">extract to excel</a> |
<a href="">extract to text file</a>
</p>

But can anyone provide some help on how i can accomplish the following:-

Develop a action methods that will return & open an excel sheet containing the SelectedCustomer model objects when the user click on the " extract to excel " link.
Develop a action methods that will return & open a .csv file containing the SelectedCustomer model objects when the user click on the " extract to text file " link.
How can i only extract the columns that have their check box "selected".

Thanks & Best Regards

Reply

RichardY

Star

8376 Points

1573 Posts

Re: Action methods that convert model objects into Excel sheet and text file

Dec 24, 2012 01:51 PM|LINK

You will probably get better help here if you keep your threads to one question and show that you have first tried to accomplish the task on your own - ussually by posting code (even if it does not work). The more narrowly defined the question, the better the responses. I assume that the model data is from a database.

Basically you want to test for the presence of the checkbox in the Request (you can use model binding if you want). A browser will not put a value for an unchecked checkbox in the Request query string,so if the name of the checkbox is in the Request, you know it was checked.

Then get the appropriate data, create the csv or excel and stream that to the response (which is returned to the browser.)

For excel I use a third party product, NPOI.

http://madskristensen.net/post/Export-a-DataTable-to-Excel-in-ASPNET.aspx

http://npoi.codeplex.com/

Reply

CPrakash82

All-Star

18284 Points

2841 Posts

Re: Action methods that convert model objects into Excel sheet and text file

Dec 24, 2012 04:59 PM|LINK

You can create a derived action result to return Excel document.

http://stephenwalther.com/archive/2008/06/16/asp-net-mvc-tip-2-create-a-custom-action-result-that-returns-microsoft-excel-documents.aspx

http://cprakash.com

Reply

johnjohn1231...

Participant

922 Points

871 Posts

Re: Action methods that convert model objects into Excel sheet and text file

Dec 25, 2012 07:30 PM|LINK

I have tired the folloiwng:-

i have the following view:-

@model MvcApplication4.Models.SelectedCustomers

@{
    ViewBag.Title = "CustomerDetials";
}

<h3>Select Customers Detials</h3>

@using (Html.BeginForm("Export", null))
{

<table>
    <tr>
        <th>
            ORGANIZATION NAME @Html.CheckBox("IncludeName",true)
        </th>
         <th>
            LOGIN URI @Html.CheckBox("IncludeURI",true)
        </th>
        <th>
            SUPPORT EMAIL @Html.CheckBox("IncludeEmail",true)
        </th>
         </tr>

@foreach (var item in Model.Info) {
    <tr>
             <td>
            @Html.DisplayFor(modelItem => item.ORG_NAME)
        </td>
      
        <td>
            @Html.DisplayFor(modelItem => item.LOGIN_URI)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.SUPPORT_EMAIL)
        </td>
    </tr>
}

</table>
<p>
@Html.ActionLink("Back","customer","Home") |
        <button type="submit" name="format" value="xls">extract to excel</button> |
        <button type="submit" name="format" value="csv">extract to text file</button>
    </p>}

And i have the folloiwng action methods:-

 public ActionResult Export(ExportViewModel exportOptions,IEnumerable<AccountDefinition> sc)
        {
          //  var accounts = GetAccounts();
            if (exportOptions.Format == "csv")
            {
                return sc.AsCsvResult(exportOptions);
            }
            else if (exportOptions.Format == "xls")
            {
                return sc.AsXlsResult(exportOptions);
            }

            throw new NotSupportedException(
                string.Format("Unsupported format: {0}", exportOptions.Format)
            );
        }

And the have added the folloiwng model class:-

namespace MvcApplication4.Models
{
    public class SelectedCustomers
    {
        public IEnumerable<AccountDefinition> Info { get; set; }
    }
    public static class ActionResultextensions
    {
        public static ActionResult AsCsvResult(this IEnumerable<AccountDefinition> accounts, ExportViewModel exportOptions)
        {
            return new CsvResult(accounts, exportOptions);
        }

        public static ActionResult AsXlsResult(this IEnumerable<AccountDefinition> accounts, ExportViewModel exportOptions)
        {
            return new XlsResult(accounts, exportOptions);
        }
    }
}

and the folloiwng model classes:-

namespace MvcApplication4.Models
{
    public class ExportViewModel
    {
        public string Format { get; set; }
        public bool IncludeName { get; set; }
        public bool IncludeURI { get; set; }
        public bool IncludeEmail { get; set; }

    }
}

and the following model class:-

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net.Mime;
using System.Web;
using System.Web.Mvc;

namespace MvcApplication4.Models

{
public abstract class ExportAccountsResult : ActionResult
{
    protected ExportAccountsResult(IEnumerable<AccountDefinition> accounts, ExportViewModel exportOptions)
    {
       
        this.Accounts = accounts;
        this.ExportOptions = exportOptions;
    }

    protected IEnumerable<AccountDefinition> Accounts { get; private set; }
    protected ExportViewModel ExportOptions { get; private set; }

    protected abstract string ContentType { get; }
    protected abstract string Filename { get; }

    public override void ExecuteResult(ControllerContext context)
    {
        var response = context.HttpContext.Response;
        response.ContentType = ContentType;
        var cd = new ContentDisposition
        {
            FileName = this.Filename,
            Inline = false
        };
        response.AddHeader("Content-Disposition", cd.ToString());

        // TODO: Use a real CSV parser here such as https://github.com/JoshClose/CsvHelper/wiki/Basics
        // and never roll your own parser as shown in this oversimplified
        // example. Here's why: http://secretgeek.net/csv_trouble.asp
        using (var writer = new StreamWriter(response.OutputStream))
        {
            foreach (var account in this.Accounts)
            {
                var values = new List<object>();
                if (this.ExportOptions.IncludeName)
                {
                    values.Add(account.ORG_NAME);
                }
                if (this.ExportOptions.IncludeURI)
                {
                    values.Add(account.LOGIN_URI);
                }
                if (this.ExportOptions.IncludeEmail)
                {
                    values.Add(account.SUPPORT_EMAIL);
                }
                writer.WriteLine(string.Join(", ", values));
            }
        }
    }
}
}

and the following model class:-

namespace MvcApplication4.Models
{
    public class CsvResult : ExportAccountsResult
    {
        public CsvResult(IEnumerable<AccountDefinition> accounts, ExportViewModel exportOptions)
            : base(accounts, exportOptions)
        {
        }

        protected override string ContentType
        {
            get { return "text/csv"; }
        }

        protected override string Filename
        {
            get { return "accounts.csv"; }
        }
    }

}

and the following model class:-

namespace MvcApplication4.Models
{
    public class XlsResult : ExportAccountsResult
    {
        public XlsResult(IEnumerable<AccountDefinition> accounts, ExportViewModel exportOptions)
            : base(accounts, exportOptions)
        {
        }

        protected override string ContentType
        {
            get { return "application/vnd.ms-excel"; }
        }

        protected override string Filename
        {
            get { return "accounts.csv"; }
        }
    }
}

But when i run the application i got the following exception :-

System.NullReferenceException was unhandled by user code HResult=-2147467261 Message=Object reference not set to an instance of an object. Source=MvcApplication4 StackTrace: at MvcApplication4.Models.ExportAccountsResult.ExecuteResult(ControllerContext context) in c:\Users\Administrator\Documents\Visual Studio 2012\Projects\MvcApplication4\MvcApplication4\Models\ExportAccountResult.cs:line 43 at System.Web.Mvc.ControllerActionInvoker.InvokeActionResult(ControllerContext controllerContext, ActionResult actionResult) at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass1c.<InvokeActionResultWithFilters>b__19() at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilter(IResultFilter filter, ResultExecutingContext preContext, Func`1 continuation) InnerException:

on this code inside the ExportAccountResult.cs:-

foreach (var account in this.Accounts)

So what might be getting wrong? BR

Edited by johnjohn123123 on Dec 25, 2012 07:42 PM

Reply

CPrakash82

All-Star

18284 Points

2841 Posts

Re: Action methods that convert model objects into Excel sheet and text file

Dec 25, 2012 11:31 PM|LINK

johnjohn123123

on this code inside the ExportAccountResult.cs:-
foreach (var account in this.Accounts)
So what might be getting wrong? BR

Looks like your Accounts object is null or not initialized, make sure you have it initialized while passing.

johnjohn123123

public static class ActionResultextensions 
    { 
        public static ActionResult AsCsvResult(this IEnumerable<AccountDefinition> accounts, ExportViewModel exportOptions) 
        {

Ideally this should be a controller extension, it doesn't make any difference but not good.

change it like this.

public static class ControllerExtensions 
    { 
        public static ActionResult AsCsvResult(this Controller controller, IEnumerable<AccountDefinition> accounts, ExportViewModel exportOptions) 
        { 


Inside your action method you should call it like this.

return this.AsCsvResult(accounts, exportOptions);

http://cprakash.com

Reply

RichardY

Star

8376 Points

1573 Posts

Re: Action methods that convert model objects into Excel sheet and text file

Dec 26, 2012 06:59 PM|LINK

johnjohn123123

public ActionResult Export(ExportViewModel exportOptions,IEnumerable<AccountDefinition> sc)

When the form posts to this action is the model updater filling the sc list? Set a break point and see if sc has the values you expect. If so, then disregard the rest of this reply.

Binding a request query string to a list is a little complicated and very much dependant upon what version MVC you are using. Since in your view you do not filter or otherwise change the contents of the list that is rendered in the view model, I would suggest that you do not try to bind the list in the action parameter but simply retrieve it in the controller action from the same source you did for the view model. That way you can be assued that your Accounts list will not be null.

As a further note, I try to always test for null before iterating or otherwise using a list.

Reply

johnjohn1231...

Participant

922 Points

871 Posts

Re: Action methods that convert model objects into Excel sheet and text file

Dec 26, 2012 07:50 PM|LINK

I ended up with the following code, first of all the view code is:-

model MvcApplication4.Models.SelectedCustomers



<h3>Select Customers Detials</h3>

@using (Html.BeginForm("Export", null))
{
    Int32 c = -1;
    <table>
        <tr>
            <th>
                NAME @Html.CheckBox("IncludeName", true)
            </th>
            <th>
                Description @Html.CheckBox("IncludeDescription", true)
            </th>
            <th>
                Address @Html.CheckBox("IncludeAddress", true)
            </th>
        </tr>
        @foreach (var item in Model.Info) {
            c++;
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.ORG_NAME)
                
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.LOGIN_URI)
                   
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.SUPPORT_EMAIL)
                    
                </td>
             
               @Html.Hidden(String.Format("Info[{0}].ORG_ID", c), item.ORG_ID)
               @Html.Hidden(String.Format("Info[{0}].ORG_NAME", c), item.ORG_NAME)
               @Html.Hidden(String.Format("Info[{0}].LOGIN_URI", c), item.LOGIN_URI)
               @Html.Hidden(String.Format("Info[{0}].SUPPORT_EMAIL", c), item.SUPPORT_EMAIL)
            </tr>
        }
    </table>

    <p>
        
        <button type="submit" name="Format" value="xls">Export to excel</button> |
        <button type="submit" name="Format" value="csv">Export to text file</button>
    </p>
    <p>
        @Html.ActionLink("Back","customer","Home")
        
    </p>
}

and the action methods is now :-

[Authorize]
        [HttpPost]
        public ActionResult Export(ExportViewModel exportOptions, SelectedCustomers sc)
        {
          // var accounts = GetAccount();
           var accounts = sc.Info.ToList();
            if (exportOptions.Format == "csv")
            {
                return accounts.AsCsvResult(exportOptions);
            }
            else if (exportOptions.Format == "xls")
            {
                return accounts.AsXlsResult(exportOptions);
            }

            throw new NotSupportedException(
                string.Format("Unsupported format: {0}", exportOptions.Format)
            );
        }

now the text file and the excel sheet will be opened with the associated records. but i am facing these problems:-

1. The two files will open as .csv , without any differences, so is there a way to specify that a file should be opned using excel and the other using any avilable text editor.

2. I fail to add column headers inside the excel sheet, currenly the first excel row will contain data instead of the column headers?

So can anyone adivce on how i can implement the above two issues?

Thanks in advance for any help.

BR

Reply

RichardY

Star

8376 Points

1573 Posts

Re: Action methods that convert model objects into Excel sheet and text file

Dec 27, 2012 12:38 PM|LINK

johnjohn123123

@Html.Hidden(String.Format("Info[{0}].ORG_ID", c), item.ORG_ID)
               @Html.Hidden(String.Format("Info[{0}].ORG_NAME", c), item.ORG_NAME)
               @Html.Hidden(String.Format("Info[{0}].LOGIN_URI", c), item.LOGIN_URI)
               @Html.Hidden(String.Format("Info[{0}].SUPPORT_EMAIL", c), item.SUPPORT_EMAIL)

This markup should be inside a <td>.

As I said, one question per thread.

Action methods that convert model objects into Excel sheet and text file RSS

7 replies

johnjohn1231...

Action methods that convert model objects into Excel sheet and text file

RichardY

Re: Action methods that convert model objects into Excel sheet and text file

CPrakash82

Re: Action methods that convert model objects into Excel sheet and text file

johnjohn1231...

Re: Action methods that convert model objects into Excel sheet and text file

CPrakash82

Re: Action methods that convert model objects into Excel sheet and text file

johnjohn123123

johnjohn123123

RichardY

Re: Action methods that convert model objects into Excel sheet and text file

johnjohn123123

johnjohn1231...

Re: Action methods that convert model objects into Excel sheet and text file

RichardY

Re: Action methods that convert model objects into Excel sheet and text file

johnjohn123123

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft