Sheetjs: Add new data in sheet

[upasana-shah]

Problem is - Read a xls file via sheetjs and after reading need to append data to it and write the workbook as physical file.
Not able to find any example, where new rows and columns are added to existing sheet.

Is there any way to achieve this result?

[zyuhel]

Yes, it isn't well documented, but it is possible.
First you need to update sheet !ref property
if you cells are out of !ref range, they won't be processed.
After that you need to pass object with t,v,s fields to chosen cell
sheet[desired_cell]={t:'s',v:'test'};
I would suggest using Protobi's fork, because it supports styling.

[mca22120]

Hello, could you explain how can I add new values in an existing file by using this code ? Is the problem in this line : var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }}; ?

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

[reviewher]

At a high level, you need to insert cell objects and update the worksheet range. Here's a simple function to add a cell to a worksheet:

function add_cell_to_sheet(worksheet, address, value) {
    /* cell object */
    var cell = {t:'?', v:value};

    /* assign type */
    if(typeof value == "string") cell.t = 's'; // string
    else if(typeof value == "number") cell.t = 'n'; // number
    else if(value === true || value === false) cell.t = 'b'; // boolean
    else if(value instanceof Date) cell.t = 'd';
    else throw new Error("cannot store value");

    /* add to worksheet, overwriting a cell if it exists */
    worksheet[address] = cell;

    /* find the cell range */
    var range = XLSX.utils.decode_range(worksheet['!ref']);
    var addr = XLSX.utils.decode_cell(address);

    /* extend the range to include the new cell */
    if(range.s.c > addr.c) range.s.c = addr.c;
    if(range.s.r > addr.r) range.s.r = addr.r;
    if(range.e.c < addr.c) range.e.c = addr.c;
    if(range.e.r < addr.r) range.e.r = addr.r;

    /* update range */
    worksheet['!ref'] = XLSX.utils.encode_range(range);
}

For example, using the write demo to generate sheetjs.xlsx, here's how you would set F6 to 12345:

var XLSX = require('xlsx');
var wb = XLSX.readFile('sheetjs.xlsx');
add_cell_to_sheet(wb.Sheets.SheetJS, "F6", 12345);
XLSX.writeFile('sheetjs-new.xlsx', wb);

[hiob10hiob]

@reviewher `s comment is perfect!
Why is this function not part of the xlsx module?