Come posso salvare correttamente il risultato in una variabile? XLSX angolare

Aug 19 2020

Sto cercando di leggere un file excel da un input con Angular e trasformare i dati di quel file in un array di oggetti. I miei componenti sono così:

import * as XLSX from 'xlsx';
import { Injectable } from '@angular/core';

@Injectable({ providedIn:'root' })
export class ExcelService {

    arrayBuffer: any;

    constructor() {}

    xlsxToJSON(event) {
        let arreglo;   
        let fileReader = new FileReader();    
        fileReader.readAsArrayBuffer(event);
        fileReader.onload = async(e)  => {    
            this.arrayBuffer = fileReader.result;    
            let data = new Uint8Array(this.arrayBuffer);    
            let arr = new Array();    
            for(let i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);    
            let bstr = arr.join("");    
            let workbook = XLSX.read(bstr, {type:"binary"});    
            let first_sheet_name = workbook.SheetNames[0];    
            let worksheet = workbook.Sheets[first_sheet_name];    
            // console.log(XLSX.utils.sheet_to_json(worksheet,{raw:true}));
            // arraylist es el JSON después de tratar la data del excel
            let arraylist = XLSX.utils.sheet_to_json(worksheet,{raw:true});
            arreglo = arraylist;
            console.log('El arraylist'+ arraylist);
            console.log(typeof arraylist);
            console.log('Excel Service: '+arraylist);
            console.log(arreglo);
            return arraylist;
        }
        console.log('ULTIMO LOG'+arreglo);
    } 
}

Ho un servizio per usarlo da un altro componente:

import { Component, OnInit } from '@angular/core';
import { Router } from '@angular/router';
import { ExcelService } from '../services/excel.service';

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

  file: File;

  ngOnInit() {}
  
  async importExcel (event) {
    this.file = (event.target as HTMLInputElement).files[0]; 
    const result = await this.excelService.xlsxToJSON(this.file);
    console.log('Result: '+result);
  }
}

E poi il mio componente HTML:

<div class="container">
  <div class="row">
    <div class="col-md-12 form-group">        
      <input 
        type="file" 
        class="form-control" 
        (change)="importExcel($event)" 
        placeholder="Upload file" 
        accept=".csv,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">        
    </div>
  </div>
</div>

Uso molti "console.log ()" per vedere cosa sto ottenendo, fino ad ora posso trasformare quel file in un oggetto JS, ma quando ho bisogno di usare il risultato, la variabile in cui salvo il risultato è indefinita, io Immagino che questo accada perché il processo è asincrono, ho provato con async / await, ma non ha funzionato. L'obiettivo è prendere questo array di oggetti e inviarlo al backend e quindi a un database.

Risposte

Noname Aug 19 2020 at 11:41
import { Component, OnInit } from '@angular/core';
import { DataService } from '../../services/data.service';
import * as jspdf from 'jspdf';
import html2canvas from 'html2canvas';
// import * as XLSX from 'xlsx';

import * as ExcelJS from 'exceljs';
import * as FileSaver from 'file-saver';

import { ViewChild, ElementRef } from '@angular/core';

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

  purchases: any;
  constructor(private dataService: DataService) {
    this.GetPurchases();
  }

  ngOnInit(): void {
  }

  async GetPurchases() {
    const response = await this.dataService.GetPurchases();
    const dataService = await response.json();
    this.purchases = dataService;
  }


  downloadExcel() {

    // using instead of Date.now()
    const date = new Date().toISOString().slice(0, 10).split('-').reverse().join('/');
    console.log(date);

    // get help from here for excel file export using excelJS with alignment
    // in your tsconfig.app.json u must use "types": ["node"] if u use exceljs
    // https://stackoverflow.com/questions/62127452/angular-cant-export-excel-using-exceljs-error-ts2307-cannot-find-module-s/62128182?noredirect=1#comment109909862_62128182

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('My Sheet');

    worksheet.columns = [
      { header: 'Id', key: 'id', width: 10},
      { header: 'Name', key: 'name', width: 32 },
      { header: 'Quantity', key: 'quantity', width: 15 },
      { header: 'Rate', key: 'rate', width: 15 },
      { header: 'Date', key: 'date', width: 15 },
      { header: 'Total', key: 'total', width: 15 }
    ];

    // get help from here for excel cell alignment
    // https://openbase.io/js/exceljs#alignment


    for (const purchase of this.purchases) {
      worksheet.addRow({
        id: purchase.item_id ,
        date: purchase.item_purchase_date.toString().slice(0, 10).split('-').reverse().join('/'),
        name: purchase.item_name,
        quantity: purchase.item_quantity,
        rate: purchase.item_rate,
        total: purchase.item_rate * purchase.item_quantity
       })
      .alignment = { horizontal: 'left' };
    }

    worksheet.getRow(1).font = { bold: true };

    // get help from here
    // https://stackoverflow.com/questions/62149358/exceljs-iterate-each-cell-of-each-row-and-column/62149808#62149808
    worksheet.columns.forEach(column => {
      // for each non empty cell
      column.eachCell((cell, rowNumber) => {
        cell.border = {
          top: { style: 'thick' },
          left: { style: 'thick' },
          bottom: { style: 'thick' },
          right: { style: 'thick' }
        };
      });
    });





    // save under export.xlsx, dont use writeFile see the above stackoverflow question
    // await workbook.xlsx.writeFile('export.xlsx');
    // await maybe optional here
    workbook.xlsx.writeBuffer()
      .then(buffer => FileSaver.saveAs(new Blob([buffer]), `${date}_feedback.xlsx`))
      .catch(err => console.log('Error writing excel export', err));

}

utilizzare exceljs. Puoi vedere che uso i miei dati asincroni per creare file Excel