1. Home
  2. Forum
  3. Angular - EJ 2
  4. Replace display of value in Excel Filtering in Grid.

Replace display of value in Excel Filtering in Grid.

Hi, Syncfusion! I am using eg-grid with value accessor (replacing display of some columns from id to alias). Is it possible to do the same in the Excel filtering (display value should be alias, value on chosen should be id)? Data (id, alias) stored locally in local storage. 






11 Replies
LR Logesh Rajappa Syncfusion Team July 6, 2018 12:48 PM UTC

Hi Eugene, 

Thanks for contacting Syncfusion Support. 

Query: “Replace display of value in Excel Filtering in Grid” 

We have validated your query and we suspect that do you want to show the text value instead of ID in Excel checkbox filtering. If so, we have achieved your requirement using filter property. Initially grid is rendered with key value field. By using valueAccessor property, display values are changed from key to value. To change the filter values from key to value, we have used itemTemplate property.  Using getValue helper function to get key replaced with the field value in the Excel filter. Please refer to the below sample for your reference. 

Code Example: 

[app.component.ts] 
@Component({ 
    selector: 'app-container', 
    template: `<ejs-grid #grid [dataSource]='data' [height]='315' allowFiltering='true' [filterSettings]='filterSettings'> 
                    <e-columns> 
                        ... 
                        <e-column field='Name.key' headerText='Name' textAlign='Right' width=130 [valueAccessor]='changeValue'  [filter]='filter'></e-column> 
                        ... 
                    </e-columns> 
                </ejs-grid>`, 
                 providers:  [SortService, GroupService] 
}) 
export class AppComponent implements OnInit { 
    public data: Object[]; 
... 
    public changeValue = (field: number, data: Object, column: Object) => { 
      let fieldName: string[] = field.split('.'); 
       return data[fieldName[0]].value; 
    }  

    ngOnInit(): void { 
        this.data = data; 
        this.filterSettings = { type: 'Excel' }; 
        this.filter = { itemTemplate: '#temp'}; 
    } 
} 

[index.html] 
<body> 
... 
<script id='temp' type='text/x-template'> ${getValue(data)}</script> 
    <script> 
      function getValue(data){ 
         
        return data.Name !== undefined ? data.Name.value : "Select All"; 
      } 
    </script> 
... 
</body> 


Please get back to us for further assistance. 

Regards, 
Logesh Rajappa

EV Eugene Voronovich July 7, 2018 07:39 AM UTC

Hi Logesh, thank you for your answer. Actually it's wotking, but the issue is that I don't have the display value in the object, that I am getting from the server (I have only the id). The display value stores locally in local storage, I need to replace id to alias. I have the special StaticService, that have some methods that accepts id and returns the alias. Using value accessor I can easily use this service.
In the solution that you have provided, the function getValue(data) stores in index.html, so I don't have my StaticService in it (I have only the local storage, I can do what I want, but I need to rewrite all StaticService methods in <script> tag. The question is: can I somehow use my angular services to replace display value in excel filters?
My Static Service
@Injectable()
export class StaticBundleService {
  readonly STATIC_BUNDLE = 'static_bundle';
  private staticBundle: StaticBundleModel = JSON.parse(localStorage.getItem(this.STATIC_BUNDLE));

  constructor() {
  }

  isStaticBundle() {
    return !!this.staticBundle;
  }

  setStaticBundle(staticBundle: StaticBundleModel) {
    localStorage.setItem(this.STATIC_BUNDLE, JSON.stringify(staticBundle));
  }

  getStaticBundle() {
    return this.staticBundle;
  }

  getAlias(field: string, value: any) {
    switch (field) {
      case 'Category':
        return this.getCategoryAlias(value);
      case 'ManufacturerId':
        return this.getManufacturerAlias(value);
      case 'SupplierId':
        return this.getSupplierAlias(value);
      default:
        return null;
    }
  }

  private getCategoryAlias(id: number): string {
    return this.getCategoryTableEntityValues()
      .find(cat => cat.id === id)
      .alias;
  }

  private getManufacturerAlias(id: number): string {
    return this.getManufacturerTableEntityValues()
      .find(man => man.id === id)
      .alias;
  }

  private getSupplierAlias(id: number): string {
    return this.getSupplierTableEntityValues()
      .find(sup => sup.id === id)
      .alias;
  }


  private getCategoryTableEntityValues() {
    return this.staticBundle.staticBundleEntities
      .find(sbe => sbe.table === CATEGORY_TABLE).entitiesValues;
  }

  private getManufacturerTableEntityValues() {
    return this.staticBundle.staticBundleEntities
      .find(sbe => sbe.table === MANUFACTURER_TABLE).entitiesValues;
  }

  private getSupplierTableEntityValues() {
    return this.staticBundle.staticBundleEntities
      .find(sbe => sbe.table === SUPPLIER_TABLE).entitiesValues;
  }



LR Logesh Rajappa Syncfusion Team July 11, 2018 04:04 AM UTC

Hi Eugene, 

Sorry for the inconvenience caused. 

Query: “can I somehow use my angular services to replace display value in excel filters” 

Currently we don’t have support to define the filter itemTemplate using ng-template(Angular way).We have considered this “Provide support to define a itemTemplate using ng-template compiler to display the customize value in excel filter “as a bug and the fix will be available on July 18th 2018 patch release. 

We appreciate your patience until then. 


Regards, 
Logesh Rajappa

EV Eugene Voronovich July 11, 2018 06:25 AM UTC

Thank you for answer, will wait for it.

LR Logesh Rajappa Syncfusion Team July 11, 2018 12:52 PM UTC

Hi Eugene, 

Thanks for the update. 

Fix for “Provide support to define a itemTemplate using ng-template compiler to display the customize value in excel filter “ will be available on our July 18, 2018 patch release as promised. 
 
Regards, 
Logesh Rajappa 


AT Aman Thapar December 5, 2019 04:21 PM UTC

Replace display of value in Excel Filtering in Grid

How to do this with React Grid?



PS Pavithra Subramaniyam Syncfusion Team December 6, 2019 11:57 AM UTC

Hi Aman, 

You can achieve your requirement by using the Item Template feature of Grid filtering. For that you can use the column property filter and return the value as you want inside the html element in the  `itemTemplate` . Please refer the below code and sample for information. 

 


import { render } from 'react-dom'; 
import './index.css'; 
import * as React from 'react'; 
import { DropDownListComponent } from '@syncfusion/ej2-react-dropdowns'; 
import { GridComponent, ColumnsDirective, ColumnDirective, Page, Filter, Inject } from '@syncfusion/ej2-react-grids'; 
import { orderDataSource } from './data'; 
import { SampleBase } from './sample-base'; 
export class FilterMenu extends SampleBase { 
    constructor() { 
        super(...arguments); 
         
        this.filterSettings = { type: 'Excel' }; 
        this.fields = { text: 'text', value: 'value' }; 
    } 
     valueAccess(field, data, column){ 

      return data[field] + 678 ;       // here you can customize your data 
  } 
     filterValue  = { 
    type: 'CheckBox', 
    itemTemplate: (e)=>{ 

      return   <span>{e.data[e.column.field] +'678'}</span>     // here you can return your customized data 
    } 
  }   

    render() { 
        return (<div className='control-pane'> 
                <div className='control-section row'> 
                    <GridComponent dataSource={orderDataSource} allowPaging={true} ref={grid => this.gridInstance = grid} pageSettings={{ pageSize: 10, pageCount: 5 }} allowFiltering={true} filterSettings={this.filterSettings}> 
                        <ColumnsDirective> 
                            <ColumnDirective field='OrderID' headerText='Order ID' width='120' textAlign='Right'></ColumnDirective> 
                            <ColumnDirective field='ShipCountry' headerText='Ship Country' width='150'  filter={this.filterValue} valueAccessor={this.valueAccess}></ColumnDirective> 
                            <ColumnDirective field='CustomerName' headerText='Customer Name' width='150'></ColumnDirective> 
                       </ColumnsDirective> 
                        <Inject services={[Filter, Page]}/> 
                    </GridComponent> 
                </div> 

            </div>); 
    } 
} 

Please get back to us if you need further assistance. 
 
Regards, 
Pavithra S. 


MS Mike Schall February 10, 2020 03:16 PM UTC

This still doesn't seem to solve the search functionality of the Excel Filtering UI.  You still can only search by the value bound (ID).  Shouldn't all the values in the Filtering UI be sourced from the valueAccessor?

BS Balaji Sekar Syncfusion Team February 11, 2020 04:37 PM UTC

  
Hi Mike, 
 
Thanks for your update. 
 
We have validated your query and by default the valueAccessor is used to access/manipulate the value of the displayed data. By using this valueAccessor , we can customize the display value. And the Grid actions(such as sorting, filtering, etc) are done based on the fields in the dataSource. This is the default behavior. 
 
Please get back to us if you need further assistance. 
 
Regards, 
Balaji Sekar. 


MS Mike Schall April 14, 2020 01:15 PM UTC

There should be an option to have Grid Actions (search and filter) use what is displayed in the column.   That is what user is seeing and would want to search/filter by.  For example, the user doesn't know the enum numbers or what they mean.  They would want to search by what they see in the column.

Please add an option to allow grid actions to use the result of the value accessor.

BS Balaji Sekar Syncfusion Team April 15, 2020 11:03 AM UTC

Hi Mike, 

By default, we have performed grid actions such as searching, filtering etc based on the grid dataSource. While using valueAccessor in column which helps to show the custom value in grid and  Grid valueAccessor property is only to change the particular column cells text and it does not affect the Grid data source. 
we could not able to perform sorting, filtering action based on valueAccessor in grid. 

Regards, 
Balaji Sekar 


Loader.
Up arrow icon