How to create a dynamic range for calculating the sum of column values in Angular Spreadsheet
I am trying to add a grand total to a numeric column in Syncfusion Angular Spreadheet. I have a column (column E) containing order amounts. After listing all orders I want to add a total amount. If for example there are 100 rows in the spreadsheet, I would like to add on cell E103 the sum of all amounts from row 3 up to row 103. Thus, E103 should have [formula]="=SUM(E3:E103)". How can I define the range something like 'E3:E'+this.lastRow' or 'E3:E`{this.lastRowNo}`' ?
What is the appropriate solution to what I want to achieve?
Below is my code. It is currently giving a #NAME? entry on the cell.
Thanks in advance.
1. Component class
export class BackOrderListPanelComponent implements OnInit {
@ViewChild('spreadsheet') spreadsheet: SpreadsheetComponent
orders: Order[] = [];
public spreadsheetData: any[] = [];
lastRowNo: number = 0;
HEADER_ROWS_COUNT: number = 2;
freezePane: number = 2;
constructor(private service: OrderService) {}
ngOnInit() {
let dealerCode: string = 'D501';
let page: number = 1;
let start: number = 1;
let limit: number = 50;
this.service.getOrderList(dealerCode, page, start, limit)
.subscribe((response: QueryResultList<Order>) => {
if (response.success && response.data) {
this.orders = response.data;
this.lastRowNo = response.data.length + HEADER_ROWS_COUNT; // add number of rows of column headers (2)
console.log(`lastRowNo: ${this.lastRowNo}`);
} else {
const error = response.error;
}
});
}
beforeDataBound() {
if(this.spreadsheet) {
this.spreadsheet.addDefinedName({
name: 'totalAmountRange',
refersTo: "'=E3:E'+this.lastRowNo"
});
}
}
}
2. Component Template
<ejs-spreadsheet (created)="onSpreadsheetCreated()" (beforeDataBound)="beforeDataBound()">
<e-definednames>
<e-definedname name="totalAmountRange" refersTo="'=E3:E'+lastRowNo"></e-definedname>
</e-definednames>
<e-sheets>
<e-sheet name="Back Orders" [frozenRows]="freezePane" [frozenColumns]="freezePane" selectedRange="C1">
<e-ranges>
<e-range [dataSource]="spreadsheetData" startCell="A3" [showFieldAsHeader]="false"></e-range>
</e-ranges>
<e-rows>
<!-- Header Rows -->
<e-row>
<e-cells>
<e-cell [index]="0" [value]="'ORDER'" [colSpan]="2" [style]="{ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }"></e-cell>
<e-cell [index]="2" [value]="'Customer'" [rowSpan]="2" [style]="{ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }"></e-cell>
<e-cell [index]="3" [value]="'Type'" [rowSpan]="2" [style]="{ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }"></e-cell>
<e-cell [index]="4" [value]="'Amount'" [rowSpan]="2" [style]="{ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }"></e-cell>
</e-cells>
</e-row>
<e-row>
<e-cells>
<e-cell [index]="0" [value]="'Number'" [style]="{ fontWeight: 'bold', textAlign: 'center' }"></e-cell>
<e-cell [index]="1" [value]="'Date'" [style]="{ fontWeight: 'bold', textAlign: 'center' }"></e-cell>
</e-cells>
</e-row>
<e-row [index]="lastRowNo">
<e-cells>
<e-cell [index]="0" value="Total Amount" colSpan="4" [style]="styles"></e-cell>
<e-cell [index]="4" [formula]="'=SUM(totalAmountRange)'" [style]="styles"></e-cell>
</e-cells>
</e-row>
</e-rows>
<e-columns>
<e-column [width]=120></e-column>
<e-column [width]=120></e-column>
<e-column [width]=180></e-column>
<e-column [width]=100></e-column>
<e-column [width]=120 format="C2"></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
Hi MARIO GARRIDO,
We have validated your query based on your shared details. To replicate the issue, we created a local Angular sample and appended data to the spreadsheet using remote data binding, as you did in your code snippet. Since your dataset was not shared, we used default data from OData services. We then attempted to dynamically add a defined name (totalAmountRange) and display the sum of the range. While doing so, we were able to reproduce the issue.
Upon further investigation, we found that you have added the definedName range like "'=E3:E'+this.lastRowNo" which treats the range as a text instead of evaluating this.lastRowNo, resulting in the defined name referencing an incorrect or static value rather than the intended dynamic range. So, we changed the reference to `=E3:E${this.lastRowNo}` for proper range reference.
Additionally, after correcting this reference, we encountered another issue where the sum appeared in the wrong cell with an incorrect value. Further validation revealed that this occurred because, when loading data remotely, there is a delay in data retrieval, but the beforeDataBound event is triggered before the data is fully loaded. This led to an incorrect calculation of lastRowNo.
To resolve this, we recommend using the created event, which triggers after the data is fully loaded. We also updated the formula assignment to occur within the created event instead of cell data binding. This ensures that the sum calculation happens after the defined name is included, preventing invalid or incorrect values. After making these adjustments, we were able to resolve the issue and achieve the expected output.
Code snippet:
|
created() { // using created event instead of beforeDataBound event - to get triggered after data loading if (this.spreadsheetObj) { const isDefinedNameExists = this.spreadsheetObj.definedNames.some( (name) => name.name === 'totalAmountRange' ); if(!isDefinedNameExists){ this.spreadsheetObj.addDefinedName({ name: 'totalAmountRange', refersTo: `=D3:D${this.lastRowNo}` // Correct range reference }); //updating the cell with formula once the defined name is added this.spreadsheetObj.updateCell({ formula: "=SUM(totalAmountRange)"}, `D${this.lastRowNo + 1}`); console.log("this: ",`=D3:D${this.lastRowNo}`); } } }
|
Reference output:
For your reference, we have shared a sample demonstrating the solution. Please find the sample from the below attachment.
Please get back to us if you have any other concerns.
With regards,
Deira
Attachment: Forum_cb8b86eb.zip
