1. Home
  2. Forum
  3. Angular
  4. Updating Spreadsheet dynamically using Cell Binding

Updating Spreadsheet dynamically using Cell Binding

Dear Syncfusion Team,


How to dynamically update whole Spreadsheet while using cell binding. 
To simplify, please look on your example. 
I would like Spreadsheet to refresh the content every time when "spreadData" changes, without reloading the whole component.

Regards,
Maciej

<ej-spreadsheet id="spreadsheet" [sheets] = "spreadData">
</ej-spreadsheet>
import { Component, ViewEncapsulation } from '@angular/core';

@Component({
  selector: 'ej-app',
  templateUrl: 'app/app.component.html',  //give the path file for spreadsheet control html file.
})
export class AppComponent {
  public spreadData;
  constructor() {
    this.spreadData = [{
      rows: [{
        height: 30,
        cells: [
          { value: "Item Name", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
          { value: "Quantity", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
          { value: "Price", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
          { value: "Amount", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
          { value: "Stock Detail", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
          { value: "Website", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } }
        ]
      },
      {
        cells: [
          { value: "Casual Shoes", comment: { value: "Casual Footwears with wide variety of colors." } },
          { value: "20", index: 2, format: { type: "currency" } },
          { value: "=B2*C2" },
          { value: "OUT OF STOCK" },
          { value: "Amazon", hyperlink: { webAddr: "www.amazon.com" } }
        ]
      },
      {
        cells: [
          { value: "Sports Shoes", style: { "background-color": "#E5F3FF" } },
          { value: "20", style: { "background-color": "#E5F3FF" } },
          { value: "30", format: { type: "currency" }, style: { "background-color": "#E5F3FF" } },
          { value: "=B3*C3", style: { "background-color": "#E5F3FF" } },
          { value: "IN STOCK", style: { "background-color": "#E5F3FF" } },
          { value: "AliExpress", hyperlink: { webAddr: "www.aliexpress.com" }, style: { "background-color": "#E5F3FF" } }
        ]
      },
      {
        cells: [
          { value: "Formal Shoes", comment: { value: "Formal Footwears with wide range of sizes." } },
          { value: "20" },
          { value: "15", format: { type: "currency" } },
          { value: "=B4*C4" },
          { value: "IN STOCK" },
          { value: "Amazon", hyperlink: { webAddr: "www.amazon.com" } }
        ]
      },
      {
        height: 30,
        index: 5,
        cells: [
          { style: { "background-color": "#428bca" } },
          { style: { "background-color": "#428bca" } },
          { value: "Total Amount", index: 2, style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
          { value: "=Sum(D2:D4)", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
          { style: { "background-color": "#428bca" } },
          { style: { "background-color": "#428bca" } }
        ]
      }]
    }];
  }
}

1 Reply
SI Silambarasan I Syncfusion Team January 29, 2018 09:46 AM UTC

Hi Maciej, 
 
Thank you for using Syncfusion products. 
 
We have checked your requirement ‘To dynamically update whole Spreadsheet while using cellbinding’ and it can be achievable in Spreadsheet. Please refer the below methods and its operation details. 
 
Public methods 
API Link 
Operation 
updateRange() 
 
To update data for range of cells. 
updateCellValue() 
To update data for particular cell value and its format. 
setHyperlink() 
To set hyperlink for a cell or range of cells. 
setHeightToRows()    
To set height for the rows. 
format() 
To apply format for a cell or range of cells. 
 
Note: When the ‘spreadData’ values are modified externally then, we can use the above methods to refresh the Spreadsheet based on the modification. There’s no option for refresh automatically since the cell data binding ‘spreadData’ is used to update the Spreadsheet on initial rendering only. 
 
We have prepared a sample to demonstrate your requirement and the same can be downloaded from the following sample link. 
 
Code Example: 
html 
 
//... 
 
<button class="btn btn-primary" (click)="performUpdateAction('ranges')">Update range - A1:E2</button> 
<button class="btn btn-primary" (click)="performUpdateAction('cell')">Update cell - A3</button> 
<button class="btn btn-primary" (click)="performUpdateAction('hyperlink')">Update hyperlink - F4</button> 
<button class="btn btn-primary" (click)="performUpdateAction('rowheight')">Update 2nd row height</button> 
<button class="btn btn-primary" (click)="performUpdateAction('format')">Update formats in D2:D4</button> 
//... 
 
<ej-spreadsheet id="spreadsheet" [sheets] = "spreadData"></ej-spreadsheet> 
 
 
ts 
 
performUpdateAction (action) { 
       var excelObj = $("#spreadsheet").data("ejSpreadsheet"), className, heightRowObj; 
 
       switch (action){ 
           case "ranges": 
               var settings = { dataSource: [{ Column1: "XYZ", Column2: 2000, Column3: 30, Column4: 60000, Column5: "IN Stock" }], showHeader: "true", startCell: "A1" }; 
               // To update range of cells with the specified settings 
               excelObj.updateRange(1, settings); 
               //.. 
               break; 
           case "cell": 
               className = excelObj.XLFormat.getFormatHashCode({ color: "#FFFFFF", "background-color": "#ec2024" }); 
               // To update the specified values in a particular cell. 
               excelObj.XLEdit.updateCellValue({rowIndex: 2, colIndex: 0}, "Product 1", className, 1); 
               break; 
           case "hyperlink": 
               excelObj.setHyperlink("F4",{"cellAddr":"https://www.google.com"}, 1); 
               excelObj.XLEdit.updateCellValue({rowIndex: 3, colIndex: 5}, "Google", "", 1);  
               break; 
           case "rowheight": 
               heightRowObj= [{rowIndex: 1, height: 40}]; 
               excelObj.setHeightToRows(heightRowObj); 
               break; 
           case "format": 
               excelObj.XLFormat.format({ "type": "currency", "formatStr":"{0:C3}", style:{ "background-color": "#FF0000", color: "#FFFFFF" } }, "D2:D4"); 
               break; 
       } 
} 
 
 
Please check the above sample and get back to us if you need any further assistance. 
 
Regards, 
Silambarasan 


Loader.
Up arrow icon