I am trying to export the contents of my grid out to excel. One of the columns is a number but needs to be exported as a percentage (with a percent sign).
My main complaint is that the ng-template formatting is not being picked up by the export so, while the grid shows 56.75% when i export it to excel it shows 56.75.
Then I discovered that I could use the cell options feature and format the number. However, when I do this with the % it exports as 5675% not 56.75%. I can not figure out how to divide that number by 100.
Here is the grid code. I have been working on this for hours. Please help :)
<kendo-grid
#grid
class="mt-1"
[kendoGridBinding]="gridSelection ? (whoDoIOwns$ | async) : (whoOwnsMe$ | async)"
reorderable="true"
[sortable]="{ allowUnsort: true, mode: 'multiple' }"
[sort]="[
{ field: 'glNumber', dir: 'desc' },
{ field: 'legalName', dir: 'asc' }
]"
resizable="true"
>
<kendo-grid-column field="id" title="id" [hidden]="true" width="8%"></kendo-grid-column>
<!-- <kendo-grid-column title="Entity ID" field="legalEntityID" width="8%"></kendo-grid-column> -->
<kendo-grid-column
*ngIf="userAllData && !gridSelection"
title="FEIN"
field="fein"
width="10%"
></kendo-grid-column>
<kendo-grid-column
*ngIf="userAllData && gridSelection"
title="FEIN"
field="ownedFEIN"
width="10%"
></kendo-grid-column>
<kendo-grid-column
*ngIf="!gridSelection"
title="GL Number"
field="glNumber"
width="12%"
></kendo-grid-column>
<kendo-grid-column
*ngIf="gridSelection"
title="GL Number"
field="ownedGLNumber"
width="12%"
></kendo-grid-column>
<kendo-grid-column *ngIf="!gridSelection" title="Name" [field]="'legalName'" width="46%">
<ng-template kendoGridCellTemplate let-dataItem>
<a
[href]="'#/entities/' + dataItem.legalEntityID"
target="_blank"
style="color: blue; text-decoration: underline;"
>{{ dataItem.legalName }}</a
>
</ng-template>
</kendo-grid-column>
<kendo-grid-column *ngIf="gridSelection" title="Name" [field]="ownedLegalName" width="46%">
<ng-template kendoGridCellTemplate let-dataItem>
<a
[href]="'#/entities/' + dataItem.ownedLegalEntityID"
target="_blank"
style="color: blue; text-decoration: underline;"
>{{ dataItem.ownedLegalName }}</a
>
</ng-template>
</kendo-grid-column>
<kendo-grid-column title="Legal Role" field="ownershipLegalRole" width="12%"></kendo-grid-column>
<kendo-grid-column title="Acct Type" field="accountingOwnershipType" width="8%"></kendo-grid-column>
<kendo-grid-column title="Pct" field="ownershipPercentage" width="8%">
<ng-template kendoGridCellTemplate let-dataItem>
{{ dataItem.ownershipPercentage }}%
</ng-template>
</kendo-grid-column>
<kendo-grid-excel [fileName]="(gridSelection ? 'i-own-' : 'owns-me-') + 'ownership.xlsx'">
<kendo-excelexport-column
*ngIf="userAllData && !gridSelection"
field="fein"
title="FEIN"
></kendo-excelexport-column>
<kendo-excelexport-column
*ngIf="userAllData && gridSelection"
field="ownedFEIN"
title="FEIN"
></kendo-excelexport-column>
<kendo-excelexport-column
*ngIf="!gridSelection"
title="GL Number"
field="glNumber"
></kendo-excelexport-column>
<kendo-excelexport-column
*ngIf="gridSelection"
title="GL Number"
field="ownedGLNumber"
></kendo-excelexport-column>
<kendo-excelexport-column
*ngIf="!gridSelection"
title="Name"
field="legalName"
></kendo-excelexport-column>
<kendo-excelexport-column
*ngIf="gridSelection"
title="Name"
field="ownedLegalName"
></kendo-excelexport-column>
<kendo-excelexport-column
title="Legal Role"
field="ownershipLegalRole"
></kendo-excelexport-column>
<kendo-excelexport-column
title="Acct Type"
field="accountingOwnershipType"
></kendo-excelexport-column>
<kendo-excelexport-column
title="Pct"
field="ownershipPercentage"
[cellOptions]="{ format: '#.##%' }"
>
<ng-template kendoGridExcelTemplate let-dataItem>
{{ dataItem.ownershipPercentage }}%
</ng-template>
</kendo-excelexport-column>
</kendo-grid-excel>
</kendo-grid>
1 Answer, 1 is accepted
Hi Jimmy,
Thank you for the provided code snippet.
Basically, this is a known limitation of the Grid Excel export. During the export to Excel, the cell column templates of the Grid are not evaluated. Please refer to the following article to check all current limitation
https://www.telerik.com/kendo-angular-ui/components/grid/export/excel-export/#toc-known-limitations
By design, the Excel Export of the Grid works with the bound data, rather than the actual formatting from the UI. The format can be customized with the cellOptions property or by changing the configurations in the Excel Worksheet - iterate every cell and set the cell.format option to the desired format before exporting:
https://www.telerik.com/kendo-angular-ui/components/grid/export/excel-export/#toc-customizing-generated-workbooks
The reported behavior is expected when formatting an existing number to percentage since the Excel multiplies those numbers by 100 to convert them to percentages:
What needs to be done is to make sure that numbers are calculated as percentages, and that they are displayed in decimal format.
Here is an example where the 'UnitPrice' column is formatted as a percentage:
https://stackblitz.com/edit/angular-jj5wme?file=app/app.component.ts\
I hope this helps.
Regards,
Martin
Progress Telerik
Our thoughts here at Progress are with those affected by the outbreak.