How to extract data using excel upload?

[soob]

Hello, It's me agian.:^D

This time, I want to extract data using excel uploading. 

I want to use the 'uploader' to upload an Excel file, 

extract data from it, 

and spread it on a grid (not a spread sheet). 

can you help me? ;)...

It would be nice if you could suggest a way to do this, 

and it would be better if you could give us a sample. :) 

I hope you have a nice day.

[Rajapandi Ravi]

Hi Soob,

Greetings from Syncfusion support

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 success event. You can also use the below way to achieve your requirement.

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

import * as XLSX from 'xlsx';

 

function onUploadSuccess(args: any): void  { //success event of uploader

        if (args.operation === 'upload') {

          console.log('File uploaded successfully');

          var files = args.file.rawFile; // FileList object

          parseExcel(files);

        }

      }

      function onUploadFailure(args: any): void  {

        console.log('File failed to upload');

      }

      function parseExcel(file) {

        var reader = new FileReader();

        reader.onload = (e)=> {

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

          var workbook = XLSX.read(data, {

            type: 'binary'

          });

          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)

            grid.dataSource = JSON.parse(json_object); //set the json data to the Grid dataSource

          }).bind(this), this);

        };

 

        reader.onerror = function(ex) {

          console.log(ex);

        };

        reader.readAsBinaryString(file);

      };

 

Sample: https://stackblitz.com/edit/flappt?file=index.ts,index.html

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

Regards,

Rajapandi R

[soob]

Your answers were of great help. thank you.

And I got some additional question... 

If my Excel file has a specific cell blank like the image below, 

and I need to read it except for that cell, what specific work should I do?

▲ I just want to read Apple/fruit~ Dasheen/vegetable ( B4:C7 ) area. 

    Except for items(Orange/fruit) marked as examples.

Can I get some help? :^D 

[Pavithra Subramaniyam]

Hi Soob,

We have used the “sheet_to_json” function of the “XLSX” library to convert the Excel sheets into the JSON format, not the Syncfusion library. So You can customize the parsed JSON before assigning it to the Grid only. Please refer to the below code example for more information.

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

 

  // here you can customize the JSON

 

  grid.dataSource = JSON.parse(json_object); //set the json data to the Grid dataSource

 

}).bind(this), this);

 

[Sarath Mohandas]

If there is any images inside any columns? How will the conversion will take place 
 var XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]); for us the column empty.

[Rajapandi Ravi]

Sarath,

Before we start providing solution to your query, we need some more information for our clarification. So only we will provide the exact solution based on your application scenario. So please share the below details that would be helpful for us to provide better solution.

1)              Please share an Excel document which contains your images.

2)              Share your complete Grid rendering code, we would like to check your implementation and Grid settings.

3)              Share your Syncfusion package version.