How to get grid display numbers without grouping and same format to be exported to excel in Angular Grid

[Sandip Kumar]

Hi,

I have set the format of column as 'N2'.

In my grid numbers show with grouping as "123,456,123" and during excel export as "123456123".

How to get grid display numbers without grouping i.e. "123456123" and same format to be exported to excel.

[Prasanna Kumar Viswanathan]

Hi Sandip, 

 

Greetings from Syncfusion. 

 

Query: How to get grid display numbers without grouping i.e. "123456123" and same format to be exported to excel. 

 

We have validated your query and you want to display the number column as without formatting in Grid and also while exporting. You can achieve your requirement by using queryCellInfo, excelQueryCellInfo events. Find the below code snippets and sample for your reference. 

 

<div class="control-section"> 

    <ejs-grid #grid [dataSource]='data' allowPaging='true' [pageSettings]='pageSettings' [toolbar]='toolbar' (toolbarClick)='toolbarClick($event)' 

                [allowExcelExport]='true' [allowPdfExport]='true' (queryCellInfo)='queryCellInfo($event)' (excelQueryCellInfo)='excelQueryCellInfo($event)' (beforeExcelExport)='beforeExcelExport($event)' (excelExportComplete)='excelExportComplete($event)'> 

        <e-columns> 

            <e-column field='OrderID' headerText='Order ID' width='120' textAlign='Right' isPrimaryKey='true'></e-column> 

            <e-column field='OrderDate' headerText='Order Date' width='130' format="yMd" textAlign='Right'></e-column> 

            <e-column field='CustomerID' headerText='Customer ID' width='120'></e-column> 

            <e-column field='Freight' headerText='Freight' [allowGrouping]="false" width='120' format='N2' textAlign='Right'></e-column>             

            <e-column field='ShipCountry' headerText='Ship Country' width='150'></e-column> 

        </e-columns> 

    </ejs-grid> 

</div> 

 

. . . 

export class AppComponent { 

    . . . 

 

    queryCellInfo(args: any): void { 

      if(args.column.field === 'Freight') {    //checked column 

        var arr = args.cell.innerHTML.split('.');    //split using '.' 

        var val =""; 

        for(var i: number = 0; i < arr.length; i++) { 

          val += arr[i];      //concatenate the values 

        } 

        var lVal = Number(val);    //convert to number 

        args.cell.innerHTML = lVal;     //set value to cell 

      } 

    } 

 

    beforeExcelExport(args: any): void { 

      args.gridObject.columns[3].format="N0";   //before exporting, changed the format 

    } 

    excelExportComplete(args: any): void { 

      (this.grid.columns[3]as any).format="N2"     //after exporting reset the format 

    } 

 

    excelQueryCellInfo(args: any): void { 

      var sVal = String(args.data.Freight); 

      var arr = sVal.split('.'); 

      var val =""; 

      for(var i: number = 0; i < arr.length; i++) { 

          val += arr[i]; 

        } 

        var lVal = Number(val); 

        args.data.Freight = lVal; 

    } 

} 

 

 

 

 

Sample: https://stackblitz.com/edit/angular-rxzgxa-nhqhyt?file=app.component.ts  

 

Reference:  

https://ej2.syncfusion.com/angular/documentation/api/grid/#querycellinfo 

https://ej2.syncfusion.com/angular/documentation/api/grid/#excelquerycellinfo 

https://ej2.syncfusion.com/angular/documentation/api/grid/#beforeexcelexport 

https://ej2.syncfusion.com/angular/documentation/api/grid/#excelexportcomplete 

 

Please get back to us if you need further assistance. 

 

Regards, 

Prasanna Kumar N.S.V