Writing data to an Excel file and composing an email

tutorials
1

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: