1. Home
  2. Forum
  3. Angular - EJ 2
  4. Load/Save worksheet data individually

Load/Save worksheet data individually

I have some data that spread across multiple worksheets and I want manipulate data by each sheets separately. Basically I want to load , read and run some validations agains each sheets programatically. Could you please give some documentations or samples how to load and read worksheet data individuall?


9 Replies
JS Janakiraman Sakthivel Syncfusion Team September 21, 2021 03:41 AM UTC

Hi Rahal Ekanayaka, 
 
Thank you for contacting Syncfusion support. 
 
We have checked your requirements and they can be achieved in our EJ2 Spreadsheet. By using the ranges property, we can load data into each sheet. And by using the getData and getRowData methods, we can read data from a data source.  And we can add validation by using the addDataValidation method, as shown below. In our spreadsheet we have data validation support, so you can add data validation to any sheet. 
 
 
<button id="button1" (click)="loadData()">Load Data</button> 
<button id="button2" (click)="readData()">Read Data</button> 
<button id="button3" (click)="applyValidation()">Apply Validation</button> 
 
   loadData() { 
        this.spreadsheetObj.sheets[1].ranges[0].dataSource = [].concat(grossPay()); 
 
        this.spreadsheetObj.sheets[2].ranges[0].dataSource = [].concat(GDPData()); 
    } 
 
    readData() { 
        // Used to get a row data from the data source with updated cell value. 
        this.spreadsheetObj.getData("CarSalesReport!A1:B2").then(data => console.log('getData - ', data)); 
 
        // Used to get a row data from the data source with updated cell value. 
        var rowData = this.spreadsheetObj.getRowData(0,0); 
        console.log('getRowData - ', rowData); 
    } 
 
    applyValidation() { 
    this.spreadsheetObj.addDataValidation({ type: 'List', operator: 'LessThan', value1: '100,200,300', ignoreBlank: false }, 'F2:F30'); 
    this.spreadsheetObj.addDataValidation({ type: 'WholeNumber', operator: 'EqualTo', value1: '30000', ignoreBlank: false }, 'F31:F31'); 
    this.spreadsheetObj.addInvalidHighlight('F31:F31'); 
    } 
       
 
 
For your convenience, we have prepared the sample based on your requirements. Please find the link below. 
 
 
Documentation Link: 
  
Could you please check the above links and get back to us if we misunderstood your requirement or if you need any further assistance on this? 
 
Regards, 
Janakiraman S. 


RE Rahal Ekanayaka September 26, 2021 10:53 PM UTC

Hi Janakiraman,

Thank you for the sample. However in your sample by clicking on "Load data" does not seems load the data into datasheet. Could you please 


JS Janakiraman Sakthivel Syncfusion Team September 27, 2021 12:07 PM UTC

Hi Rahal Ekanayaka, 
 
Thank you for your update. 
 
We have checked your reported query ”clicking on ‘Load data’ does not seem to load the data into the datasheet” in our end. For your convenience, we have prepared the sample and video demonstration of this issue. Please find the link below. 
 
 public sheet1Data: Object[] = getDefaultData(); 
 public sheet2Data: Object[] = []; 
 public sheet3Data: Object[] = []; 
 
<e-sheet name="CarSalesReport"> 
     <e-ranges> 
        <e-range [dataSource]="sheet1Data"></e-range> 
     </e-ranges> 
</e-sheet> 
<e-sheet name="DataLoaded1"> 
      <e-ranges> 
          <e-range [dataSource]="sheet2Data"></e-range> 
      </e-ranges> 
  </e-sheet> 
  <e-sheet name="DataLoaded2"> 
        <e-ranges> 
            <e-range [dataSource]="sheet3Data"></e-range> 
       </e-ranges> 
</e-sheet> 
 
loadData() { 
        this.sheet1Data = orderDetails(); 
        this.sheet2Data = grossPay(); 
        this.sheet3Data = GDPData(); 
    } 
 
 
Could you please check the above links and get back to us if you need further assistance on this. 
 
Regards, 
Janakiraman S. 


RE Rahal Ekanayaka September 29, 2021 10:31 AM UTC

Hi Janakiraman ,

Thanks you for the example. However in my use case I generate worksheets dynamically. So I don't have luxury to bind dataSource as in <e-range [dataSource]="sheet3Data"></e-range> 


Could you please have a look at attached my sample code . In my example basically what I want is

  1. load sheets with data upon click on "Load Meta" (please check onLoadMeta())
  2. Change data in selected sheet upon click on "Load Data" (please check loadData())

<div class="control-section">
<ejs-spreadsheet #spreadsheet (actionBegin)="actionBegin($event)" (created)="onCreated()" [allowChart]="false" [allowMerge]="false" >
<e-sheets >
<e-sheet *ngFor="let wb of worksheets" [name]="wb.title">
<e-ranges>
<e-range [dataSource]="wb.data" >

</e-range>
</e-ranges>
<e-columns
><e-column [width]=90></e-column>
<e-column [width]=100></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</div>
<div class="row">
<div class="col-xs-12">
<button class="btn btn-success" type="button" (click)="onLoadMeta()" >Load Meta</button>
<button class="btn btn-danger" type="button" (click)="loadData()" >Load Data</button>
<button class="btn btn-primary" type="button" >Clear</button>
<button class="btn btn-primary" type="button" (click)="onSave()">Save</button>
</div>
</div>

@ViewChild('spreadsheet')
public spreadsheetObj !: SpreadsheetComponent;

title = 'spreadsheet';
worksheets: any[] = []

onLoadMeta() {
// this.worksheetService.getMetObjects()
// .subscribe((data: any) => {
// this.worksheets = data.body;
// });
this.worksheets = [
{
title: 'Attribute',
data: [{
"Customer Name": "Romona Heaslip",
"Model": "Taurus",
"Color": "Aquamarine",
"Payment Mode": "Debit Card",
"Delivery Date": "07/11/2015",
"Amount": "8529.22"
}]
},
{
title: 'Class',
data: [{
"Customer Name": "Romona Heaslip",
"Model": "Taurus",
"Color": "Aquamarine",
"Payment Mode": "Debit Card",
"Delivery Date": "07/11/2015",
"Amount": "8529.22"
}]
},
{
title: 'DataType',
data: [{
"Customer Name": "Romona Heaslip",
"Model": "Taurus",
"Color": "Aquamarine",
"Payment Mode": "Debit Card",
"Delivery Date": "07/11/2015",
"Amount": "8529.22"
}]
}
]
}

loadData() {

if(this.spreadsheetObj.sheets[1].ranges) {
this.spreadsheetObj.sheets[1].ranges[0].dataSource = [{
"Customer Name": "Romona Heaslip",
"Model": "Taurus",
"Color": "Aquamarine",
"Payment Mode": "Debit Card",
"Delivery Date": "07/11/2015",
"Amount": "8529.22"
},
{
"Customer Name": "Clare Batterton",
"Model": "Sparrow",
"Color": "Pink",
"Payment Mode": "Cash On Delivery",
"Delivery Date": "7/13/2016",
"Amount": "17866.19"
},
{
"Customer Name": "Eamon Traise",
"Model": "Grand Cherokee",
"Color": "Blue",
"Payment Mode": "Net Banking",
"Delivery Date": "09/04/2015",
"Amount": "13853.09"
}]
}

}

JS Janakiraman Sakthivel Syncfusion Team September 30, 2021 05:06 PM UTC

Hi Rahal Ekanayaka, 
  
Query1: Load sheets with data upon click on "Load Meta". 
 
We have checked your requirements and it can be achievable in our EJ2 Spreadsheet by using the insertSheet method as like as below. 
 
Code Example: 
 
onLoadMeta() { 
        this.spreadsheetObj.insertSheet([{ 
            index: 1, 
            name: 'Inserted Sheet', 
            ranges: [{ dataSource: this.worksheets }], 
            columns: [{ width: 150 }, { width: 110 }, { width: 110 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 }, 
                { width: 85 }] 
        }]); 
    } 
 
 
For your convenience, we have prepared the sample based on our suggestion. Please find the link below. 
 
 
Query2: Change data in selected sheet upon click on "Load Data". 
 
We have checked your reported requirement and we need to validate more on this, so we will update you the further details on reported October 4th, 2021. We appreciate your patience until then. 
  
Regards,            
Janakiraman S. 


RE Rahal Ekanayaka October 9, 2021 04:58 PM UTC

Hi Janakiraman,


Any update on this?


JS Janakiraman Sakthivel Syncfusion Team October 12, 2021 03:38 AM UTC

Hi Rahal Ekanayaka, 
 
Sorry for the inconvenience caused.  
  
We have faced some issues while validating your requirement and need to ensure many dependent cases for this. So, we will update you the further details on October 13th, 2021. We appreciate your patience until then.  
   
Regards,             
Janakiraman S. 


JS Janakiraman Sakthivel Syncfusion Team October 14, 2021 03:52 AM UTC

Hi Rahal Ekanayaka, 
 
Thank you for your patience. 
 
We have validated your reported requirement. We cannot able to bind dynamic data in selected sheet. So, we have logged this issue as a bug, and it will be available in our upcoming patch release which will be scheduled on November 3rd, 2021 and we appreciate your patience until then.   
 
You can also track the status of this bug by using below feedback portal link.   
  
 
Please get back to us, if you need further assistance. 
 
Regards, 
Janakiraman S. 


JS Janakiraman Sakthivel Syncfusion Team November 8, 2021 03:22 AM UTC

Hi Rahal Ekanayaka, 
 
Thank you for your patience. 
 
We have checked your reported requirement “Change data in selected sheet upon click on ‘Load Data’” and it can be achievable in our EJ2 Spreadsheet now by using the updateRange method as like as below. 
  
 
loadData() { 
    this.spreadSheetInstance.clear({ type: "Clear All", range: "A1:AA100" }); 
    this.spreadSheetInstance.updateRange({dataSource: orderDetails(), startCell: "A1"}, this.spreadSheetInstance.activeSheetIndex + 1); 
    this.spreadSheetInstance.refresh(); 
} 
 
  
For your convenience, we have prepared the sample and video demonstration on your requirement. Please find the link below.  
 
Could you please check the above links and get back to us if you need further assistance on this. 
 
Regards,
Janakiraman S.

Loader.
Up arrow icon