Available in the Full Version

ASP.NET MVC Excel Library - Create Excel Worksheet

You can export our jQuery Grid’s data to a Microsoft Excel® spreadsheet.

This sample is designed for a larger screen size.

On mobile, try rotating your screen, view full size, or email to another device.

Our Excel library also provides the option of selecting from a variety of different export formats, as well as styling options for the exported spreadsheet.

Code View

Copy to Clipboard

                @using Infragistics.Web.Mvc
@using IgniteUI.SamplesBrowser.Models
@model IQueryable<IgniteUI.SamplesBrowser.Models.Northwind.Order>
<!DOCTYPE html>

<html>
<head>
    <title></title>

    <!-- Ignite UI for jQuery Required Combined CSS Files -->
    <link href="http://cdn-na.infragistics.com/igniteui/2023.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" />
    <link href="http://cdn-na.infragistics.com/igniteui/2023.2/latest/css/structure/infragistics.css" rel="stylesheet" />

    <script src="http://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>
    <script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
    <script src="http://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>

    <!-- Ignite UI for jQuery Required Combined JavaScript Files -->
    <script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.core.js"></script>
    <script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.lob.js"></script>

    <style type="text/css">
        .export-excel-sample fieldset {
            margin: 5px;
            margin-bottom: 10px;
            padding: 10px;
            border: 1px solid #CCCCCC;
        }

            .export-excel-sample fieldset label {
                display: block;
                margin-bottom: 3px;
            }

            .export-excel-sample fieldset input {
                margin-bottom: 5px;
                float: left;
                clear: left;
            }

            .export-excel-sample fieldset label {
                float: left;
                width: 160px;
                padding-top: 2px;
            }

            .export-excel-sample fieldset fieldset {
                display: inline-block;
                vertical-align: middle;
                width: auto;
            }

        #grid1_container {
            margin: 15px auto;
        }

        .export-excel-sample .button-container {
            position: relative;
            display: inline-block;
            vertical-align: middle;
            height: auto;
        }
    </style>
</head>
<body>
    @using(Html.BeginForm())
    {

        <div class="export-excel-sample">
            <fieldset>
                <legend>Export Options</legend>
                <fieldset>
                    <legend>Export Type</legend>
                    <input type="radio" name="exportType" id="currentPage" value="false" checked="checked" />
                    <label for="currentPage">Current Page</label>
                    <input type="radio" name="exportType" id="allPages" value="true" />
                    <label for="allPages">All Pages</label>
                </fieldset>
                <fieldset>
                    <legend>Export Format</legend>
                    <input type="radio" name="exportFormat" id="Excel2007" value="true" checked="checked" />
                    <label for="Excel2007">Excel 2007 Format</label>
                    <input type="radio" name="exportFormat" id="Excel97-03" value="false" />
                    <label for="Excel97-03">Excel 97 2003 Format</label>
                </fieldset>
                <div class="button-container">
                    <input id="btnSubmit" type="submit" value="Export Button" />
                </div>
            </fieldset>
        </div>

        <script type="text/javascript">
            $(function () {
                var grid = $("#grid1"),
                    pageSize = $("#pageSize"),
                    pageNumber = $("#pageNumber");

                pageNumber.val(0);
                pageSize.val(0);

                grid.on("iggridrendered", function (e, ui) {
                    pageSize.val(grid.igGridPaging("option", "pageSize"));
                    pageNumber.val(grid.igGridPaging("option", "currentPageIndex"));
                });

                grid.on("iggridpagingpageindexchanged", function (e, ui) {
                    pageNumber.val(ui.pageIndex);
                });

                grid.on("iggridpagingpagesizechanged", function (e, ui) {
                    pageSize.val(ui.pageSize);
                    pageNumber.val(0);
                });
            });
        </script>

        @(Html.Infragistics().Grid(Model).ID("grid1")
        .PrimaryKey("OrderID")
        .AutoGenerateColumns(false)
        .Columns(column =>
        {
            column.For(x => x.OrderID).HeaderText("Order ID").DataType("number");
            column.For(x => x.ContactName).HeaderText("Contact Name");
            column.For(x => x.ShipAddress).HeaderText("Shipping Address");
            column.For(x => x.OrderDate).HeaderText("Order Date");
        }).Features(features =>
        {
            features.Paging();
        }).Height("500").Width("100%").DataSourceUrl(Url.Action("PagingGetData")).DataBind().Render())

        <input type="hidden" name="pageSize" id="pageSize" value="" />
        <input type="hidden" name="pageNumber" id="pageNumber" value="" />
    }
</body>
</html>

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

#region Excel Exporting

using System.Drawing;
using System.Security;
using Infragistics.Web.Mvc;
using Infragistics.Documents.Excel;
using IgniteUI.SamplesBrowser.Models.Northwind;
using IgniteUI.SamplesBrowser.Models.Repositories;

#endregion

namespace IgniteUI.SamplesBrowser.Controllers
{
    public class InfragisticsExcelController : Controller
    {
        private IEnumerable<Order> orders = null;

        // Orders data
        private IEnumerable<Order> Orders
        {
            get
            {
                try
                {
                    if (orders == null)
                        orders = RepositoryFactory.GetOrderRepository().Get().Take(100).AsQueryable();
                    return orders;
                }
                catch (Exception)
                {
                    return new List<Order>().AsQueryable();
                }
            }
        }

        //
        // GET: /InfragisticsExcel/
        [ActionName("create-excel-worksheet")]
        public ActionResult ExcelExporter()
        {
            return View("create-excel-worksheet", Orders);
        }

        [GridDataSourceAction]
        [ActionName("PagingGetData")]
        public ActionResult PagingGetData()
        {
            return View("create-excel-worksheet", Orders);
        }

        [HttpPost]
        [ActionName("create-excel-worksheet")]
        public void ExcelExporter(int pageNumber, int pageSize, bool exportType, bool exportFormat)
        {
            pageNumber++;
            List<Order> newOrders = Orders.ToList();
            bool exportAllPages = exportType;
            WorkbookFormat excelFormat;

            if(exportFormat)
                excelFormat = WorkbookFormat.Excel2007;
            else
                excelFormat = WorkbookFormat.Excel97To2003;

            if(exportAllPages)
            {
                newOrders = newOrders
                    .Select(c => new Order
                    {
                        OrderID = c.OrderID,
                        ContactName = c.ContactName,
                        ShipAddress = c.ShipAddress,
                        OrderDate = c.OrderDate
                    })
                    .ToList();
            }
            else
            {
                newOrders = newOrders
                    .Skip<Order>(pageSize * (pageNumber - 1))
                    .Take<Order>(pageSize)
                    .Select(c => new Order
                    {
                        OrderID = c.OrderID,
                        ContactName = c.ContactName,
                        ShipAddress = c.ShipAddress,
                        OrderDate = c.OrderDate
                    })
                    .ToList();
            }

            ExcelExportingModel exportModel = new ExcelExportingModel(excelFormat);
            exportModel.PopulateExcelWorkbook(newOrders);
            SendForDownload(exportModel.ExcelWorkbook, excelFormat);
        }
        [SecuritySafeCritical]
        private void SendForDownload(Workbook document, WorkbookFormat excelFormat)
        {
            string documentFileNameRoot;
            documentFileNameRoot = string.Format("Document.{0}", excelFormat == WorkbookFormat.Excel97To2003 ? "xls" : "xlsx");
             
            Response.Clear();
            Response.AppendHeader("content-disposition", "attachment; filename=" + documentFileNameRoot);
            Response.ContentType = "application/octet-stream";
            document.SetCurrentFormat(excelFormat);
            document.Save(Response.OutputStream);
            Response.End();
        }

        #region Excel Report Model

        class ExcelExportingModel
        {
            #region Members

            #region Private Members

            private Workbook excelWorkbook;

            #endregion

            #endregion

            #region Constructor

            public ExcelExportingModel()
            {
                this.excelWorkbook = new Workbook();
            }

            public ExcelExportingModel(WorkbookFormat exportFormat)
            {
                this.excelWorkbook = new Workbook(exportFormat);
            }

            #endregion
            
            #region Properties

            public Workbook ExcelWorkbook
            {
                get
                {
                    return this.excelWorkbook;
                }
            }

            #endregion

            #region Public Methods

            public void PopulateExcelWorkbook(List<Order> data)
            {
                Worksheet currentWorksheet = this.excelWorkbook.Worksheets.Add("WorkSheet1");

                foreach (var cell in currentWorksheet.GetRegion("A1:D1"))
                {
                    cell.CellFormat.Fill = CellFill.CreateSolidFill(Color.Gray);
                    cell.CellFormat.Font.ColorInfo = new WorkbookColorInfo(Color.White);
                }

                currentWorksheet.Rows[0].Cells[0].Value = "Order ID";
                currentWorksheet.Rows[0].Cells[1].Value = "Contact Name";
                currentWorksheet.Rows[0].Cells[2].Value = "Shipping Address";
                currentWorksheet.Rows[0].Cells[3].Value = "Order Date";
                
                currentWorksheet.Columns[0].Width = 3000;
                currentWorksheet.Columns[0].CellFormat.Alignment = HorizontalCellAlignment.Left;
                currentWorksheet.Columns[1].Width = 7100;
                currentWorksheet.Columns[2].Width = 3000;
                currentWorksheet.Columns[2].CellFormat.Alignment = HorizontalCellAlignment.Left;
                currentWorksheet.Columns[3].Width = 6100;

                int i = 1;
                foreach (Order order in data)
                {
                    currentWorksheet.Rows[i].Cells[0].Value = order.OrderID;
                    currentWorksheet.Rows[i].Cells[1].Value = order.ContactName;
                    currentWorksheet.Rows[i].Cells[2].Value = order.ShipAddress;
                    currentWorksheet.Rows[i].Cells[3].Value = order.OrderDate != null ? string.Format("{0:d}", order.OrderDate) : "";
                    i++;
                }
            }

            #endregion

        }


        #endregion

    }
}

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

namespace IgniteUI.SamplesBrowser.Models.Northwind
{
    public class Order
    {
        public int OrderID { get; set; }
        public string CustomerID { get; set; }
        public Nullable<int> EmployeeID { get; set; }
        public Nullable<System.DateTime> OrderDate { get; set; }
        public Nullable<System.DateTime> RequiredDate { get; set; }
        public Nullable<System.DateTime> ShippedDate { get; set; }
        public Nullable<int> ShipVia { get; set; }
        public Nullable<decimal> Freight { get; set; }
        public string ShipName { get; set; }
        public string ShipAddress { get; set; }
        public string ShipCity { get; set; }
        public string ShipRegion { get; set; }
        public string ShipPostalCode { get; set; }
        public string ShipCountry { get; set; }
        public string ContactName { get; set; }
        public string EmployeeName { get; set; }
        public int ShipperID { get; set; }
        public string ShipperName { get; set; }
        public decimal TotalPrice { get; set; }
        public int TotalItems { get; set; }
    }
}

Ignite UI for jQuery | Samples

Search Results

ASP.NET MVC Excel Library - Create Excel Worksheet

Code View