Feb 24, 2014, 5:42 AM
Export GridPanel (with Paging and filters) Data to Excel
Hi,
I have a gridpanel with paging and filters. Whenever I try and export a file, it only includes the current page from the gridpanel on the export file. I need all filtered data from the GridPanel. How can I do this?
I have a gridpanel with paging and filters. Whenever I try and export a file, it only includes the current page from the gridpanel on the export file. I need all filtered data from the GridPanel. How can I do this?
@(Html.X().GridPanel()
.ID("GridPanel1")
.Title("Records")
.Width(1100)
.Height(350)
.Store(Html.X().Store()
.RemoteSort(true)
.PageSize(20)
.Proxy(
Html.X().AjaxProxy()
.Url(Url.Action("ReadData"))
.ActionMethods(actions =>
{
actions.Read = HttpMethod.POST;
})
.Reader(
Html.X().JsonReader().Root("data")
)
)
.ID("Store1")
.Model(Html.X().Model()
.IDProperty("id")
.Fields(
new ModelField("Name", ModelFieldType.String),
new ModelField("DateString", ModelFieldType.Date, "MM/dd/yyyy"),
new ModelField("Location", ModelFieldType.String),
new ModelField("Code", ModelFieldType.String),
new ModelField("Amount", ModelFieldType.Float),
new ModelField("Status", ModelFieldType.String),
new ModelField("Email", ModelFieldType.String),
new ModelField("TimeStampString", ModelFieldType.Date, "MM/dd/yyyy HH:mm tt"),
new ModelField("LastUpdated", ModelFieldType.Date, "MM/dd/yyyy HH:mm tt"),
new ModelField("PaymentRecord", ModelFieldType.String)
)
)
.Sorters(
Html.X().DataSorter().Property("Name").Direction(Ext.Net.SortDirection.ASC)
)
)
.ColumnModel(
Html.X().Column().Text("Event Name").DataIndex("Name").Renderer("NameRenderer").Width(145),
Html.X().DateColumn().Text("Date").DataIndex("DateString").Align(Alignment.Center).Format("MM/dd/yyyy").Width(135),
Html.X().Column().Text("Location").DataIndex("Location").Width(140),
Html.X().Column().Text("Code").DataIndex("Code").Width(130),
Html.X().Column().Text("Amount").DataIndex("Amount").Renderer("Amount").Width(130),
Html.X().Column().Text("Status").DataIndex("Status").Width(124),
Html.X().Column().Text("Contact").DataIndex("Email").Width(135),
Html.X().DateColumn().Text("Created").DataIndex("TimeStampString").Align(Alignment.Center).Format("MM/dd/yyyy HH:mm").Width(135),
Html.X().DateColumn().Text("Last Updated").DataIndex("LastUpdated").Align(Alignment.Center).Format("MM/dd/yyyy HH:mm").Width(135)
)
.Features(
Html.X().GridFilters()
.Filters(
Html.X().StringFilter().DataIndex("Name"),
Html.X().DateFilter().DataIndex("DateString")
.DatePickerOptions(options =>
{
options.TodayText = "Now";
}),
Html.X().StringFilter().DataIndex("Location"),
Html.X().StringFilter().DataIndex("Code"),
Html.X().StringFilter().DataIndex("Amount"),
Html.X().ListFilter().DataIndex("Status").Options("Paid, Cancelled"),
Html.X().StringFilter().DataIndex("Email"),
Html.X().DateFilter().DataIndex("TimeStampString")
.DatePickerOptions(options =>
{
options.TodayText = "Now";
}),
Html.X().DateFilter().DataIndex("LastUpdated")
.DatePickerOptions(options =>
{
options.TodayText = "Now";
}),
Html.X().StringFilter().DataIndex("PaymentRecord")
)
)
.TopBar(
Html.X().Toolbar().Items(
Html.X().ToolbarFill(),
Html.X().Button().ID("btnXML").Text("To XML").Icon(Icon.PageCode)
.Listeners(btn => btn.Click.Fn = "submitDataXML"),
Html.X().Button().ID("btnExcel").Text("To Excel").Icon(Icon.PageExcel)
.Listeners(btn => btn.Click.Fn = "submitDataExcel"),
Html.X().Button().ID("btnCSV").Text("To CSV").Icon(Icon.PageAttach)
.Listeners(btn => btn.Click.Fn = "submitDataCSV")
)
)
.BottomBar(
Html.X().PagingToolbar()
)
Here is the Javascript code var submitDataExcel = function (evt, t, o) {
var url = '@Url.Action("ToExcel")';
submitData(url);
};
var submitData = function (url) {
var dataToSubmit = Ext.encode(App.GridPanel1.getRowsValues({ selectedOnly: false }));
Ext.net.DirectMethod.request({
url: url,
cleanRequest: true,
isUpload: true,
params: {
json: dataToSubmit
}
});
};
Here is the controller: public ActionResult ReadData(StoreRequestParameters parameters)
{
List<object> data = GridFiltersModel.Data;
FilterConditions fc = parameters.GridFilters;
//-- start filtering ------------------------------------------------------------
if (fc != null)
{
foreach (FilterCondition condition in fc.Conditions)
{
Comparison comparison = condition.Comparison;
string field = condition.Field;
FilterType type = condition.Type;
object value;
switch (condition.Type)
{
case FilterType.Boolean:
value = condition.Value<bool>();
break;
case FilterType.Date:
value = condition.Value<DateTime>();
break;
case FilterType.List:
value = condition.List;
break;
case FilterType.Numeric:
if (data.Count > 0 && data[0].GetType().GetProperty(field).PropertyType == typeof(int))
{
value = condition.Value<int>();
}
else
{
value = condition.Value<double>();
}
break;
case FilterType.String:
value = condition.Value<string>();
break;
default:
throw new ArgumentOutOfRangeException();
}
data.RemoveAll(
item =>
{
object oValue = item.GetType().GetProperty(field).GetValue(item, null);
IComparable cItem = oValue as IComparable;
DateTime date1 = new DateTime();
if (condition.Field.Contains("Date"))
{
if (cItem.ToString().Count() < 1)
{
date1 = condition.Value<DateTime>();
}
else if (condition.Field.Contains("EventDateString"))
{
date1 = DateTime.Parse(cItem.ToString());
}
else
{
date1 = DateTime.ParseExact(cItem.ToString(), "MM/dd/yyyy HH:mm tt", null);
}
}
switch (comparison)
{
case Comparison.Eq:
switch (type)
{
case FilterType.List:
return !(value as List<string>).Contains(oValue.ToString());
case FilterType.String:
return !oValue.ToString().StartsWith(value.ToString());
default:
return !cItem.Equals(value);
}
case Comparison.Gt:
return date1.CompareTo(value) < 1;
case Comparison.Lt:
return date1.CompareTo(value) > -1;
default:
throw new ArgumentOutOfRangeException();
}
}
);
}
}
//-- end filtering ------------------------------------------------------------
//-- start sorting ------------------------------------------------------------
if (parameters.Sort.Length > 0)
{
DataSorter sorter = parameters.Sort[0];
data.Sort(delegate(SalesPaymentRecord x, SalesPaymentRecord y)
{
object a;
object b;
int direction = sorter.Direction == Ext.Net.SortDirection.DESC ? -1 : 1;
a = x.GetType().GetProperty(sorter.Property).GetValue(x, null);
b = y.GetType().GetProperty(sorter.Property).GetValue(y, null);
return CaseInsensitiveComparer.Default.Compare(a, b) * direction;
});
}
//-- end sorting ------------------------------------------------------------
//-- start paging ------------------------------------------------------------
int limit = parameters.Limit;
if ((parameters.Start + parameters.Limit) > data.Count)
{
limit = data.Count - parameters.Start;
}
List<SalesPaymentRecord> rangeData = (parameters.Start < 0 || limit < 0) ? data : data.GetRange(parameters.Start, limit);
//-- end paging ------------------------------------------------------------
return this.Store(rangeData, data.Count);
}
public ActionResult ToExcel(string json)
{
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;
XslCompiledTransform xtExcel = new XslCompiledTransform();
System.IO.MemoryStream ms = new System.IO.MemoryStream();
xtExcel.Load(Server.MapPath("/Excel.xsl"));
xtExcel.Transform(xml, null, ms);
return File(ms.GetBuffer(), "application/vnd.ms-excel", "excelfile.xls");
}
I appreciate the help. Thanks in advance!