how to set formula for cell data for (export to .xlsx) SheetJS js-xlsx: https://github.com/SheetJS/js-xlsx

Question

Refering this example https://github.com/SheetJS/js-xlsx/blob/master/tests/write.js , it doesn't have any implementation for cell formula in xlsx spreadsheet I tried using cell.f = "=SUM(A1+B1)" for the cell C1 and cell.v as the summation value which was 3. But i didn't succeed. With the exported file, when opened in MS excel, the cell contained just the data and when selected, didn't show any formula which i assigned in f(x) field.

Can someone post me a example which actually uses the functions/property '.f' and 'cellFormula'

Will be very helpful. I just need a working example with static values.

Answer 1

The cell object has the property f, which is the formula you want to use. Here you can see all the options: https://github.com/SheetJS/js-xlsx#cell-object.

And here is an example of the use of formula:

var xlsx = require('xlsx');

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

var exportBook = new Workbook();

var worksheet = {};

var cell = {f: 'A2+A3'};

var cellRef = xlsx.utils.encode_cell({r:0, c:0});

var range = {s:{r: 0, c: 0},
            e: {r: 10, c: 10}};



worksheet[cellRef] = cell;
worksheet['!ref'] = xlsx.utils.encode_range(range);

exportBook.SheetNames.push('test');
exportBook.Sheets.test = worksheet;


xlsx.writeFile(exportBook, 'formula sample.xlsx');

Here, A1 should have the formula A2+A3.

Hope it helps :)