We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Excel Filter in Grid

Dear Syncfusion,
I am implementing Grid with Excel Filter, and I have some troubles listed below:
1. I have 100.000+ data, in my experience, Excel Filter will get the whole data in order to know what is the options of the specific column... Is it possible to let Excel Filter know the options so it won't query the whole data?
2. I am aware that it has built in TextFilter and NumberFilter, does it also have Date Filter, to filter specific range of date?

Thank you.

3 Replies

PK Prasanna Kumar Viswanathan Syncfusion Team July 18, 2017 11:44 AM UTC

Hi Sugandi, 

Thanks for contacting Syncfusion support. 

       Queries 
                                                  Response 

I have 100.000+ data, in my experience, Excel Filter will get the whole data in order to know what is the options of the specific column... Is it possible to let Excel Filter know the options so it won't query the whole data?” 


If you do not need to display the whole data in excel filter, use maxFilterChoices API of filterSettings. In the attached sample we mentioned 200 in maxFilterChoices so it shows 200 data in excel filer instead of whole data.  

Find the code example:  


$("#Grid").ejGrid({ 
 
           dataSource : ej.DataManager({ 
               url: "/Grid/DataSource", 
               adaptor : "UrlAdaptor" 
           }), 
              allowPaging: true, 
              isResponsive: true, 
              allowFiltering: true, 
              filterSettings: { filterType: "excel", maxFilterChoices: 200 }, 
              columns: [ 
                ----------------------- 
                 ] 
          }); 

Refer to the Help document for maxFilterChoices. 



I am aware that it has built in TextFilter and NumberFilter, does it also have Date Filter, to filter specific range of date?” 


Yes, we have built in DateFilter for Date column to filter the specific range of date.  

Find the screenshot:  

 



Regards, 
Prasanna Kumar N.S.V 
 



OS Oka Sugandi July 20, 2017 04:47 AM UTC

Thank you Prasanna for the help.

I am still struggling to cope with the problem...

1. About option of Excel Filter. If I specifiy a number in maxFilterChoices, let say 1,000. The options is only shown if exists in the first 1,000 data... I have 100,000+ data, and in a particular column (let say 'Stage'), the first (let say) 10,000 data has same value 'Init', in fact the stage option has 5 value. I am unable to make Excel Filter show the 5 option.

2. I am unable to make Excel Filter show "Date Filter". It always show "Text Filter" although the data has been formatted as standard date... Any additional method to make it happen?


Thank you very much for the help provided.



PK Prasanna Kumar Viswanathan Syncfusion Team July 21, 2017 06:53 PM UTC

Hi Sugandi, 

          Queries 
                                                  Response 

I have 100,000+ data, and in a particular column (let say 'Stage'), the first (let say) 10,000 data has same value 'Init', in fact the stage option has 5 value. I am unable to make Excel Filter show the 5 option.” 


In excel filter it will show only unique values even if we bind 50000 records in grid. So, if you have same value for 100,000 records then it will display only one record in excel filter dialog. By default the excel filter will show only 1000 records if we bind 100,000 records in Grid  
 
If you need to view other data use search option in excel filter. In search box type the particular value and click search icon we will get the other data in excel filter.  


I am unable to make Excel Filter show "Date Filter". It always show "Text Filter" although the data has been formatted as standard date... Any additional method to make it happen?” 


We suspect that you have render the string value in Grid for the date column. So, we suggest you to mention the type as date for that particular column.  

Find the code example:  

columns: [ 
                ----- 
                { field: "OrderDate", headerText: "Order Date", width: 80, format: "{0:dd/MM/yyyy}", type : "date", textAlign: ej.TextAlign.Right }, 
                 
                 ] 


If you still face the same issue. Please share the following details. 

1. Code example of a Grid.  

2. Share the first record of data that you have bounded to the Grid. 

3. Essential Studio Version details. 

4. If possible, replicate the issue in the attached sample.    


Regards, 
Prasanna Kumar N.S.V 
 


Loader.
Live Chat Icon For mobile
Up arrow icon