We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

How to import excel data to Angular Grid

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


12 Replies

SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team July 19, 2019 01:26 PM UTC

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]);  
  }  
}  

       

Regards,  
Seeni Sakthi Kumar S. 



SH Satish H N July 25, 2019 04:44 AM UTC

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


SH Satish H N July 25, 2019 10:52 AM UTC

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


SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team July 25, 2019 11:30 AM UTC

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); 
        }; 

        .   .  . 
      }; 




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.  


Regards, 
Seeni Sakthi Kumar S. 



SH Satish H N July 25, 2019 11:44 AM UTC

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


SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team July 26, 2019 08:47 AM UTC

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(); 
  } 



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

Regards, 
Seeni Sakthi Kumar S. 



SH Satish H N July 26, 2019 09:14 AM UTC

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


SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team July 29, 2019 06:22 AM UTC

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. 



DD Dinesh D September 24, 2019 07:20 AM UTC

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




FS Farveen Sulthana Thameeztheen Basha Syncfusion Team September 25, 2019 11:33 AM UTC

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:- 

Refer to the API Link:- 

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:- 

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

Regards, 
Farveen sulthana T 



SH Satish H N September 26, 2019 02:40 AM UTC

Hi Farveen,

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


FS Farveen Sulthana Thameeztheen Basha Syncfusion Team September 27, 2019 04:47 AM UTC

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.  

  


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 


Loader.
Live Chat Icon For mobile
Up arrow icon