How to generate html with formula calculated ?

[emmanueladk]

This will require the following scripts to achieve this :

• Hyperformula
• XLSX
• xslx-calc

Once sheetjs convert x-spreadsheet into object, use XSLX_CALC to force calculation of the formula present in your workbook. With the method sheet_to_html, it generate a html table you can use for anything.

const { xtos, stox } = require('./xs');
const XLSX = require('xlsx');
const { HyperFormula } = require('hyperformula');
var XLSX_CALC = require('xlsx-calc');

// load your calc functions lib
var formulajs = require('@formulajs/formulajs');

// import your calc functions lib
XLSX_CALC.import_functions(formulajs);

var data = [
    { "name": "sheet2", "freeze": "A1", "styles": [], "merges": [], "rows": { "0": { "cells": { "0": { "text": "noms" }, "1": { "text": "value" } } }, "1": { "cells": { "0": { "text": "titre a" }, "1": { "text": "10000" } } }, "2": { "cells": { "0": { "text": "titre b" }, "1": { "text": "5000" } } }, "3": { "cells": { "0": { "text": "titre c" }, "1": { "text": "45495" } } }, "4": { "cells": { "0": { "text": "total" }, "1": { "text": "=SUM(B2,B4)" } } }, "len": 100 }, "cols": { "len": 26 }, "validations": [], "autofilter": {} }
]

let workbook = xtos(data);

XLSX_CALC(workbook)

let elements = []
let output = [];
/* loop through the worksheet names in order */
for (var er in workbook.SheetNames) {
    //workbook.SheetNames.forEach(name => {
    var name = workbook.SheetNames[er];
    const worksheet = workbook.Sheets[name];
   // console.log(worksheet, "one piece")
   // const sdf = XLSX.read([worksheet], {cellFormula:true});
    //console.log(worksheet)

    const html = XLSX.utils.sheet_to_html(worksheet);
    output.push(`<H3>${name}</H3>${html}`);

    console.log(html)
};

`