How to create a Excel file in Ionic apps using PouchDB

In Short:

You will learn how to create a Excel file based on data from PouchDB.

Pre-Condition:

For creating a PouchDB instance and inserting data, check out the following tutorial:

Ionic 5 Background Geolocation Tracking with PouchDB

Step 1Create a new Ionic project. npm install -g ionic@latestionic start ionic4-background-geo-pouchdb blank Step 2Install plugin and dependencies. // geolocation trackingionic cordova plugin add @mauron85/cordova-plugin-background-geolocationnpm install --save @ionic-native/background-geolo…

The Awesome Code BlogDavid Koller

How it works

Step 1
Create an ionic angular app or use an existing one. For creating a new one you can use the "tabs" template.

ionic start myApp tabs

Step 2
Install the following dependencies

npm install xlsx --save

ionic cordova plugin add cordova-plugin-file
npm install @ionic-native/file

Step 3
Import the dependencies installed before and create a global pdfMake object

import {File} from '@ionic-native/file/ngx';

@NgModule({
    ...
    providers: [
    	File,...
	]
})

import * as XLSX from 'xlsx';
import { File } from '@ionic-native/file/ngx';
import {CouchApiService} from '../providers/couch-api.service';

...
export class OurPage {

  constructor(private couchApiService: CouchApiService,
              private file: File) {
  }
}

Step 4
Create a method for a PDF layout. For displaying the stored data of our PouchDB a HTML table is used.

The stored data will be added to the template in an additional method (next step).

 async createXLS(){
 	this.couchApiService.readExcel('your_database_name')
          .then((addresses: any) => {
            let sheet = XLSX.utils.json_to_sheet(addresses);
            let wb = {
              SheetNames: ["your_name"],
              Sheets: {
                "your_name": sheet
              }
            };

            let wbout = XLSX.write(wb, {
              bookType: 'xlsx',
              bookSST: false,
              type: 'binary'
            });

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

            let blob = new Blob([s2ab(wbout)], {type: 'application/octet-stream'});
            let self = this;
            this.getStoragePath().then( (url) => 
            	{
              		self.file.writeFile(
              		url, 'myfile.xlsx', 
              		blob, {replace: true
              	}
              )
             	.then(() => {
                      console.log("Export finished and file saved");
             	}).catch((error) => {
                    console.log(error);
             	});
            }).catch((error) => {
              console.log(error);
            });
          })
          .catch((error) => {
            console.log(error);
          });
}

Step 5
In the steps before the "getStoragePath" method was used for receiving the file path, where the file will be persisted.

    private getStoragePath() {
        const file = this.file;
        return this.file.resolveDirectoryUrl(this.file.dataDirectory)
        .then( (directoryEntry) => {
        	return file.getDirectory(
            	directoryEntry, 
            	'our_root_folder', 				
                {
                    create: true,
                    exclusive: false
                })
            .then( () => {
                return directoryEntry.nativeURL + 'our_root_folder/';
            });
        });
    }

Step 6
Finally lets create a button for trigger the export function

<button class="btn btn-primary my-4" type="button" (click)="createXLS()">
      Create Excel
</button>
   

Step 7 (Optional)
The CouchAPI Service is explained in this tutorial:
https://blog.appit-online.de/background-geolocation-tracking-with-ionic-4/

The "readExcel" method is defined as follows and is transforming the database object to human readable labels.

private data;

 readExcel(name) {
        const db = new PouchDB(name + '.db', {auto_compaction: true, adapter: 'cordova-sqlite', iosDatabaseLocation: 'Library',
            androidDatabaseImplementation: 2});

        return new Promise((resolve, reject) => {
            db.allDocs({
                include_docs: true
            }).then((result) => {

                this.data = [];
                result.rows.map((row) => {
                    if (!row.doc.purposeName) {
                        row.doc.purposeName = '';
                    }

                    this.data.push(
                        {
                            Start_Date: new Date(row.doc.sTime).toString(),
                            Travel_Purpose: row.doc.purpose + '  ' + row.doc.purposeName,
                            Start_Kilometer: row.doc.startKm
                            
                        }
                    );
                });

                resolve(this.data);
            }).catch((error) => {
                console.log(error);
                reject(error);
            });
        });
    }