Question when export radzen data grid to excel it export all rows but i need filtered rows only export to excel?

[ahmedaziz]

I work on asp.net core blazer server side . I face issue I can't export radzen data grid to excel after filter data on it .

so when page load list of Data source of radzen data grid it display 10 records

after that I make filter to any column on radzen data grid then it display 4 records so

when press button export to excel it export 10 rows meaning it export radzen data grid

before filter .

but correct it must export 4 rows to excel .

so How to export radzen data grid to excel after filter ridzen data grid ?

private async Task ExportToExcel()
    {
         ExceldatabaseData = databaseData.Select(p => new CopyExcelDatabaseClass
            {
                DBID = p.dbid,
                DBName = p.databaseName,
                ServerID = p.serverID,
                ServerName = p.serverName,
                Severity = p.serverity,
                BackupProcedure = p.backUpProcedure,
                Remarks = p.remarks,
                OwnerFileNo = p.ownerfilenumber,
                OwnerFileName = p.ownerfilename,
                IsActive = p.isActive

            }).ToList();
        ExcelPackage.LicenseContext = LicenseContext.Commercial;
        var stream = new MemoryStream();
        using (var package = new ExcelPackage(stream))
        {
            var worksheet = package.Workbook.Worksheets.Add("DatabaseList");
            //worksheet.Cells.LoadFromCollection(databaseData, true);
            worksheet.Cells.LoadFromCollection(ExceldatabaseData, true);
            package.Save();
        }

        var buffer = stream.ToArray();
        var fileName = $"Database_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
        await JS.InvokeAsync<object>("saveAsFile", fileName, Convert.ToBase64String(buffer));
    }
java script function
window.saveAsFile = (filename, bytesBase64) => {
    const link = document.createElement('a');
    link.download = filename;
    link.href = 'data:application/octet-stream;base64,' + bytesBase64;
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
};

  public class CopyExcelDatabaseClass
    {
        [DisplayName("DB ID")]
        public int DBID { get; set; }

        [DisplayName("DB Name")]
        public string DBName { get; set; }

        [DisplayName("Server ID")]
        public int ServerID { get; set; }
        
        
        [DisplayName("Server Name")]
        public string ServerName { get; set; }
        
        public string Severity { get; set; }
        [DisplayName("Backup Procedure")]
        public string BackupProcedure { get; set; }
        public string Remarks { get; set; }

        [DisplayName("Owner File No")]
        public string OwnerFileNo { get; set; }
        [DisplayName("Owner File Name")]
        public string OwnerFileName { get; set; }

        
        [DisplayName("Is Active")]
        public bool IsActive { get; set; }

    }
private IEnumerable<DatabaseClass> databaseData = Array.Empty<DatabaseClass>();
 private async Task RefreshList()
    {
        var request = new HttpRequestMessage(HttpMethod.Get, config["API_URL"] + "Database/GetAllDatabaseDetails");
        var client = ClientFactory.CreateClient();
        var response = await client.SendAsync(request);
        using var responsestream = await response.Content.ReadAsStreamAsync();
        databaseData = await JsonSerializer.DeserializeAsync<List<DatabaseClass>>(responsestream);


    }
 protected override async Task OnInitializedAsync()
    {
        await RefreshList();
    }

 

[Skydiver]

It's not clear in the code you presented above how the filter gets applied to databaseData. As it stands now, it looks like everything in databaseData is exported to Excel.

 

[ahmedaziz]

ok i will give filter applied but please help me

 [HttpGet]
        [Route("GetAllDatabaseDetails")]
        public IActionResult GetAllDatabaseDetails()
        {
            string query = "";


                        query = "select s.[DBID], s.[DB_Name] as DatabaseName,s.serverID,st.Server_Name as  ServerName,s.ServerityId,isnull(serverity.DetailsName,'') as Serverity,s.EnvironmentId,isnull(environments.DetailsName,'') as environment,s.BackUpProcedureId,isnull(backupproc.DetailsName,'') as BackUpProcedure,isnull(s.DRRequired,0) as DRRequiredID,isnull(drrequired.DetailsName,'') as DRRequired,isnull(s.Remarks,'') as Remarks,isnull(s.DB_Owner_FileNo,'') as ownerfilenumber,isnull(s.DB_Owner_FileName,'') as ownerfilename, s.IsActive from [dbo].[DataBase] s with(nolock)\r\ninner join [dbo].[ServerNames] st with(nolock) on st.ServerID=s.ServerID\r\nleft join  Details serverity with(nolock) on serverity.ID=s.ServerityId and serverity.HeaderId=14\r\nleft join  Details environments with(nolock) on environments.ID=s.EnvironmentId and environments.HeaderId=15\r\nleft join  Details backupproc with(nolock) on backupproc.ID=s.BackUpProcedureId and backupproc.HeaderId=18\r\nleft join  Details drrequired with(nolock) on drrequired.ID=s.DRRequired and drrequired.HeaderId=19";

            try
            {
                ICollection<object> ApplicationsDataValues = new List<object>();
                using (var command = _context.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = query;
                    command.CommandType = CommandType.Text;

                    _context.Database.OpenConnection();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ApplicationsDataValues.Add(new
                            {
                                                                DBID = reader.GetFieldValue<int>(0),

                                DatabaseName = reader.GetFieldValue<string>(1).ToString(),
                                serverID = reader.GetFieldValue<Int32>(2),
                                ServerName = reader.GetFieldValue<string>(3).ToString(),
                                                                ServerityId = reader.GetFieldValue<Int32>(4),

                                                                Serverity = reader.GetFieldValue<string>(5).ToString(),
                                EnvironmentId = reader.GetFieldValue<Int32>(6),

                                environment = reader.GetFieldValue<string>(7).ToString(),
                                BackUpProcedureId = reader.GetFieldValue<Int32>(8),

                                BackUpProcedure = reader.GetFieldValue<string>(9).ToString(),



                                DRRequiredID = reader.GetFieldValue<string>(10).ToString(),
                                DRRequired = reader.GetFieldValue<string>(11).ToString(),

                                Remarks = reader.GetFieldValue<string>(12).ToString(),
                                ownerfilenumber = reader.GetFieldValue<string>(13).ToString(),
                                ownerfilename = reader.GetFieldValue<string>(14).ToString(),
                                IsActive = reader.GetFieldValue<bool>(15)//.ToString()



                            });
                        }
                    }
                }
                return StatusCode(200, ApplicationsDataValues); // Get all users   
            }
            catch (Exception e)
            {
                return StatusCode(500, e);
            }
            //var appList = _context.Database.ExecuteSql($"select ApplicationsData.ApplicationID as ApplicationId,Application_Name as ApplicationName,CommonName,d.DetailsName as TypeOfApplication,AccessType,d.DetailsName Criticality,o.OwnerName as ApplicationOwner,DRRequired,se.[DB_Name] as [DataBase] from ApplicationsData left join [dbo].[Details] d with(nolock) on d.ID=ApplicationsData.ApplicationType and d.HeaderId=6 left join [dbo].[Details] d2 with(nolock) on d2.ID=ApplicationsData.Criticality and d2.HeaderId=7\r\nleft join dbo.[Owner] o with(nolock) on o.ApplicationId=ApplicationsData.ApplicationID\r\nleft join dbo.[DataBase] se with(nolock) on se.ServerID=ApplicationsData.ServerId where ApplicationsData.ServerId={serverID} OR SE.[DBID]={databaseId} OR o.OwnerId={ownerId}").ToList();

            //return Ok(appList);
        }

 

[cjard]

That code doesn't do any filtering either

Presumably you set (or the user sets) some property on your grid that performs a filter. Pull the rows out of the grid (if it supports it) or use the same filter yourself on the original collection to get a filtered collection, and export that

If you're going to ISNULL all your Nullable columns to be empty space, why not just make them not nullable and insert space to start with? It'd make your code crash less for sure

There's a way to download in BSS that doesn't involve creating, clicking and removing a link - I haven't got the code to have but I'll post it shortly

 

[ahmedaziz]

can you please help me i don't know how to solve this issue please

 

[Skydiver]

Change your line 3 in post #1 from:

ExceldatabaseData = databaseData.Select(p => new CopyExcelDatabaseClass

to

ExceldatabaseData = databaseData
    .Where(p => /* your filter code goes here */ )
    .Select(p => new CopyExcelDatabaseClass

 

[Skydiver]

Or looking at the Radzen documentation, it looks like the View property of the data grid returns what is currently visible in the view:

DataGrid (Blazor)

DataGrid component properties and events.

www.radzen.com

But one wonders how that will work if paging is enabled.