I have an asp.net MVC5 project where I'm using the Kendo Grid to display table data and provide the user with Export-to-Excel functionality. These datasets are relatively small in their column counts. e.g. mostly 3-6 columns. However, I have some skeptical users that would like to be able to see the data that feeds some Kendo charting where the datasets are larger than I'd care to place in a Grid in the view/browser.
My question is, has anyone figured out a way to tap into the 'Export-to-Excel' functionality that is part of the Grid without displaying the Grid. I like all of the additional features that the 'Export' has for pushing the data into Excel so I'm wondering if this is feasible vs. rolling my own output to a csv file that can be opened by Excel.
Wondering if there's a way to tap into the Export-to-Excel function directly from the Controller so the data is served back to the user in Excel?
Here's an example from Telerik;
VIEW GRID Widget
<script src="//cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>
@(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.ProductViewModel>()
.Name("grid")
.HtmlAttributes( new { style = "width: 900px" } )
.Columns(columns => {
columns.Bound(p => p.ProductName).Width(300).Locked()
.ClientFooterTemplate("Total Count: #=count#")
.ClientGroupFooterTemplate("Count: #=count#");
columns.Bound(p => p.UnitPrice).Width(300);
columns.Bound(p => p.UnitsOnOrder).Width(300)
.ClientFooterTemplate("Average: #=average#")
.ClientGroupFooterTemplate("Average: #=average#");
columns.Bound(p => p.UnitsInStock).Width(300)
.ClientFooterTemplate("Min: #= min # Max: #= max #")
.ClientGroupHeaderTemplate("Units In Stock: #= value # (Count: #= count#)");
})
.ToolBar(tools => tools.Excel())
.Pageable()
.Sortable()
.Scrollable()
.Groupable()
.Excel(excel => excel
.FileName("Kendo UI Grid Export.xlsx")
.Filterable(true)
.ProxyURL(Url.Action("Excel_Export_Save", "Grid"))
)
.Reorderable(r => r.Columns(true))
.Resizable(r => r.Columns(true))
.ColumnMenu()
.DataSource(dataSource => dataSource
.Ajax()
.PageSize(7)
.Group(g => g.Add(p => p.UnitsInStock))
.Aggregates(aggregates =>
{
aggregates.Add(p => p.UnitsInStock).Min().Max().Count();
aggregates.Add(p => p.UnitsOnOrder).Average();
aggregates.Add(p => p.ProductName).Count();
aggregates.Add(p => p.UnitPrice).Sum();
})
.Read(read => read.Action("Excel_Export_Read", "Grid"))
)
)
CONTROLLER
using System.Collections.Generic;
using System.Web.Mvc;
using Kendo.Mvc.Examples.Models;
using Kendo.Mvc.Extensions;
using Kendo.Mvc.UI;
using System.Linq;
using System;
namespace Kendo.Mvc.Examples.Controllers
{
public partial class GridController : Controller
{
public ActionResult Excel_Export()
{
return View();
}
public ActionResult Excel_Export_Read([DataSourceRequest]DataSourceRequest request)
{
return Json(productService.Read().ToDataSourceResult(request));
}
[HttpPost]
public ActionResult Excel_Export_Save(string contentType, string base64, string fileName)
{
var fileContents = Convert.FromBase64String(base64);
return File(fileContents, contentType, fileName);
}
}
}
SERVICE
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace Kendo.Mvc.Examples.Models
{
public class ProductService : IDisposable
{
private SampleEntities entities;
public ProductService(SampleEntities entities)
{
this.entities = entities;
}
public IEnumerable<ProductViewModel> Read()
{
return entities.Products.Select(product => new ProductViewModel
{
ProductID = product.ProductID,
ProductName = product.ProductName,
UnitPrice = product.UnitPrice.HasValue ? product.UnitPrice.Value : default(decimal),
UnitsInStock = product.UnitsInStock.HasValue ? product.UnitsInStock.Value : default(short),
QuantityPerUnit = product.QuantityPerUnit,
Discontinued = product.Discontinued,
UnitsOnOrder = product.UnitsOnOrder.HasValue ? (int)product.UnitsOnOrder.Value : default(int),
CategoryID = product.CategoryID,
Category = new CategoryViewModel()
{
CategoryID = product.Category.CategoryID,
CategoryName = product.Category.CategoryName
},
LastSupply = DateTime.Today
});
}
public void Create(ProductViewModel product)
{
var entity = new Product();
entity.ProductName = product.ProductName;
entity.UnitPrice = product.UnitPrice;
entity.UnitsInStock = (short)product.UnitsInStock;
entity.Discontinued = product.Discontinued;
entity.CategoryID = product.CategoryID;
if (entity.CategoryID == null)
{
entity.CategoryID = 1;
}
if (product.Category != null)
{
entity.CategoryID = product.Category.CategoryID;
}
entities.Products.Add(entity);
entities.SaveChanges();
product.ProductID = entity.ProductID;
}
public void Update(ProductViewModel product)
{
var entity = new Product();
entity.ProductID = product.ProductID;
entity.ProductName = product.ProductName;
entity.UnitPrice = product.UnitPrice;
entity.UnitsInStock = (short)product.UnitsInStock;
entity.Discontinued = product.Discontinued;
entity.CategoryID = product.CategoryID;
if (product.Category != null)
{
entity.CategoryID = product.Category.CategoryID;
}
entities.Products.Attach(entity);
entities.Entry(entity).State = EntityState.Modified;
entities.SaveChanges();
}
public void Destroy(ProductViewModel product)
{
var entity = new Product();
entity.ProductID = product.ProductID;
entities.Products.Attach(entity);
entities.Products.Remove(entity);
var orderDetails = entities.Order_Details.Where(pd => pd.ProductID == entity.ProductID);
foreach (var orderDetail in orderDetails)
{
entities.Order_Details.Remove(orderDetail);
}
entities.SaveChanges();
}
public void Dispose()
{
entities.Dispose();
}
}
}
Link: http://demos.telerik.com/aspnet-mvc/grid/excel-export
BTW, what type of Architecture is this 'Classified' as? I love it and would like to read up on it.
