Retrieving an xlsx file using http.get() and then loading said file into ExcelJs in Angular

Question

I'm using HTTP GET to retrieve an xlsx file that I then want to load into an ExcelJs workbook. After which I want to add an additional sheet to the workbook and save it. This part seems to be working but WITHOUT the xlsx template file being part of the downloaded file.

I think I might not be setting the correct responseType, as when I try and load the file into ExcelJs it is empty. I am still able to add my new sheet (sheet2) to the workbook and download it.

First off I suppose am I using the correct response type? Referring to the ExcelJs documentation, when using xlsx.load(), I need to pass an ArrayBuffer, which does not work so then I tried converting it to Uint8Array and Blob, but neither of those work either. Which is why I'm thinking maybe I'm getting the file from the get() incorrectly.

exportLocalXlsx(): void {
    // Retreive the excel template file from the angular app.
    // TODO: Replace local file with webapi URL at a later stage.
     this.http.get('./assets/Book11.xlsx', { responseType: 'arraybuffer' })
      .subscribe((file:ArrayBuffer)  => {       
          // Now prep the excel template file so it can be loaded into Exceljs.
          let xlsxArray = new Uint8Array(file);
          let xlsxBlob = new Blob([xlsxArray.buffer],
                          { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

          console.log(file);
          console.log(xlsxBlob);
          console.log(xlsxArray);

          // Load excel template file into a new Exceljs workbook.
          let wb : Excel.Workbook = new Excel.Workbook();
          ///////////////////////////////////
          // Read from a file.
          //wb.xlsx.readFile('/assets/Book11.xlsx') // Results in corrupt file.    
          ///////////////////////////////////
          // Read from a stream.
          //wb.xlsx.read(file);
          //wb.xlsx.read(xlsxBlob);
          //wb.xlsx.read(xlsxArray);
          ///////////////////////////////////
          // Load from buffer.
          //wb.xlsx.load(file);
          //wb.xlsx.load(xlsxArray.buffer);
          wb.xlsx.load(xlsxBlob);

          // Output sheets to console.
          wb.eachSheet((sheet, id) => {
            sheet.eachRow((row, rowIndex) => {
              console.log(row.values, rowIndex)
            })
          });

          // Import data into the excel template.
          let ws = wb.addWorksheet('Sheet2');

          ws.columns = [{ header: 'Id', key: 'id', width: 10 },
                        { header: 'Name', key: 'name', width: 32 }];

          // Add rows.
          ws.addRow({ id: 1, name: 'John' });
          ws.addRow({ id: 2, name: 'Jane' });

          // Now Download the modified excel template file.
          wb.xlsx.writeBuffer().then(data => {
                    const blob = new Blob([data], {
                        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8' });
                    FileSaver.saveAs(blob, 'Book11');
                });
      });
  }

Here is the source code for the entire angular app: https://stackblitz.com/edit/verifi-exportuploadtest

Any suggestions would be appreciated.

Answer 1

Could you trying to return the response with JSON from workbook.model?

on frontend should work something like that:

    const wb = new ExcelJs.Workbook();
    wb.model = response.data

Answer 2

may be very late to the party, i banged my head literally about this, so posting an answer

let workbook = this.excelService.createWorkBook();

this.http.get('./assets/1.xlsx', { responseType: 'blob' })
.subscribe((file:Blob)  => {    
 
  let fileReader = new FileReader();    
  fileReader.readAsArrayBuffer(file);     
  fileReader.onload = async (e) => {    
    let arrayBuffer = fileReader.result as ArrayBuffer; 
    if(arrayBuffer)   
    await workbook.xlsx.load(arrayBuffer);
    debugger;
    var first_sheet_name = workbook.worksheets.length;

    let worksheet = workbook.addWorksheet('Demo Sridhar');
    workbook.xlsx.writeBuffer().then((data: any) => {
      let blob = new Blob([data], {
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      });
      fs.saveAs(blob, 'Final.xlsx');
    });

Await should be the trick and blob