Angular 4: How to read data from Excel?

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

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.

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.

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.

 readfile() { // You can change the file path in the assets folder let url = "/assets/template.xlsx"; let req = new XMLHttpRequest(); req.open("GET", url, true); req.responseType = "arraybuffer"; req.onload = (e) => { let data = new Uint8Array(req.response); let workbook = XLSX.read(data, {type: "array"}); const excelBuffer: any = XLSX.write(workbook, {bookType: 'xlsx', type: 'array'}); // TO export the excel file this.saveAsExcelFile(excelBuffer, 'X'); }; req.send(); } 

You can take this code and change the path to your file; for the last line is for export it. To know more about it i suggest to visit the official doc of js-xlsx https://github.com/SheetJS/js-xlsx

/*** input in html and scope angular js */ <input type='file' ng-upload-change="uploadData" my-id="$index" ng-model='item.archivo' class="col-sm-12" base-sixty-four-input > $scope.uploadData = function($event) { var files = $event.target.files; if (files.length > 0) { var fileToLoad = files[0]; var fileReader = new FileReader(); var base64File; // Reading file content when it's loaded fileReader.onload = function(event) { base64File = event.target.result; //here your post save file-base64 console.log(base64File) }; // Convert data to base64 fileReader.readAsDataURL(fileToLoad); } } 

Using promise and raw format

import * as XLSX from 'xlsx'; type InfoSheetExcel = { sheetName: string, items: any[], sizeColumns?: number[], }; export async function importExcel(file: File) { let strData = await new Promise<string | ArrayBuffer>((resolve) => { const reader: FileReader = new FileReader(); reader.onload = (evt) => { let readerResult = evt.target.result; resolve(readerResult); }; reader.onerror = (evt) => { resolve(null); }; reader.readAsArrayBuffer(file); }); let result: InfoSheetExcel[] = []; if (!!strData) { const workbook: XLSX.WorkBook = XLSX.read(strData, { type: 'array' }); workbook.SheetNames.forEach(sheetName => { const sheet = workbook.Sheets[sheetName]; const data: any = <XLSX.AOA2SheetOpts>(XLSX.utils.sheet_to_json(sheet, { raw: true })); result.push({ sheetName, items: data }); }); } console.log('DATA EXCEL', result); return result; } 

Example of use:

async onOpenExcel(evt: any, fileInput: any) { let files: FileList = evt.target.files; let data = await importExcel(files.item(0)); fileInput.value = null; } 

File:

Output: