How to import excel data to Angular Grid

[Satish H N]

Hello,

I am working on creating a Data Grid table and able to achieve the same. Also i have implemented Export To Excel functionality successfully. Now need to import data from excel and display the same in Grid table.

Could anyone please help me in achieving this functionality for Angular 6 Grid.

Thanks in advance

[Seeni Sakthi Kumar Seeni Raj]

HI Satish,  

 

Thanks for contacting Syncfusion Support.  

As per your requirement, we have created a sample for your reference. In the below sample, we have parse the excel file data using XLSX and bind excel data to Grid in file uploader change event. You can also use the below way to achieve your requirement.  

Kindly refer the below code example and sample for more information.  

<div class="control-section">  

  <div class="col-lg-9">  

    <div class="control_wrapper">  

      <ejs-uploader #defaultupload id='defaultfileupload' [dropArea]='dropElement'(selected)='onFileRemove($event)' (change)="onSuccess($event)"></ejs-uploader>  

    </div>  

    <ejs-grid #grid id ='grid'> </ejs-grid>  

  </div>  

</div>  

import * as XLSX from 'xlsx';  

  

  

export class DefaultUploaderComponent {  

  @ViewChild('defaultupload')  

  public uploadObj: UploaderComponent;  

  @ViewChild('grid')  

  public gridObj: GridComponent;  

  

  public onFileRemove(args): void {  

    args.cancel = true;  

  }  

  

  parseExcel(file) {  

        let reader = new FileReader();  

        reader.onload = (e)=> {  

          let data = (<any>e.target).result;  

          let workbook = XLSX.read(data, {  

            type: 'binary'  

          });  

          workbook.SheetNames.forEach((function(sheetName) {  

            // Here is your object  

  

            let XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);  

            let json_object = JSON.stringify(XL_row_object);  

  

            // bind the parse excel file data to Grid  

            this.gridObj.dataSource = JSON.parse(json_object);  

          }).bind(this), this);  

        };  

  

        reader.onerror = function(ex) {  

          console.log(ex);  

        };  

        reader.readAsBinaryString(file);  

      };  

  

  public onSuccess(args: any): void {  

    let files = args.target.files; // FileList object  

    this.parseExcel(files[0]);  

  }  

}  

Sample: https://stackblitz.com/edit/angular-ohqou2-bkzyec?file=default.html   

       

Reference Link : https://stackoverflow.com/questions/8238407/how-to-parse-excel-file-in-javascript-html5   

Regards,  

Seeni Sakthi Kumar S. 

[Satish H N]

Hi Seeni Sakthi Kumar Seeni Raj,

Thanks for your reply. It is working.

I have one more query.

when import one excel and data gets displayed in the grid, then immediately if i edit the same excel and import it is not working. I am not seeing any error in console.

And I need to place the code in "DefaultUploaderComponent" in a JS file right?Also i want import to be opened as popup modal on click of button. Could you please help me on this

Regards,

Satish

[Satish H N]

Hi,

I have one more query:

One import of excel i want to compare and remove duplicates before binding the excel data to grid datasource.

Thanks in advance,

Satish

[Seeni Sakthi Kumar Seeni Raj]

Hi Satish, 

Query #1: when import one excel and data gets displayed in the grid, then immediately if i edit the same excel and import it is not working. 

You can import the same file with new data by clearing the uploader files. Please refer to the below code example, documentation link and sample link for more information. 

[component.ts] 

parseExcel(file) { 

          .   .   .   

          workbook.SheetNames.forEach((function(sheetName) { 

            // Here is your object 

            var XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]); 

            var json_object = JSON.stringify(XL_row_object); 

            console.log(json_object) 

            this.gridObj.dataSource = JSON.parse(json_object); 

          this.uploadObj.clearAll(); 

          }).bind(this), this); 

        }; 

        .   .  . 

      }; 

Documentation: https://ej2.syncfusion.com/angular/documentation/api/uploader/#clearall 

Sample               : https://stackblitz.com/edit/angular-ohqou2-jea3kh?file=default.component.ts 

Query #2: And I need to place the code in "DefaultUploaderComponent" in a JS file right? 

 

Yes, you can place the custom parcfeExcel() method in a separate file and use it in your component. 

Query #3: Also i want import to be opened as popup modal on click of button. Could you please help me on this 

 

Could you please provide more details on your requirement like pictorial representation/ video demonstration that will be helpful for us to provide a better solution as early as possible. 

Query #4: One import of excel i want to compare and remove duplicates before binding the excel data to grid datasource. 

This has already been discussed in the following Help Document.  

https://ej2.syncfusion.com/angular/documentation/uploader/validation/#duplicate-files 

Regards, 

Seeni Sakthi Kumar S. 

[Satish H N]

Hi,

Thanks for the quick reply, For query #3, in the below image you can see import and browse..

so my requirement is when click on import, i need to show browse in popup or modal. And in the popup or modal on click of Browse i must be able to select file for upload.

Thanks in advance,

Satish

[Seeni Sakthi Kumar Seeni Raj]

Hi Satish, 

Thanks for sharing the details with us. 

We could see you would like a browsing (uploader) within the Dialog and on clicking them the excel document has to be uploaded using the Select file wizard of the explorer. Refer to the following code example. 

  

[component.ts] 

@Component({ 

  selector: 'control-content', 

  template: `<div  id="container" style="height:300px" class="control-section"> 

              <div class="col-lg-9"> 

              <div class="control_wrapper"> 

      <button (click)="importFile($event)">Import</button> 

      <div id="dialog"></div> 

     <ejs-uploader #defaultupload id='defaultfileupload' [dropArea]='dropElement' (selected)='onFileRemove($event)' (change)="onSuccess($event)"></ejs-uploader></div> 

     <ejs-grid #grid id='grid'></ejs-grid> 

              </div> 

</div>`   

}) 

export class DefaultUploaderComponent implements OnInit { 

.   .   . 

  ngOnInit(): void { 

     

    this.dialog = new Dialog({ 

      // Enables the header 

      header: 'Import', 

      // Enables the close icon button in header 

      showCloseIcon: true, 

      visible: false, 

      // Dialog content 

      content: this.uploadObj.element, 

      // The Dialog shows within the target element 

      target: document.getElementById("container"), 

      // Dialog width 

      width: '250px' 

    }); 

    this.dialog.appendTo('#dialog'); 

  } 

.  .  . 

  importFile(e) { 

    this.dialog.show(); 

  } 

} 

Documentation: https://ej2.syncfusion.com/documentation/dialog/getting-started/ 

Sample               : https://stackblitz.com/edit/angular-ohqou2-3wb5kb?file=default.component.ts 

Please get back to us if you need any further assistance on this. 

Regards, 

Seeni Sakthi Kumar S. 

[Satish H N]

Hi Seeni Sakthi Kumar S,

Very thankful to you for your quick and simple solutions as I am able to complete all my requirements using EJ2 grid with import and export functionality.

Thank you again :)

Regards,

Satish

[Seeni Sakthi Kumar Seeni Raj]

Hi Satish,  

Thanks for your update.  

We are happy to hear that your requirement has been achieved and you are good to go.  

Please get back to us, if you require further assistance on this.  

Regards,  

Seeni Sakthi Kumar S. 

[Dinesh D]

I have posted one question. I need to append the child element to the existing parent element( with same columns names) in grid table. can you please reply for that

[Farveen Sulthana Thameeztheen Basha]

Hi Dinesh, 

Thanks for using Syncfusion Support. 

Query#:- I need to append the child element to the existing parent element( with same columns names) in grid table.  

If you want to add the child Element to the existing parent, you can use newRowPosition as Child of editSettings property to add the child record corresponding to the parent record.  

Refer to the code example:- 

public treegrid: TreeGridComponent; 

         ngOnInit(): void { 

        this.data = sampleData; 

        this.editSettings ={ allowEditing: true, allowAdding: true, allowDeleting: true, mode:"Cell", newRowPosition:"Child"};  

        this.toolbar = ['Add', 'Delete', 'Update', 'Cancel']; 

        this.taskidrules = { required: true , number: true}; 

        .    .   .  

        this.d1data= [{ id: 'CellEditing', name: 'Cell Editing' }, {id: 'RowEditing', name: 'Row Editing'} ] 

    } 

Refer to the Demo Link:- 

https://stackblitz.com/edit/angular-cqmxn1?file=app.component.ts 

Refer to the API Link:- 

https://ej2.syncfusion.com/documentation/api/treegrid/editSettings/#newrowposition 

Otherwise you can also add the child record corresponding to the parent element by passing the Position parameter as Child using addRecord method of TreeGrid. Refer to the API Link:- 

https://ej2.syncfusion.com/documentation/api/treegrid/#addrecord 

Please get back to us if you need any further assistance. 

Regards, 

Farveen sulthana T 

[Satish H N]

Hi Farveen,

Thanks for the reply. But one thing  would like to mention is I am not using TreeGrid, but used "<ejs-grid"

[Farveen Sulthana Thameeztheen Basha]

Hi Dinesh,  

In EJ2 Grid, we have Hierarchy Binding Grid feature in which we can add Child Grid to the corresponding parent row element. Please refer to the below screenshot, documentation link and sample link for more information.  

  

Documentation: https://ej2.syncfusion.com/angular/documentation/grid/hierarchy-grid/  

Demo                  : https://ej2.syncfusion.com/angular/demos/#/material/grid/hierarchy  

While seeing your shared UI, we think our EJ2 TreeGrid will satisfy your requirement. So we suggest you  to use TreeGrid component if it suits most of your requirement.  

Regards, 

Farveen sulthana T