[Solved] Export to Excel and Virtualization

[Francisco Amador]

Is it possible to export the entire dataset to excel (5k records) if the grid has virtualization turned on? Currently any attempt I make just outputs a max number of records equal to the ItemsPerView property. I wanted to virtualize it since loading the whole dataset was making the page be over 8MB. By virtualizing it I was able to bring it down below 1MB.

If I simply enable paging without virtualization and a page size of 500, then the size is also around 1MB and the export works for all records. However, I wanted to see if the virtualization allowed it to work since I liked the way the scrolling and automatic loading worked. This is my current setup:

<telerik:RadGrid ID="RadGridActive" runat="server" OnNeedDataSource="RadGridActive_OnNeedDataSource" AllowFilteringByColumn="True" AllowSorting="True" AutoGenerateColumns="False"
    AllowMultiRowSelection="True" AllowAutomaticInserts="False" AllowAutomaticDeletes="False" AllowAutomaticUpdates="False" AllowPaging="True" PageSize="1000"
    OnExportCellFormatting="RadGridActive_OnExportCellFormatting">
    <ExportSettings HideStructureColumns="True" ExportOnlyData="True"></ExportSettings>
    <MasterTableView>
        <Columns>
            <telerik:GridClientSelectColumn UniqueName="ClientSelectColumn">
            </telerik:GridClientSelectColumn>
        </Columns>
    </MasterTableView>
    <ClientSettings EnableRowHoverStyle="true" AllowColumnsReorder="True" ReorderColumnsOnClient="True">
        <Virtualization EnableVirtualization="True" InitiallyCachedItemsCount="1000" ItemsPerView="200" LoadingPanelID="RadAjaxLoadingPanel1"></Virtualization>
        <Selecting AllowRowSelect="True" EnableDragToSelectRows="False"></Selecting>
        <Scrolling AllowScroll="True" ScrollHeight="500" UseStaticHeaders="True"></Scrolling>
    </ClientSettings>
    <PagerStyle Mode="NextPrevNumericAndAdvanced"></PagerStyle>
</telerik:RadGrid>

And my exporting logic

public static void ExportGridToExcel(RadGrid grid)
{
    //remove selected checkbox column if there
    if (grid.MasterTableView.Columns[0].UniqueName == "ClientSelectColumn") grid.MasterTableView.Columns[0].Visible = false;
    if (grid.MasterTableView.Columns[0].UniqueName == "EditButton") grid.MasterTableView.Columns[0].Visible = false;

    //if rows selected, only export selected
    if (grid.SelectedItems.Count != 0)
    {
        foreach (GridDataItem item in grid.MasterTableView.Items)
        {
            if (!item.Selected)
                item.Visible = false;
        }
    }

    grid.ExportSettings.IgnorePaging = true;
    grid.ExportSettings.OpenInNewWindow = true;
    grid.ExportSettings.FileName = grid.ID;
    grid.ExportSettings.Excel.Format = GridExcelExportFormat.Html;
    grid.MasterTableView.ExportToExcel();
}

[Princy]

Hi Francisco Amador,

You can try setting ItemsPerView as the total count of rows in the Export. For example of 10000 rows to be exported,the code can be as follows:

C#:

//Your Code
RadGrid1.ClientSettings.Virtualization.ItemsPerView = 10000;
RadGrid1.ExportSettings.IgnorePaging = true;
RadGrid1.ExportSettings.OpenInNewWindow = true;
RadGrid1.ExportSettings.FileName = RadGrid1.ID;
RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Html;
RadGrid1.MasterTableView.ExportToExcel();

Thanks,
Princy