How to write in existing excel file using angular

Question

I am stuck actually i am working in a mean stack and i have a requirement in my project to write data in existing micros enable validated excel sheet. I do a lot of google but i did't find any node or angular module those are fulfill my requirement. Everyone given me option to create new file no one give me option update existing excel. Its really strange

Below is my requirement step by step

1. I have an micro enable excel (.xlsm)
2. Now i have to open it and write some data in angular or node js.
3. After that threw it to the user for download.

Please help me anyone

Answer 1

The SheetJS API allow you convert Microsoft Excel (.xls / .xlsx) and OpenDocument (.ods) formats to JSON stream or files and import/export from/to MongoDB or MongooseJS. Just study the simple and easy API.

In Github you can find the documentation, tutorials and code examples.

Site: http://sheetjs.com/

Project: https://github.com/SheetJS/js-xlsx

Interactive Demo: http://oss.sheetjs.com/js-xlsx/

Use the code example below, but just before run npm install xlsx and put the chunk of code to open the .xlsm file: var workbook = XLSX.readFile('test.xlsx');.

/* require XLSX */
var XLSX = require('xlsx')

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

function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
    for(var R = 0; R != data.length; ++R) {
        for(var 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;
            var cell = {v: data[R][C] };
            if(cell.v == null) continue;
            var 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 = datenum(cell.v);
            }
            else cell.t = 's';

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

/* original data */
var data = [[1,2,3],[true, false, null, "sheetjs"],["foo","bar",new Date("2014-02-19T14:30Z"), "0.3"], ["baz", null, "qux"]]
var ws_name = "SheetJS";

function Workbook() {
    if(!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);

/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;

/* write file */
XLSX.writeFile(wb, 'test.xlsx');

Answer 2

Currently office file is zip archive with xml files. So, in Node.js you can extract data by e.g. node-zip, changed by e.g. xml2js and zipped back.