Example E4121
Visible to All Users

Grid View for ASP.NET MVC - How to use Reports to export a large amount of filtered data

This example demonstrates how to export a large amount of filtered data using LINQ-to-SQL.

Implementation Details

When you export a large amount of filtered data, Grid View export mechanism requests all data from a database and then filters it. Such a request can be fulfilled and the System.OutOfMemoryException exception can occur. To resolve this issue, request filtered data and export it using the XtraReports Suite.

C#
public ActionResult ExportTo(string filterString) { CriteriaOperator filterCriteria = CriteriaOperator.Parse(filterString); IList<Email> emails = LargeDatabaseDataProvider.GetEmailsByFilter(filterCriteria); XtraReport1 report = new XtraReport1(); report.DataSource = emails; ExportReport(report, "largeData", true, "Xlsx"); return GridViewPartial(); }

To pass the FilterExpression to a controller's action, obtain and save the expression in a custom client property:

C#
settings.CustomJSProperties = (s, e) => { MVCxGridView gridView = (MVCxGridView)s; e.Properties["cpGridFilterExpression"] = gridView.FilterExpression; };
Code
settings.CustomJSProperties = Sub(s, e) Dim gridView = TryCast(s, MVCxGridView) e.Properties("cpGridFilterExpression") = gridView.FilterExpression End Sub

When a user clicks the Export to XLSx button, the Click event handler adds filter expression contained in the custom client property to the collection of jQuery selector parameters.

JavaScript
function onClick(s, e) { var actionParams = $("form").attr("action").split("?filterString="); actionParams[1] = encodeURIComponent(GridView.cpGridFilterExpression); $("form").attr("action", actionParams.join("?filterString=")); }

Files to Review

Documentation

More Examples

Example Code

E4121/Controllers/HomeController.cs
C#
using DevExpress.Data.Filtering; using DevExpress.Web.Mvc; using DevExpress.XtraReports.UI; using E4121.Models; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Web; using System.Web.Mvc; namespace E4121.Controllers { public class HomeController : Controller { public ActionResult Index() { EmailDataGenerator.Register(); return View(); } [ValidateInput(false)] public ActionResult GridViewPartial() { return PartialView("_GridViewPartial"); } public ActionResult ExportTo(string filterString) { CriteriaOperator filterCriteria = CriteriaOperator.Parse(filterString); IList<Email> emails = LargeDatabaseDataProvider.GetEmailsByFilter(filterCriteria); XtraReport1 report = new XtraReport1(); report.DataSource = emails; ExportReport(report, "largeData", true, "Xlsx"); return GridViewPartial(); } public void ExportReport(XtraReport report, string fileName, bool saveAsFile, string fileFormat) { using (MemoryStream stream = new MemoryStream()) { report.ExportToXlsx(stream); string disposition = saveAsFile ? "attachment" : "inline"; Response.Clear(); Response.Buffer = false; Response.AppendHeader("Content-Type", string.Format("application/{0}", fileFormat)); Response.AppendHeader("Content-Transfer-Encoding", "binary"); Response.AppendHeader("Content-Disposition", string.Format("{0}; filename={1}.{2}", disposition, HttpUtility.UrlEncode(fileName).Replace("+", "%20"), fileFormat)); Response.BinaryWrite(stream.ToArray()); Response.End(); } } } }
E4121VB/Controllers/HomeController.vb
Visual Basic
Imports System.IO Imports DevExpress.Data.Filtering Imports DevExpress.Web.Mvc Imports DevExpress.XtraReports.UI Public Class HomeController Inherits System.Web.Mvc.Controller Function Index() As ActionResult EmailDataGenerator.Register() Return View() End Function <ValidateInput(False)> Public Function GridViewPartial() As ActionResult Return PartialView("_GridViewPartial") End Function Public Function ExportTo(ByVal filterString As String) As ActionResult Dim filterCriteria As CriteriaOperator = CriteriaOperator.Parse(filterString) Dim emails As IList(Of Email) = LargeDatabaseDataProvider.GetEmailsByFilter(filterCriteria) Dim report As XtraReport1 = New XtraReport1() report.DataSource = emails ExportReport(report, "largeData", True, "Xlsx") Return GridViewPartial() End Function Public Sub ExportReport(ByVal report As XtraReport, ByVal fileName As String, ByVal saveAsFile As Boolean, ByVal fileFormat As String) Using stream As MemoryStream = New MemoryStream() report.ExportToXlsx(stream) Dim disposition As String = If(saveAsFile, "attachment", "inline") Response.Clear() Response.Buffer = False Response.AppendHeader("Content-Type", String.Format("application/{0}", fileFormat)) Response.AppendHeader("Content-Transfer-Encoding", "binary") Response.AppendHeader("Content-Disposition", String.Format("{0}; filename={1}.{2}", disposition, HttpUtility.UrlEncode(fileName).Replace("+", "%20"), fileFormat)) Response.BinaryWrite(stream.ToArray()) Response.End() End Using End Sub End Class
E4121/Views/Home/_GridViewPartial.cshtml
Razor
@{ var grid = Html.DevExpress().GridView<E4121.Models.Email>(settings => { settings.Name = "GridView"; settings.Width = Unit.Percentage(100); settings.CallbackRouteValues = new { Controller = "Home", Action = "GridViewPartial" }; settings.KeyFields(m => m.ID); settings.Settings.ShowFilterRow = true; settings.Columns.Add(m => m.From).Width = Unit.Pixel(200); settings.Columns.Add(m => m.Subject); settings.Columns.Add(m => m.Sent, column => { column.ColumnType = MVCxGridViewColumnType.DateEdit; column.Settings.AutoFilterCondition = AutoFilterCondition.Equals; column.Width = Unit.Pixel(100); column.Settings.AllowHeaderFilter = DefaultBoolean.True; column.SettingsHeaderFilter.Mode = GridHeaderFilterMode.DateRangePicker; column.Settings.AllowAutoFilter = DefaultBoolean.False; }); settings.Columns.Add("Size").Settings.AutoFilterCondition = AutoFilterCondition.Equals; settings.Columns.Add(m => m.HasAttachment, column => { column.Caption = "Attachment?"; column.ColumnType = MVCxGridViewColumnType.CheckBox; column.Width = Unit.Pixel(100); }); settings.CustomJSProperties = (s, e) => { MVCxGridView gridView = (MVCxGridView)s; e.Properties["cpGridFilterExpression"] = gridView.FilterExpression; }; }); } @grid.BindToLINQ("E4121.Models.LargeDatabaseDataContext", "Emails").GetHtml()
E4121VB/Views/Home/_GridViewPartial.vbhtml
Code
@Code Dim grid = Html.DevExpress(). GridView(Of E4121VB.Email)(Sub(settings) settings.Name = "GridView" settings.Width = Unit.Percentage(100) settings.CallbackRouteValues = New With {Key .Controller = "Home", Key .Action = "GridViewPartial"} settings.KeyFields(Function(m) m.ID) settings.Settings.ShowFilterRow = True settings.Columns.Add(Function(m) m.From).Width = Unit.Pixel(200) settings.Columns.Add(Function(m) m.Subject) settings.Columns.Add(Function(m) m.Sent, Sub(column) column.ColumnType = MVCxGridViewColumnType.DateEdit column.Settings.AutoFilterCondition = AutoFilterCondition.Equals column.Width = Unit.Pixel(100) column.Settings.AllowHeaderFilter = DefaultBoolean.True column.SettingsHeaderFilter.Mode = GridHeaderFilterMode.DateRangePicker column.Settings.AllowAutoFilter = DefaultBoolean.False End Sub) settings.Columns.Add(Function(m) m.Size).Settings.AutoFilterCondition = AutoFilterCondition.Equals settings.Columns.Add(Function(m) m.HasAttachment, Sub(column) column.Caption = "Attachment?" column.ColumnType = MVCxGridViewColumnType.CheckBox column.Width = Unit.Pixel(100) End Sub) settings.CustomJSProperties = Sub(s, e) Dim gridView = TryCast(s, MVCxGridView) e.Properties("cpGridFilterExpression") = gridView.FilterExpression End Sub End Sub) End Code @grid.BindToLINQ("E4121VB.LargeDatabaseDataContext", "Emails").GetHtml()
E4121/Views/Home/Index.cshtml
Razor
@using E4121.Models @{ ViewBag.Title = "Home Page"; } @if (!DatabaseGenerator.IsReady("GeneratedEmailTable")) { @Html.Partial("_CreateDatabasePartial", "GeneratedEmailTable") } else { <script type="text/javascript"> function onClick(s, e) { var actionParams = $("form").attr("action").split("?filterString="); actionParams[1] = encodeURIComponent(GridView.cpGridFilterExpression); $("form").attr("action", actionParams.join("?filterString=")); } </script> using (Html.BeginForm(new { Controller = "Home", Action = "ExportTo" })) { Html.DevExpress().Button(btn => { btn.Name = "btnExportToXLSX"; btn.Text = "Export to XLSx"; btn.UseSubmitBehavior = true; btn.ClientSideEvents.Click = "onClick"; }).Render(); <br /> Html.RenderAction("GridViewPartial"); } }
E4121VB/Views/Home/Index.vbhtml
Code
@Code ViewData("Title") = "Home Page" End Code @Code If Not E4121VB.DatabaseGenerator.IsReady("GeneratedEmailTable") Then @Html.Partial("_CreateDatabasePartial", "GeneratedEmailTable") Else End Code <script type="text/javascript"> function onClick(s, e) { var actionParams = $("form").attr("action").split("?filterString="); actionParams[1] = encodeURIComponent(GridView.cpGridFilterExpression); $("form").attr("action", actionParams.join("?filterString=")); } </script> @Using (Html.BeginForm(New With {.Controller = "Home", .Action = "ExportTo"})) Html.DevExpress().Button(Sub(btn) btn.Name = "btnExportToXLSX" btn.Text = "Export to XLSx" btn.UseSubmitBehavior = True btn.ClientSideEvents.Click = "onClick" End Sub).Render() ViewContext.Writer.Write("<br />") Html.RenderAction("GridViewPartial") End Using @Code End If End Code

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.