How to write in existing excel file using angular or node js

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

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

Please help me anyone

Fernando Santucci · Accepted Answer · 2016-07-28 17:40:01Z

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');

edited Jul 28, 2016 at 17:40

answered Jul 28, 2016 at 14:15

Fernando Santucci

4704 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user6589630 Over a year ago

Thanks, But this is everytime created a new .xls / .xlsx . My requirement is to append data into existing micro emabled excel (.xlsm) file. Actually have a excel so i just wanted to write data in it. Please suggest any more ??

Fernando Santucci Over a year ago

Yes, you can! Read the docs. You can open the .xlsm file and update the cells of worksheet of a workbook and write the file. I'll post a code example in my answer above.

Aikon Mogwai · Accepted Answer · 2016-07-28 14:13:15Z

0

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.

answered Jul 28, 2016 at 14:13

Aikon Mogwai

5,2732 gold badges23 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to write in existing excel file using angular or node js

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related