# Writing data to an Excel file and composing an email

**URL:** <https://forum.ionicframework.com/t/writing-data-to-an-excel-file-and-composing-an-email/82653>\
**Category:** tutorials\
**Created:** [March 12, 2017, 8:59pm UTC](https://forum.ionicframework.com/t/writing-data-to-an-excel-file-and-composing-an-email/82653 "2017-03-12T20:59:57Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![brickolicious](https://sea1.discourse-cdn.com/ionicframework/user_avatar/forum.ionicframework.com/brickolicious/32/99633_2.png) [@brickolicious](https://forum.ionicframework.com/u/brickolicious)\
**Post date:** [March 12, 2017, 8:59pm UTC](https://forum.ionicframework.com/t/writing-data-to-an-excel-file-and-composing-an-email/82653/1 "2017-03-12T20:59:57Z")

</div>

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”](https://www.npmjs.com/package/ts-xlsx)  
> “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](https://libraries.io/github/SheetJS/js-xls)  
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 🍺 🍻