Writing data to an Excel file and composing an email

Ive been looking around how to write data to an excel file and could’nt find any examples.
So for anyone trying to do the same.

Here’s my solution.

Dependencies

“ts-xlsx”: "0.0.9"
“cordova-plugin-email”,
“cordova-plugin-file”,

Page

   declare var cordova
    fs: string = cordova.file.externalDataDirectory;
    this.orderService.orderToExcelDataStructure(this.order).then((rows) => {

    this.excelService.createXSLX(rows).then((x) => {
    console.log('Excel blob: ', x);

    let time = new Date().getTime();
    let fileName: string = "myApp-" + time + "-" + this.order.store + ".xlsx";
    File.writeFile(this.fs, fileName, x, { create: true }).then(f => {
      console.log('Returned from writing file: ', f);

      let fp = this.fs + fileName;

      let email = {
        to: Config.defaultEmail,
        attachments: [fp],
        subject: 'Brickolicious - ' + this.order.store,
        body: '<h1>Order</h1>',
        isHtml: true
      };
      EmailComposer.open(email)

        .then(() => { this.showDone = true; })
        .catch(() => {

          let toast = this.toastCtrl.create({
            message: 'Could not open email composer',
            duration: 3000
          });
          toast.present();

        });


    }).catch(err => {
      console.log('Error when writing file: ', err);
    });


  });


});

The ‘orderToExcelDataStructure’ function returns the rows to be written to the excel file
This is an array of arrays => [[‘Head1’,‘Head2’,‘Head3’],[‘val1’,‘val2’,‘val3’]]…

CreateXLSX function (The good parts)

import { Injectable } from '@angular/core';
declare var cordova: any;
// const fs:string = cordova.file.dataDirectory;
const fs: string = cordova.file.externalDataDirectory;
import * as XLSX from 'ts-xlsx';

@Injectable()
export class ExcelService {

  sheetNames: string[] = []
  sheets: any;
  constructor() { }

  createXSLX(data:any): Promise<any> {

    return new Promise((resolve) => {
      let ws_name = "OrderDetails";

      let wb: XLSX.IWorkBook = {
        SheetNames: [],
        Sheets: {},
        Props: {}

      };
      let ws = this.sheet_from_array_of_arrays(data, {});

      /* add worksheet to workbook */
      wb.SheetNames.push(ws_name);
      wb.Sheets[ws_name] = ws;
      let wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'binary' });


      let xslxBlob = new Blob([this.s2ab(wbout)],{type:"application/octet-stream"});
      resolve(xslxBlob);
    });
  }


  datenum(v, date1904): any {
    if (date1904) v += 1462;
    let epoch: any = Date.parse(v);
    return (epoch - new Date(Date.UTC(1899, 11, 30)).getTime()) / (24 * 60 * 60 * 1000);
  }

  sheet_from_array_of_arrays(data, opts) {
    let ws = {};
    let range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
    for (let R = 0; R != data.length; ++R) {
      for (let C = 0; C != data[R].length; ++C) {
        if (range.s.r > R) range.s.r = R;
        if (range.s.c > C) range.s.c = C;
        if (range.e.r < R) range.e.r = R;
        if (range.e.c < C) range.e.c = C;
        let cell: any = { v: data[R][C] };
        if (cell.v == null) continue;
        let cell_ref = XLSX.utils.encode_cell({ c: C, r: R });

        if (typeof cell.v === 'number') cell.t = 'n';
        else if (typeof cell.v === 'boolean') cell.t = 'b';
        else if (cell.v instanceof Date) {
          cell.t = 'n';
          //cell.z = XLSX.SSF._table[14];
          cell.v = this.datenum(cell.v, null);
        }
        else cell.t = 's';

        ws[cell_ref] = cell;
      }
    }
    if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range.s, range.e);
    return ws;
  }

  s2ab(s) {
    let buf = new ArrayBuffer(s.length);
    let view = new Uint8Array(buf);
    for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
  }



}

The datenum, sheet_from_array_of_arrays and the s2ab functions are plucked from the SheetJS examples
Note: The dependency ts-xlsx is a TS wrapper for the plain JS sheetJS library

With the createXLSX function a workbook is generated and a Blob is made.

Then with the File plugin i write the Blob to the File system (Getting the correct file path can be a bit of fiddling)

Note that when passing the location of the file to the Email composer by taking the cordova path and appending the filename to it.

Hope this helps :beer: :beers: