¿Cómo puedo guardar el resultado en una variable correctamente? XLSX Angular

Aug 19 2020

Estoy tratando de leer un archivo de Excel de una entrada con Angular y transformar los datos de ese archivo en una matriz de objetos. Mis componentes son así:

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

Obtuve un servicio para usarlo desde otro 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);
  }
}

Y luego mi 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>

Utilizo muchos "console.log ()" para ver lo que obtengo, hasta ahora puedo transformar ese archivo en un objeto JS, pero cuando necesito usar el resultado, la variable donde guardo el resultado no está definida, Supongo que esto sucede porque el proceso es asincrónico, lo intenté con async / await, pero no funcionó. El objetivo es tomar esta matriz de objetos y enviarla al backend y luego a una base de datos.

Respuestas

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

}

utilizar exceljs. Puede ver que uso mis datos asíncronos para crear un archivo de Excel