Issue with paging when exporting grid data to Excel using GenerateXlsxOutput

[Rolf Falnes]

Hi.

We're having problems using GenerateXlsxOutput and ExportSettings.IgnorePaging.
When ExportSettings.IgnorePaging is true the grid's paging disappears after exporting.

We're using Asp.Net Ajax version 2021.3.1111.45.

See attached zipfile for a simple example causing this issue.

I assume this is a bug?

Regards, Rolf F.

[Attila Antal]

Hi Rolf,

Thank you for reporting the problem.

This is indeed a Bug and we have logged it into our system. You can visit the official Bug item where I also shared a Workaround that you can try: https://feedback.telerik.com/aspnet-ajax/1548078-generateexportoutput-method-changes-the-grid-structure

As a token of gratitude for reporting the issue, I have awarded you with Telerik points. To see the points and to learn more about them, Login to your Telerik Profile and navigate to Telerik Points at https://www.telerik.com/account/telerik-points

Please excuse us for any inconvenience this may have caused!

Regards,
Attila Antal
Progress Telerik

[Rolf Falnes]

Sorry, added my reply in the wrong place, here it is again:

Hi Antilla and thank you for your reply and workaround.

I tested the workaround and were able to save the Excel output server side without the paging disappearing.
However, an Excel file is also pushed to the client side; is there a way to cancel this push to client side?

Regards, Rolf F.

[Attila Antal]

Rolf,

That is because the Grid was instructed to Export regardless of what you do it will try to complete the action unless you cancel the command, see https://docs.telerik.com/devtools/aspnet-ajax/controls/grid/server-side-programming/events/itemcommand

For example:

Protected Sub RadGrid1_ItemCommand(ByVal sender As Object, ByVal e As GridCommandEventArgs)
    Dim grid = CType(sender, RadGrid)

    If e.CommandName = RadGrid.ExportToExcelCommandName Then
        e.Canceled = true 'Cancel the Grid command so it will not complete the action
        Dim exportOutput = TryCast(grid.MasterTableView.GenerateXlsxOutput(Of xlsx.Workbook)(), xlsx.Workbook)
        ' Do something with output here.
    End If
End Sub

[Rolf Falnes]

I tried that, but then the paging disappears again...

[Rolf Falnes]

Hello again.

Did you see my last comment above?

[Attila Antal]

Hi Rolf,

Yes, we have seen your message and we were investigating this issue. After looking into the source code, we found that this method was created strictly for exporting, therefore, cannot be used if not exporting anything.

Please my latest answer: https://www.telerik.com/forums/issue-with-paging-when-exporting-grid-data-to-excel-using-generatexlsxoutput#5424031

 

[Rolf Falnes]

Hi Antilla and thank you for your reply and workaround.

I tested the workaround and were able to save the Excel output server side without the paging disappearing.
However, an Excel file is also pushed to the client side; is there a way to cancel this push to client side?

Regards, Rolf F.

[Attila Antal]

Hi Rolf,

Apparently the RadGrid.MasterTableView.GenerateXlsxOutput() method was designed for Exporting. During the export, the headers are cleared and the Excel File is returned back to the client. The method makes irreversible changes to the RadGrid structure, and if the Headers are not clear, the broken structure will appear on the Page.

Until the issue is fixed, you can build your own Workbook object.

Here is an example

Required Assemblies

Mandatory for Telerik Component with "Default" Skin

• Telerik.Web.UI.dll

Mandatory for Telerik Components if using Skins other than "Default"

• Telerik.Web.UI.Skins.dll

Mandatory for Exporting to Office Open XML (XLSX)

• Telerik.Windows.Documents.Core.dll
• Telerik.Windows.Documents.Spreadsheet.dll
• Telerik.Windows.Zip.dll
• Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.dll

 

Using/Imports statements

C#

using System;
using System.Data;
using System.IO;
using System.Linq;
using System.Web.UI.WebControls;
using Telerik.Web.UI;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
using xlsx = Telerik.Windows.Documents.Spreadsheet.Model;

 

VB

Imports System
Imports System.Data
Imports System.IO
Imports System.Linq
Imports System.Web.UI.WebControls
Imports Telerik.Web.UI
Imports Telerik.Windows.Documents.Spreadsheet.FormatProviders
Imports Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx
Imports xlsx = Telerik.Windows.Documents.Spreadsheet.Model

 

GenerateXlsxOutput function

Create a Custom method that will generate a Workbook object and fill it with data based on the Grid's settings. More details on the APIs can be found in the Telerik Document Processing Library (DPL) documentation: https://docs.telerik.com/devtools/document-processing/introduction

C#

private xlsx.Workbook GenerateXlsxOutput(RadGrid grid)
{
    var fileName = grid.ExportSettings.FileName.Replace(".xlsx", string.Empty);
    var sheetName = !string.IsNullOrEmpty(grid.ExportSettings.Excel.WorksheetName) ? grid.ExportSettings.Excel.WorksheetName : "Sheet1";
    var workbook = new xlsx.Workbook();
    workbook.Name = fileName;

    var worksheet = workbook.Worksheets.Add();
    worksheet.Name = sheetName;

    var gridColumns = grid.MasterTableView.RenderColumns.Where(col => !(col is GridRowIndicatorColumn) && !(col is GridGroupSplitterColumn) && !(col is GridExpandColumn) && col.Visible && col.Display).ToList();

    if (grid.MasterTableView.ShowHeader)
    {
        for (int colIndex = 0; colIndex < gridColumns.Count; colIndex++)
        {
            var column = gridColumns[colIndex];

            var columName = string.IsNullOrEmpty(column.HeaderText) ? column.UniqueName : column.HeaderText;
            worksheet.Cells[0, colIndex].SetValue(columName);
        }
    }

    bool ShouldEnablePaging = false;

    if (grid.ExportSettings.IgnorePaging)
    {
        grid.AllowPaging = false;
        grid.Rebind();
        ShouldEnablePaging = true;
    }

    int headerOffset = grid.ShowHeader ? 1 : 0;

    for (int i = 0; i < grid.Items.Count; i++)
    {
        var rowIndex = i + headerOffset;

        var item = grid.Items[i];

        for (int colIndex = 0; colIndex < gridColumns.Count; colIndex++)
        {
            worksheet.Cells[rowIndex, colIndex].SetValue(item[gridColumns[colIndex].UniqueName].Text);
        }
    }

    if (ShouldEnablePaging)
    {
        grid.AllowPaging = true;
        grid.Rebind();
    }
    return workbook;
}

 

VB

Private Function GenerateXlsxOutput(ByVal grid As RadGrid) As xlsx.Workbook
    Dim fileName = grid.ExportSettings.FileName.Replace(".xlsx", String.Empty)
    Dim sheetName = If(Not String.IsNullOrEmpty(grid.ExportSettings.Excel.WorksheetName), grid.ExportSettings.Excel.WorksheetName, "Sheet1")
    Dim workbook = New xlsx.Workbook()
    workbook.Name = fileName
    Dim worksheet = workbook.Worksheets.Add()
    worksheet.Name = sheetName
    Dim gridColumns = grid.MasterTableView.RenderColumns.Where(Function(col) Not (TypeOf col Is GridRowIndicatorColumn) AndAlso Not (TypeOf col Is GridGroupSplitterColumn) AndAlso Not (TypeOf col Is GridExpandColumn) AndAlso col.Visible AndAlso col.Display).ToList()

    If grid.MasterTableView.ShowHeader Then

        For colIndex As Integer = 0 To gridColumns.Count - 1
            Dim column = gridColumns(colIndex)
            Dim columName = If(String.IsNullOrEmpty(column.HeaderText), column.UniqueName, column.HeaderText)
            worksheet.Cells(0, colIndex).SetValue(columName)
        Next
    End If

    Dim ShouldEnablePaging As Boolean = False

    If grid.ExportSettings.IgnorePaging Then
        grid.AllowPaging = False
        grid.Rebind()
        ShouldEnablePaging = True
    End If

    Dim headerOffset As Integer = If(grid.ShowHeader, 1, 0)

    For i As Integer = 0 To grid.Items.Count - 1
        Dim rowIndex = i + headerOffset
        Dim item = grid.Items(i)

        For colIndex As Integer = 0 To gridColumns.Count - 1
            worksheet.Cells(rowIndex, colIndex).SetValue(item(gridColumns(colIndex).UniqueName).Text)
        Next
    Next

    If ShouldEnablePaging Then
        grid.AllowPaging = True
        grid.Rebind()
    End If

    Return workbook
End Function

 

Usage

When the Button is clicked get the Workbook object of the Grid

C#

protected void RadButton1_Click(object sender, EventArgs e)
{
    xlsx.Workbook workbook = GenerateXlsxOutput(RadGrid1);
}

 

VB

Protected Sub RadButton1_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim workbook As xlsx.Workbook = GenerateXlsxOutput(RadGrid1)
End Sub

 

Note: This example does not create a Hierarchical structure, however, it can be implemented similar to the example from Export RadGrid with hierarchy and grouping to Excel and Word: https://www.telerik.com/support/code-library/grid-with-hierachy-and-grouping-export-to-excel?_ga=2.141718065.2057753475.1641195931-732519942.1634562903

 

Regards,
Attila Antal
Progress Telerik

[Rolf Falnes]

Thanks again Attila.

Seems to work for now as a workaround.

When approximately do you expect that the bug will be fixed?

Regards, Rolf

 

[Attila Antal]

Hi Rolf,

I'm afraid I can't provide an ETA for the fix. Our developers currently have their Road Map planned with issues that have been reported before this.

You can follow the item and if we start working on it we will mention the ETA and you will get a notification about that.

[Rolf Falnes]

Ok.

I also noticed another smaller bug when I found the one above:
Too many cells in the first row containing the title in the Excel file were merged. Instead of the number of columns visible in the grid being merged, it seemed all columns regardless of visible or not were merged.
And when I tried to use the CellSelection.Unmerge function to remove the merging, it didn't work.

Hopefully they can fix that also when they're going to fix the bug above.

Regards, Rolf.

[Attila Antal]

Hi Rolf,

An issue can only be considered as a Bug once we identified it. Please open a separate Forum thread or Submit a Bug Report directly and share all the details (Grid configuration and steps to replicate the issue). Once we have enough details to replicate the problem, we will be able to determine whether it's a bug.