Grid Sever Export to export only filtered rows

[Richard]

Good afternoon,

I've been using the following demo to export a large dataset to Excel:

https://demos.telerik.com/aspnet-core/grid/server-export

However, unless you click the checkbox to select a row or all rows on a page, it exports all 830 rows in the demo data source regardless of whether any filters have been applied.  If you use the checkbox to select rows it will export one page i.e. 20 rows.

Ideally I want to export all the filtered rows in the grid and not require a checkbox to select them.  If I filter the Freight column as > 200 and then export, I only want to export the 73 items.

Is this possible?

Kind regards,

Richard

 

[Stoyan]

Hello Richard,

I am sending a complete example that demonstrates a similar implementation - how to pass, process and execute the filter expressions. Please find it attached to my response. Here are the highlights and steps:

1. Adding the custom serialization function of the data source and serializing the filter from the client-side:

        $("#export-filter").val(encodeURIComponent(serializeFilter(grid.dataSource.filter())));

 2. Parsing the filter into a FilterDescriptor (built-in class provided by Kendo UI):

 

public FileStreamResult ExportServer(string model, string data, string filter)
        {
            var columnsData = JsonConvert.DeserializeObject<IList<ExportColumnSettings>>(HttpUtility.UrlDecode(model));
            var filters = FilterDescriptorFactory.Create(filter);
         

3. Executing the filter expression over your data collection and take the data items only:

   var filteredData= students.ToDataSourceResult(new DataSourceRequest() { Filters=filters,Page=1}).Data;

Also, I want to add that the Kendo UI Grid is a client-side widget that is mainly used for visualizing and manipulating data. It has little to no correlation with the server-side logic. 

The export that we expose as built-in is on the client. Therefore, the grid is responsible for requesting all of the data and the creation of the file on the client. Of course, this comes with performance issues. That is why we have exposed functionality to handle the export on the server that the developer has to handle additionally. 

What essentially happens is that a button is included on the page (external for the grid). It submits a form with the options of the grid - the columns' configuration and data model. The handler on the server-side should request the data and create a File. The already built file is pushed to the client, again, externally from the grid.

I hope that you find those clarifications helpful.

Regards,
Stoyan
Progress Telerik

[Richard]

Hello Stoyan,

Many thanks for your answer - it's really useful and seems to be doing what I want.

Can I just check that I understand how it's working?

1. model is just the column header information - title etc.
2. data contains just the format and workbook title
3. filter contains just the filters applied to the columns

In the controller I needed to add a line between the filters and filteredData declarations in order to populate the list:

var filters = FilterDescriptorFactory.Create(filter);

// Fill model list
students = service.GetAll();

var filteredData = students.ToDataSourceResult(new DataSourceRequest() { Filters=filters,Page=1}).Data;

In this instance GetAll() is also used to populate the grid in the read action.

I've noticed that any dates in the resultant spreadsheet are appearing as numerals - do I need to handle them specifically in the controller?

Also, I've tried to apply the Export to CSV as in the demo.

filteredData.ToCsvStream(columnsData);

In the controller I'm getting an error:

FormatException: The header contains invalid values at index 0:

Is this because the first column of the grid is hidden?

Kind regards,

Richard

[Alexander]

Hi Richard,

In regards to your first question:

Indeed your assumptions are correct:

• The model retrieves the column header information for the grid.
• The data is used to retrieve both format and workbook title.
• The filter retrieves the currently applied filter to the respective.

In regards to your second question:

You can apply formatting for the specific date columns within the controller through the "NumberFormat" configuration. For example:

private void ChangeCellStyle(ExportCellStyle e)
{
    bool isHeader = e.Row == 0;
    SpreadCellFormat format = new SpreadCellFormat
    {
        ForeColor = isHeader ? SpreadThemableColor.FromRgb(50, 54, 58) : SpreadThemableColor.FromRgb(214, 214, 217),
        IsItalic = true,
        VerticalAlignment = SpreadVerticalAlignment.Center,
        WrapText = true,
        Fill = SpreadPatternFill.CreateSolidFill(isHeader ? new SpreadColor(93, 227, 0) : new SpreadColor(50, 54, 58))
    };
    if (e.Column == 2) //assert if it is the Date column
    {
        format.NumberFormat = "MM/dd/yyyy"; //apply the required formatting
    }
    e.Cell.SetFormat(format);
}

This will generate the following result within the Excel file:

In regards to the third question:

Could you please consider replicating the reported behavior within the previously attached sample with a Grid configuration similar to yours, and send it back for further investigation? This will help me get a better understanding of the issue you currently are encountering and provide further guidance.

[Richard]

Hi Alexander,

Many thanks for your reply.

I've managed to apply the logic you suggested for the date column - I wanted it to be more generic so I created the following, based on the fact that the date column would have the word "date" in its title:

public static List<int> dateCols = new List<int>();

in the ExportServer action:

var columnsData = JsonConvert.DeserializeObject<IList<ExportColumnSettings>>(HttpUtility.UrlDecode(model));
int colIndex = 0;
foreach (var col in columnsData)
{
    if (col.Field.Contains("date", StringComparison.OrdinalIgnoreCase))
    {
        dateCols.Add(colIndex);
    }
    colIndex++;
}

in the ChangeCellStyle method:

if (dateCols.Contains(e.Column)) //assert if it is the Date column
{
    format.NumberFormat = "dd/MM/yyyy HH:mm:ss";
}

With regards to the third question and the error I was getting with Export to CSV, I managed to track that down to the mime type not being set correctly.

Kind regards,

Richard

 

[Alexander]

Thank you for sharing your solution with the community it is greatly appreciated. Could you please confirm whether the encountered issue has been resolved on your end or not?

[Richard]

Hi Alexander - the answer that Stoyan submitted, plus my code to handle the date format, has resolved my question.

Many thanks,

Richard