Angular / Typescript Reading Excel File from url

Question

I need my app to able to read a prepared excel file (located in my domain (www.***.com/data.xlsx)) into an array. The followings code works like charm for the purpose but its client based where user browse and input for the xlsx file.

import {
  Component,
  OnInit
} from '@angular/core';
import * as XLSX from 'xlsx';

@Component({
  selector: 'app-excelsheet',
  templateUrl: './excelsheet.component.html',
  styleUrls: ['./excelsheet.component.css']
})
export class ExcelsheetComponent implements OnInit {

  data: [][];
  constructor() {}

  ngOnInit(): void {}
  onFileChange(evt: any) {
    const target: DataTransfer = <DataTransfer>(evt.target);

    if (target.files.length !== 1) throw new Error('Cannot use multiple files');

    const reader: FileReader = new FileReader();

    reader.onload = (e: any) => {
      const bstr: string = e.target.result;
      const wb: XLSX.WorkBook = XLSX.read(bstr, {
        type: 'binary'
      });
      const wsname: string = wb.SheetNames[0];
      const ws: XLSX.WorkSheet = wb.Sheets[wsname];

      console.log(ws);

      this.data = (XLSX.utils.sheet_to_json(ws, {
        header: 1
      }));

      console.log(this.data);

      let x = this.data.slice(1);
      console.log(x);
    };

    reader.readAsBinaryString(target.files[0]);
  }
}

I tried various ways through trial and errors, testing the functions and variables, other libraries, read about client/server based but still now I'm kinda in dead end, maybe because I have less experience into JavaScript.

For Short, How can my app read the excel file(from a URL) to an array everytime the app started?

For extra, I also tried inserting the excel file inside the app directory, but still...

I know I should have put my code attempt for you guys to troubleshoot but I will just think its just a weird attempts as I'm really new. I hope you guys can briefly list what I should use and do, thanks in advance, I appreciate it.

A more conventional approach would be to place this data in a database. Failing that, why not export the xlsx as CSV and use that instead? Much easier to consume. — spender
– spender, Commented Oct 6, 2020 at 9:45
@spender the reason on not using database is because im only going to read the file. i dont really mind to use csv, i mean if csv is easier to import either through url or local app directory, its a definitely choice for me at the moment. is it easier? anyway thanks for your comment , appreciate it. — DoubleLiu
– DoubleLiu, Commented Oct 6, 2020 at 9:58

DoubleLiu · Accepted Answer · 2020-10-07 01:15:22Z

After more research and testing, i finally did it using the same js-xlsx. I hope this helps the one troubling in the future.

import * as XLSX from 'xlsx';
@Component({
  selector: 'app-home',
  templateUrl: 'home.page.html',
  styleUrls: ['home.page.scss'],
})
export class HomePage {

  constructor() {}

  load(){
    
  var url = "http://ururl.com/excelfile.xlsx";
  var oReq = new XMLHttpRequest();
  oReq.open("GET", url, true);
  oReq.responseType = "arraybuffer";

  oReq.onload = function(e) {
  var arraybuffer = oReq.response;

  /* convert data to binary string */
  var data = new Uint8Array(arraybuffer);
  var arr = new Array();
  for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
  var bstr = arr.join("");

  /* Call XLSX */
  var workbook = XLSX.read(bstr, {type:"binary"});

  /* DO SOMETHING WITH workbook HERE */
  var first_sheet_name = workbook.SheetNames[0];
  /* Get worksheet */
  var worksheet = workbook.Sheets[first_sheet_name];
  let x = XLSX.utils.sheet_to_json(worksheet,{raw:true});
  console.log(x);
  
}

oReq.send();
  }
}

Collectives™ on Stack Overflow

Angular / Typescript Reading Excel File from url

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related