This is a migrated thread and some comments may be shown as answers.

[Solved] Filtering and Paging with Excel Export

1 Answer 199 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Gianluca asked on 18 Apr 2013, 09:41 AM

I'm using Export to excel feature in my Telerik Radgrid, for this i'm using UseAllDataFields="true"
<MasterTableView CommandItemDisplay="Top" UseAllDataFields="true"> and working fine,
also the paging is working fine with internal filtering but the panel which shows ' 10 items in 1 Pages' after filtering i have only 1 record in Radgrid but the panel again shows ' 10 items in 1 Pages'
also if i change UseAllDataFields="false" then it works fine but then the export to excel feature stops.
Enclosed is my project. Can you help me?

Dll Telerik Version: 2012.3.1308.40

Thanks
Gianluca

Project
File: RadGrid.aspx
----------------------------------------------------------------------------------------------------------------------------------------------
ASPX
----------------------------------------------------------------------------------------------------------------------------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="RadGrid.aspx.cs" Inherits="TestTelerikAspxGrid.Web.RadGrid" %>

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN">

<html>

<head runat="server">

<title>RadGrid</title>

<script language="javascript" type="text/javascript">

function RequestStart(sender, args) {

if (args.get_eventTarget().indexOf("ExportToExcelButton") >= 0) {

args.set_enableAjax(false);

}

</script>

</head>

<body>

<form id="form1" runat="server">

<telerik:RadScriptManager runat="server" ID="RadScriptManager1" />

<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">

<AjaxSettings>

<telerik:AjaxSetting AjaxControlID="RadGridFootballPlayers">

<UpdatedControls>

<telerik:AjaxUpdatedControl ControlID="RadAjaxPanel"></telerik:AjaxUpdatedControl>

<telerik:AjaxUpdatedControl ControlID="RadGridFootballPlayers"></telerik:AjaxUpdatedControl>

</UpdatedControls>

</telerik:AjaxSetting>

</AjaxSettings>

</telerik:RadAjaxManager>

<telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server" Skin="Default">

</telerik:RadAjaxLoadingPanel>

<telerik:RadAjaxPanel runat="server" ID="RadAjaxPanel" LoadingPanelID="RadAjaxLoadingPanel1"

ClientEvents-OnRequestStart="RequestStart">

<br/>

<input id="Button1" type="button" value="button" runat="server" />

<br/>

<telerik:RadGrid ID="RadGridFootballPlayers" runat="server" Skin="WebBlue"

PageSize="10" GridLines="None" AutoGenerateColumns="false"

AllowSorting="True" AllowPaging="True" AllowMultiRowSelection="True"

AllowFilteringByColumn="True"

EnableLinqExpressions="false">

<ExportSettings ExportOnlyData="true" IgnorePaging="true" OpenInNewWindow="true">

<Excel Format="ExcelML"></Excel>

</ExportSettings>

<groupingsettings casesensitive="false"></groupingsettings>

<MasterTableView TableLayout="Fixed" CommandItemDisplay="Top" UseAllDataFields="true">

<CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false"></CommandItemSettings>

<Columns>

<telerik:GridBoundColumn UniqueName="Id" DataField="Id" HeaderText="Id" SortExpression="Id" AutoPostBackOnFilter="true" ShowFilterIcon="false" CurrentFilterFunction="Contains">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn UniqueName="Name" DataField="Name" HeaderText="Name" SortExpression="Name" AutoPostBackOnFilter="true" ShowFilterIcon="false" CurrentFilterFunction="Contains">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn UniqueName="Surname" DataField="Surname" HeaderText="Surname" SortExpression="Surname" AutoPostBackOnFilter="true" ShowFilterIcon="false" CurrentFilterFunction="Contains">

</telerik:GridBoundColumn>

<telerik:GridDateTimeColumn UniqueName="FirstSubscriptionDate" DataField="BirthDate" DataType="System.DateTime" DataFormatString="{0:dd/MM/yyyy HH:mm:ss}" HeaderText="First Sub Date" SortExpression="FirstSubscriptionDate" HeaderStyle-Width="150" AutoPostBackOnFilter="true" EnableTimeIndependentFiltering="true" AllowFiltering="false">

</telerik:GridDateTimeColumn>

<telerik:GridBoundColumn UniqueName="FootballClub" DataField="FootballClub" HeaderText="FootballClub" SortExpression="FootballClub" AutoPostBackOnFilter="true" ShowFilterIcon="false" CurrentFilterFunction="Contains">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn UniqueName="Role" DataField="Role" HeaderText="Role" SortExpression="Role" AutoPostBackOnFilter="true" ShowFilterIcon="false" CurrentFilterFunction="Contains">

</telerik:GridBoundColumn>

</Columns>

</MasterTableView>

<ClientSettings AllowDragToGroup="True">

<Selecting AllowRowSelect="True" />

</ClientSettings>

<PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>

</telerik:RadGrid>

</telerik:RadAjaxPanel>

</form>

</body>

</html>

----------------------------------------------------------------------------------------------------------------------------------------------
CS
----------------------------------------------------------------------------------------------------------------------------------------------

using System;

using System.Collections.Generic;

using System.Web.UI.WebControls;

using Telerik.Web.UI;

using Telerik.Web.UI.GridExcelBuilder;

using TestTelerikAspxGrid.Web.Bm;

using TestTelerikAspxGrid.Web.Entities;

namespace TestTelerikAspxGrid.Web

{

public partial class RadGrid : System.Web.UI.Page

{

private bool _isConfigured;

protected void Page_Load(object sender, EventArgs e)

{

Button1.ServerClick += RadButton1Click;

RadGridFootballPlayers.NeedDataSource += RadGridPendingFootballPlayersNeedDataSource;

RadGridFootballPlayers.PageSizeChanged += RadGridPendingFootballPlayersPageSizeChanged;

RadGridFootballPlayers.PageIndexChanged += RadGridPendingFootballPlayersPageIndexChanged;

RadGridFootballPlayers.SortCommand += RadGridPendingFootballPlayersSortCommand;

RadGridFootballPlayers.ExcelMLExportRowCreated += RadGridExcelMlExportRowCreated;

RadGridFootballPlayers.ExcelMLExportStylesCreated += RadGridExcelMlExportStylesCreated;

if (IsPostBack) return;

Session["GridData"] = new List<EntityFootballPlayer>();

}

protected void RadButton1Click(object sender, EventArgs e)

{

LoadData();

}

private void LoadData()

{

var footballPlayers = new ManagerRadGrid().GetFootballPlayers();

Session["GridData"] = footballPlayers;

RadGridFootballPlayers.DataSource = footballPlayers;

RadGridFootballPlayers.DataBind();

}

private void LoadDataEventRadGridPendingFootballPlayers()

{

RadGridFootballPlayers.DataSource = Session["GridData"];

RadGridFootballPlayers.DataBind();

}

protected void RadGridPendingFootballPlayersNeedDataSource(object sender, GridNeedDataSourceEventArgs e)

{

RadGridFootballPlayers.MasterTableView.DataSource = Session["GridData"];

}

protected void RadGridPendingFootballPlayersPageSizeChanged(object source, GridPageSizeChangedEventArgs e)

{

LoadDataEventRadGridPendingFootballPlayers();

}

protected void RadGridPendingFootballPlayersPageIndexChanged(object source, GridPageChangedEventArgs e)

{

LoadDataEventRadGridPendingFootballPlayers();

}

protected void RadGridPendingFootballPlayersSortCommand(object source, GridSortCommandEventArgs e)

{

LoadDataEventRadGridPendingFootballPlayers();

}

#region Export

protected void RadGridExcelMlExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)

{

if (e.RowType == GridExportExcelMLRowType.DataRow)

{

if (e.Row.Cells.Count == 0)

return;

if (!_isConfigured)

{

//Set Worksheet name

e.Worksheet.Name = "Order Details Extended";

//Set Column widths

foreach (ColumnElement column in e.Worksheet.Table.Columns)

{

if (e.Worksheet.Table.Columns.IndexOf(column) == 2)

column.Width = Unit.Point(180); //set width 180 to ProductName column

else

column.Width = Unit.Point(80); //set width 80 to the rest of the columns

}

//Set Page options

var pageSetup = e.Worksheet.WorksheetOptions.PageSetup;

pageSetup.PageLayoutElement.IsCenteredVertical = true;

pageSetup.PageLayoutElement.IsCenteredHorizontal = true;

pageSetup.PageMarginsElement.Left = 0.5;

pageSetup.PageMarginsElement.Top = 0.5;

pageSetup.PageMarginsElement.Right = 0.5;

pageSetup.PageMarginsElement.Bottom = 0.5;

pageSetup.PageLayoutElement.PageOrientation = PageOrientationType.Landscape;

//Freeze panes

e.Worksheet.WorksheetOptions.AllowFreezePanes = true;

e.Worksheet.WorksheetOptions.LeftColumnRightPaneNumber = 1;

e.Worksheet.WorksheetOptions.TopRowBottomPaneNumber = 1;

e.Worksheet.WorksheetOptions.SplitHorizontalOffset = 1;

e.Worksheet.WorksheetOptions.SplitVerticalOffest = 1;

e.Worksheet.WorksheetOptions.ActivePane = 2;

_isConfigured = true;

}

protected void RadGridExcelMlExportStylesCreated(object sender, GridExportExcelMLStyleCreatedArgs e)

{

//Add currency and percent styles

var priceStyle = new StyleElement("priceItemStyle");

priceStyle.NumberFormat.FormatType = NumberFormatType.Currency;

priceStyle.FontStyle.Color = System.Drawing.Color.Red;

e.Styles.Add(priceStyle);

var alternatingPriceStyle = new StyleElement("alternatingPriceItemStyle");

alternatingPriceStyle.NumberFormat.FormatType = NumberFormatType.Currency;

alternatingPriceStyle.FontStyle.Color = System.Drawing.Color.Red;

e.Styles.Add(alternatingPriceStyle);

var percentStyle = new StyleElement("percentItemStyle");

percentStyle.NumberFormat.FormatType = NumberFormatType.Percent;

percentStyle.FontStyle.Italic = true;

e.Styles.Add(percentStyle);

var alternatingPercentStyle = new StyleElement("alternatingPercentItemStyle");

alternatingPercentStyle.NumberFormat.FormatType = NumberFormatType.Percent;

alternatingPercentStyle.FontStyle.Italic = true;

e.Styles.Add(alternatingPercentStyle);

//Apply background colors

foreach (StyleElement style in e.Styles)

{

if (style.Id == "headerStyle")

{

style.InteriorStyle.Pattern = InteriorPatternType.Solid;

style.InteriorStyle.Color = System.Drawing.Color.Gray;

}

if (style.Id == "alternatingItemStyle" || style.Id == "alternatingPriceItemStyle" || style.Id == "alternatingPercentItemStyle" || style.Id == "alternatingDateItemStyle")

{

style.InteriorStyle.Pattern = InteriorPatternType.Solid;

style.InteriorStyle.Color = System.Drawing.Color.LightGray;

}

if (style.Id.Contains("itemStyle") || style.Id == "priceItemStyle" || style.Id == "percentItemStyle" || style.Id == "dateItemStyle")

{

style.InteriorStyle.Pattern = InteriorPatternType.Solid;

style.InteriorStyle.Color = System.Drawing.Color.White;

}

#endregion

}

1 Answer, 1 is accepted

answered on 23 Apr 2013, 10:15 AM

Hi Gianluca,

I am afraid we are not able to reproduce this issue locally. A similar problem is discussed in the following thread:
http://www.telerik.com/community/forums/aspnet-ajax/grid/pager-not-working-properly-after-filtering-with-usealldatafields-true.aspx

You can try to disable the following property:

<MasterTableView ... EnableLinqGrouping="false">

If this does not help, please modify the attached web site in the provided forum thread to reproduce the issue and instruct us the exact steps, or open a support ticket and send us a runnable isolated version of your project.

Thank you for your cooperation in advance.

All the best,
Eyup
the Telerik team

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.