Node.js and xlsjs or exceljs

Question

I am trying to manipulate an Excel workbook in node using the module xlsjs

https://www.npmjs.com/package/xlsjs

I've also found exceljs

https://www.npmjs.com/package/exceljs

I would like to be able to modify cells and ranges in the work book then save as another workbook.

Is this functionality possible with this module?

Answer 1

Yes it is possible with both of them.

Though i would Recommend using exceljs as it is much easier to use. See this example code

    var Excel = require("exceljs");
    var workbook = new Excel.Workbook();
    workbook.xlsx.readFile(templatePath)
        .then(function() {
            worksheet = workbook.getWorksheet(1);
            worksheet.addRow([3, "Sam", new Date()]);
            workbook.xlsx.writeFile(path)
                .then(function(){
                    helper.openFile(path);
                });
        });

Answer 2

Following is code for xlsjs and csv:

app.component.html

<!--The content below is only a placeholder and can be replaced.-->

<h2>Export as Excel</h2>
<button (click)="SortExcel()">Sort And Download</button><br><br>
<button (click)="GroupExcel()">Group And Download</button>

<h2>Export as CSV</h2>
<button (click)="SortCSV()">Sort And Download</button><br><br>
<button (click)="GroupCSV()">Group And Download</button>

app.component.spec.ts
-------------------------
import { TestBed, async } from '@angular/core/testing';
import { AppComponent } from './app.component';

describe('AppComponent', () => {
  beforeEach(async(() => {
    TestBed.configureTestingModule({
      declarations: [
        AppComponent
      ],
    }).compileComponents();
  }));

  it('should create the app', () => {
    const fixture = TestBed.createComponent(AppComponent);
    const app = fixture.debugElement.componentInstance;
    expect(app).toBeTruthy();
  });

  it(`should have as title 'reports'`, () => {
    const fixture = TestBed.createComponent(AppComponent);
    const app = fixture.debugElement.componentInstance;
    expect(app.title).toEqual('reports');
  });

  it('should render title in a h1 tag', () => {
    const fixture = TestBed.createComponent(AppComponent);
    fixture.detectChanges();
    const compiled = fixture.debugElement.nativeElement;
    expect(compiled.querySelector('h1').textContent).toContain('Welcome to reports!');
  });
});

app.component.ts
--------------------------
import { Component } from '@angular/core';
import { ExcelService } from './excel.service';
import { CSVService } from './csv.service';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent {


  constructor(public excelservice : ExcelService, public csvservice : CSVService) {

  }

    SortExcel() {
      this.excelservice.sortby = "Name";
      this.excelservice.SortAndDownload(0);      
    }


    GroupExcel() {
      this.excelservice.groupby = "Bank";
      this.excelservice.GroupAndDownload();
    }


    SortCSV() {
      this.csvservice.sortby = "Bank";
      this.csvservice.SortAndDownload();
    }


    GroupCSV() {
      this.csvservice.groupby = "Bank";
      this.csvservice.GroupAndDownload();
    }
}

csv.service.ts
---------------------
import { Injectable } from '@angular/core';
import * as FileSaver from 'file-saver';

@Injectable({
  providedIn: 'root'
})
export class CSVService {

  sortby: string;
  groupby: string;
  testdata = [
    { Name: "ABCD", Bank: "CS", AccountType: "Corporate" },
    { Name: "PQRS", Bank: "SBI", AccountType: "Corporate" },
    { Name: "MNOP", Bank: "J,P", AccountType: "Corporate" },
    { Name: "JKLM", Bank: "CS", AccountType: "Individual" },
    { Name: "XYZ", Bank: "CS", AccountType: "Corporate" },
    { Name: "Dan", Bank: "CS", AccountType: "Individual" }
  ];

  constructor() { }

  SortAndDownload() {
    console.log("Start Time " + new Date());
    var csv = "";
    var filename = 'export.csv';
    csv += this.getHeader(this.testdata);
    this.testdata.sort(this.compare.bind(this));
    csv += this.getrows(this.testdata) + "\n";

    this.exportFile(csv, filename);
    console.log("End Time " + new Date());
  }






  private exportFile(data: string, filename: string) {
    var BOM = "\uFEFF";
    const blob = new Blob([BOM + data], { type: 'text/csv;charset=utf-8' });
    FileSaver.saveAs(blob, filename);
  }



  private compare(a, b) {
    if (a[this.sortby] < b[this.sortby])
      return -1;
    if (a[this.sortby] > b[this.sortby])
      return 1;
    return 0;
  }



  ///////Get the List of Attributes from Data
  private getHeader(data) {
    if (data == null || !data.length) {
      return null;
    }

    let columnDelimiter = ',';
    let lineDelimiter = '\n';

    let keys = Object.keys(data[0]);
    for (var i = 0; i < keys.length; i++) {
      if (keys[i].indexOf('"') != -1) {
        keys[i] = keys[i].replace(/"/g, '""');
      }
    }
    let result = '';

    result += '"';
    for (var i = 0; i < keys.length; i++) {
      if (i > 0) result += columnDelimiter;
      result += "'" + keys[i] + "'";
    }
    result += '"';

    result += lineDelimiter;
    return result;
  }





  GroupAndDownload() {
    var csv = "";
    var filename = 'export.csv';
    csv += this.getHeader(this.testdata);
    csv += this.group_by(this.testdata);
    this.exportFile(csv, filename);

  }




  //////Convert All Object Specified in Array into CSV Format
  private getrows(data) {
    if (data == null || !data.length) {
      return null;
    }
    let columnDelimiter = ',';
    let lineDelimiter = '\n';

    let keys = Object.keys(data[0]);
    let result = '';

    data.forEach(function (item) {
      let ctr = 0;
      result += '"';
      keys.forEach(function (key) {
        if (ctr > 0) result += columnDelimiter;

        if (item[key] != undefined && item[key].indexOf('"') != -1) {
          item[key] = item[key].replace(/"/g, '""');
        }
        result += "'" + item[key] + "'";
        ctr++;
      });
      result += '"';
      result += lineDelimiter;
    });

    return result;
  }




  /////Return CSV Data Grouped By Specified Attribute
  private group_by(data) {

    let set = new Set();
    var items = [];
    var itr = 0;
    var res = "";

    data.forEach(function (item) {
      set.add(item[this.groupby]);
    }.bind(this));

    set.forEach(function (val1) {
      var temp = [];
      var cnt = 0;

      data.forEach(function (item) {
        if (item[this.groupby] === val1) {
          temp[cnt] = item;
          cnt++;
        }
      }.bind(this));

      items[itr] = temp;
      itr++;
    }.bind(this));

    itr = 0;
    var tem1 = this;
    set.forEach(function (val1) {
      if (val1.indexOf('"') != -1) {
        val1 = val1.replace(/"/g, '""');
      }
      res += "\"'" + val1 + "'\"\n";
      var tmp = items[itr];
      console.log(tmp);
      // res += this.getrows(items[itr++]);
      res += tem1.getrows(tmp);
      itr++;

    }.bind(this));
    return res;
  }

}

excel.service.ts
-----------------------------
import { Injectable } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';


const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';


@Injectable({
  providedIn: 'root'
})
export class ExcelService {

  sortby: string;
  groupby: string;
  testdata = [
    { Name: "ABCD", Bank: "CS", AccountType: "Corporate", Balance:5000 },
    { Name: "PQRS", Bank: "SBI", AccountType: "Corporate", Balance:2000 },
    { Name: "MNOP", Bank: "J,P", AccountType: "Corporate", Balance:9956 },
    { Name: "JKLM", Bank: "CS", AccountType: "Individual", Balance:4521 },
    { Name: "XYZ", Bank: "CS", AccountType: "Corporate", Balance:45201 },
    { Name: "Dan", Bank: "CS", AccountType: "Individual", Balance:10023 }
  ];

  constructor() { }


  GroupAndDownload() {
    var csv;
    var filename = 'export.csv';
    let data = this.GroupBy();
    this.exportFile(data,filename);
  }



  private GroupBy() {
    let res=[];
    let set = new Set();
    let map = new Map();
    var key = Object.keys(this.testdata[0])[0];

    this.testdata.forEach(function (item) {
      set.add(item[this.groupby]);
    }.bind(this));

    this.testdata.forEach(function (item) {
      let key = item[this.groupby];
      if(map.has(key)) {
        map.get(key).push(item);
      } else {
        let arr = [];
        arr.push(item);
        map.set(key,arr);
      }
    }.bind(this));

    set.forEach(function (val) {
      let header={};
      header[key] = val;
      res.push(header);  
      res = res.concat(map.get(val))
    });
    return res;
  }




  SortAndDownload(ascending) {
    var csv = "";
    var filename = 'export';
    var data = this.testdata;
    if(ascending==0) {
      data.sort(this.compare.bind(this));
    } else if(ascending==1) {
      data.sort(this.compare1.bind(this));
    }

    this.exportFile(data, filename);
  }


  ////Comparator for Ascending Order
  private compare(a, b) {
    if (a[this.sortby] < b[this.sortby])
      return -1;
    if (a[this.sortby] > b[this.sortby])
      return 1;
    return 0;
  }



  ////Comparator for Descending Order
  private compare1(a, b) {
    if (a[this.sortby] > b[this.sortby])
      return -1;
    if (a[this.sortby] < b[this.sortby])
      return 1;
    return 0;
  }


  private exportFile(data:any[], filename:string) {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(data);
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });

    const blobdata: Blob = new Blob([excelBuffer], {
      type: EXCEL_TYPE
    });
    FileSaver.saveAs(blobdata, filename + EXCEL_EXTENSION);

  }
}