[ASP.net] MVC에서 엑셀 출력하기 1

출처 : http://adambielecki.blogspot.kr/2013/05/exporting-data-to-excel-and-csv-in.html

Exporting data to Excel and CSV in ASP.NET MVC application

public class ClientsList

    {

        public ClientsList(string firstname, string lastname, DateTime dob, string email)

        {

            this.FirstName = firstname;

            this.LastName = lastname;

            this.Dob = dob;

            this.Email = email;

        }

        public string FirstName { set; get; }

        public string LastName { set; get; }

        public string Email { set; get; }

        public DateTime Dob { set; get; }

    }

Now in Home controller we are going to populate this class with some data. Make sure you instantiate this List within class so it will be available in every method in this controller.

CONTROLLER

public class HomeController : Controller

    {

List<ClientsList> ClientsList = new List<ClientsList>

            {

                 new ClientsList ( "Adam",  "Bielecki", DateTime.Parse("22/05/1986"),       "adamb@example.com" ), 

                 new ClientsList (  "George", "Smith",  DateTime.Parse("10/10/1990"), "george@example.com" )

            };

public ActionResult Index()

        {

            return View(ClientsList);

        }

}

VIEW

We are going to display data that came from controller and model we are using is List<ClientsList>.

@{

    ViewBag.Title = "Export";

}

@model List<ExportToExcel_PDF_CSV.Models.ClientsList>

<h2>

    Export to PDF, Excel CSV Example</h2>

    <table>

        <thead>

            <tr>

                <th>

                    First Name

                </th>

                <th>

                    Last Name

                </th>

                <th>

                    Date Of Birth Name

                </th>

                <th>

                    Email

                </th>

            </tr>

        </thead>

        @foreach (var item in Model)

        {

            <tr>

                <td>

                    @item.FirstName

                </td>

                <td>@item.LastName

                </td>

                <td>@item.Dob

                </td>

                <td>

                    @item.Email

                </td>

            </tr>

        }

    </table>

    <div class="ExportSection">

        @Html.ActionLink("Export to Csv", "ExportClientsListToCSV")

        @Html.ActionLink("Export to Excel", "ExportClientsListToExcel")

       

    </div>

Add some styling to your Site.css style sheet.

/* Custom added

----------------------------------------------------------*/

.ExportSection

{

    margin:10px 0;

    float:left;

   

}

.ExportSection a

{

    text-decoration:none;

    padding:0 5px 0 0;

   

}

Now in controller we want to add 2 methods:

ExportClientsListToExcel

ExportClientsListToCSV

   public void ExportClientsListToCSV()

        {

         

            StringWriter sw = new StringWriter();

            sw.WriteLine("\"First Name\",\"Last Name\",\"Email\"");

            Response.ClearContent();

        Response.AddHeader("content-disposition","attachment;filename=Exported_Users.csv");

            Response.ContentType = "text/csv";

            foreach (var line in ClientsList)

            {

                sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\"",

                                           line.FirstName,

                                           line.LastName,

                                           line.Dob,

                                           line.Email));

            }

            Response.Write(sw.ToString());

            Response.End();

        }

public void ExportClientsListToExcel()

        {

            var grid = new System.Web.UI.WebControls.GridView();

            grid.DataSource = /*from d in dbContext.diners

                              where d.user_diners.All(m => m.user_id == userID) && d.active == true */

                              from d in ClientsList

                              select new

                              {

                                  FirstName = d.FirstName,

                                  LastName = d.LastName,

                                  DOB = d.Dob,

                                  Email = d.Email

                              };

            grid.DataBind();

            Response.ClearContent();

       Response.AddHeader("content-disposition", "attachment; filename=Exported_Diners.xls");

            Response.ContentType = "application/excel";

            StringWriter sw = new StringWriter();

            HtmlTextWriter htw = new HtmlTextWriter(sw);

            grid.RenderControl(htw);

            Response.Write(sw.ToString());

            Response.End();

        }

In both cases we are using HttpResponseBase (Response) and stream writer.

In excel export we are using grid, so we can bind data to it.

You can download project from here : ExportToExcelCSVASPMVC