Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Angular 4: How to read data from Excel?

I am not able to get data from an Excel sheet in Angular 4. Below is my code sample.

HTML code:

<input id="my-file-selector" type="file" (change)="uploadData($event)" name="uploadExcel">

upload.component.ts:

public uploadData(event: any) : void { 
    // get data from file upload       
    let filesData = event.target.files;
    console.log(filesData[0]);
}
like image 540
alok kumar Avatar asked Nov 07 '17 06:11

alok kumar


People also ask

How do I read a .xlsx file in typescript?

steps to read a value from Excel : // create object for workbook let wb:Workbook = new Workbook(); From the Workbook object, we have to use readFile along with file type, here xlsx is file type. Create then block to resolve the promise to get values of the workbook.


4 Answers

You should follow these 3 steps

step 1: import ts-xlsx refer: https://www.npmjs.com/package/ts-xlsx for installation

step 2: Using FileReader convert to arraybuffer

step 3: Reading the arraybuffer with XLSX and converting as workbook

HTML CODE

<input type="file" style="display: inline-block;" (change)="incomingfile($event)" placeholder="Upload file" accept=".xlsx">
<button type="button" class="btn btn-info" (click)="Upload()" >Upload</button>

Typescript

//import it

    import * as XLSX from 'ts-xlsx';

//inside export class

arrayBuffer:any;
file:File;
incomingfile(event) 
  {
  this.file= event.target.files[0]; 
  }

 Upload() {
      let fileReader = new FileReader();
        fileReader.onload = (e) => {
            this.arrayBuffer = fileReader.result;
            var data = new Uint8Array(this.arrayBuffer);
            var arr = new Array();
            for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
            var bstr = arr.join("");
            var workbook = XLSX.read(bstr, {type:"binary"});
            var first_sheet_name = workbook.SheetNames[0];
            var worksheet = workbook.Sheets[first_sheet_name];
            console.log(XLSX.utils.sheet_to_json(worksheet,{raw:true}));
        }
        fileReader.readAsArrayBuffer(this.file);
}
like image 100
Prabhu Anand Avatar answered Oct 17 '22 20:10

Prabhu Anand


You can use SheetJs/xlsx package from npm to get the data from excel as a json object in Angular / Ionic.

Just follow these steps:

1) npm install --save xlsx

2) Now in your component file import xlsx

import * as XLSX from 'xlsx';

3) Now attach this function in the change event of input tag of type file

onFileChange(event: any) {
    /* wire up file reader */
    const target: DataTransfer = <DataTransfer>(event.target);
    if (target.files.length !== 1) {
      throw new Error('Cannot use multiple files');
    }
    const reader: FileReader = new FileReader();
    reader.readAsBinaryString(target.files[0]);
    reader.onload = (e: any) => {
      /* create workbook */
      const binarystr: string = e.target.result;
      const wb: XLSX.WorkBook = XLSX.read(binarystr, { type: 'binary' });

      /* selected the first sheet */
      const wsname: string = wb.SheetNames[0];
      const ws: XLSX.WorkSheet = wb.Sheets[wsname];

      /* save data */
      const data = XLSX.utils.sheet_to_json(ws); // to get 2d array pass 2nd parameter as object {header: 1}
      console.log(data); // Data will be logged in array format containing objects
    };
 }

You can also refer to these utils method present in xlsx to perform different operation according to your need.

https://github.com/SheetJS/sheetjs#utility-functions

And also during read operation you can pass these parsing options inside object

https://github.com/SheetJS/sheetjs#parsing-options

For any other information refer to the doc

https://github.com/SheetJS/sheetjs

Hope this will help you or somebody else.

like image 20
Aman Kumar Gupta Avatar answered Oct 17 '22 20:10

Aman Kumar Gupta


This package has been deprecated: https://www.npmjs.com/package/ts-xlsx

Use https://github.com/SheetJS/js-xlsx/.

And TypeScript or Angular 5: https://github.com/SheetJS/js-xlsx/tree/master/demos/typescript.

With import * as XLSX from 'xlsx';

Then use the steps in the answer, and it works perfectly.

like image 23
Rattios Technology Avatar answered Oct 17 '22 22:10

Rattios Technology


I've tried the file upload and below is my steps and result with both data and header,

This will also support multiple sheet within the excel sheet,

1.npm install --save xlsx
    
2.import * as XLSX from 'xlsx';

3.HTML Code:

<input type="file" (change)="onFileChange($event)">

4.Angular Typescript:

  exceltoJson = {};

  onFileChange(event: any) {
    this.exceltoJson = {};
    let headerJson = {};
    /* wire up file reader */
    const target: DataTransfer = <DataTransfer>(event.target);
    // if (target.files.length !== 1) {
    //   throw new Error('Cannot use multiple files');
    // }
    const reader: FileReader = new FileReader();
    reader.readAsBinaryString(target.files[0]);
    console.log("filename", target.files[0].name);
    this.exceltoJson['filename'] = target.files[0].name;
    reader.onload = (e: any) => {
      /* create workbook */
      const binarystr: string = e.target.result;
      const wb: XLSX.WorkBook = XLSX.read(binarystr, { type: 'binary' });
      for (var i = 0; i < wb.SheetNames.length; ++i) {
        const wsname: string = wb.SheetNames[i];
        const ws: XLSX.WorkSheet = wb.Sheets[wsname];
        const data = XLSX.utils.sheet_to_json(ws); // to get 2d array pass 2nd parameter as object {header: 1}
        this.exceltoJson[`sheet${i + 1}`] = data;
        const headers = this.get_header_row(ws);
        headerJson[`header${i + 1}`] = headers;
        //  console.log("json",headers)
      }
      this.exceltoJson['headers'] = headerJson;
      console.log(this.exceltoJson);
    };
  }

  get_header_row(sheet) {
    var headers = [];
    var range = XLSX.utils.decode_range(sheet['!ref']);
    var C, R = range.s.r; /* start in the first row */
    /* walk every column in the range */
    for (C = range.s.c; C <= range.e.c; ++C) {
      var cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* find the cell in the first row */
      // console.log("cell",cell)
      var hdr = "UNKNOWN " + C; // <-- replace with your desired default 
      if (cell && cell.t) {
        hdr = XLSX.utils.format_cell(cell);
        headers.push(hdr);
      }
    }
    return headers;
  }

5.Result
{filename: "uploadedexcel.xlsx", sheet1: Array(212), sheet2: Array(8), headers: {…}}

Results holds the uploaded excel name, data in the sheet1 and sheet2 and also header in the sheet1 and sheet2.

The uploaded excel sheets has sheet1 and sheet2.

like image 4
Rohinibabu Avatar answered Oct 17 '22 21:10

Rohinibabu