Missing functions

[anolan23]

const express = require('express');
const XLSX = require('xlsx');
const XLSX_CALC = require('xlsx-calc');
const formulajs = require('@formulajs/formulajs');

const db = require('../db');
const Vehicles = require('../db/repo/Vehicles');

const router = express.Router();

XLSX_CALC.import_functions(formulajs, { override: true });
const workbook = XLSX.readFile('backend/excel/calc.xlsm');
const sheet = workbook.SheetNames[8];
const worksheet = workbook.Sheets[sheet];

router.post('/api/vehicles', async (req, res) => {
  try {
    const {
      user_id,
      year,
      make,
      model
    } = req.body;

    worksheet['B2'].v = make;
    worksheet['B3'].v = model;
    worksheet['B5'].v = year;

    XLSX_CALC(workbook);

    const summary = XLSX.utils.sheet_to_json(worksheet);
    res.send(summary);

    // const vehicle = await Vehicles.create(req.body);
    // res.send(vehicle);
  } catch (error) {
    console.error(error);
    res.status(error.status || 500).send({ error: error.message });
  }
});

module.exports = router;

I've been stuck for hours on trying to recalculate the workbook which has many references and complex functions.

I'm getting this error after making POST request to /api/vehicles even though formulajs HAS this function. Why is it giving me error if the function exists?

[anolan23]

If I remove { override: true } this error logs:

[anolan23]

Here is the formulajs object printed to console showing that GAMMA.INV exists when I import_functions

[fabiooshiro]

Try to put a key "GAMA.INV" like:

let directkeys = {}; directkeys["GAMA.INV"] = formulas.GAMA.INV; // import that directkeys formula

Em dom, 29 de mai de 2022 13:55, anolan23 @.***> escreveu:

Here is the formulajs object printed to console showing that GAMMA.INV exists when I import_functions [image: image] https://user-images.githubusercontent.com/57581908/170882121-d4a4d13a-4812-4013-aca7-d7bf8e61b03d.png

— Reply to this email directly, view it on GitHub https://github.com/fabiooshiro/xlsx-calc/issues/89#issuecomment-1140486453, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABVNEBQB4QYJCGK2BL5EKLVMOOQXANCNFSM5XHMKIAQ . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[anolan23]

const XLSX = require('xlsx');
const XLSX_CALC = require('xlsx-calc');
const formulajs = require('@formulajs/formulajs');

XLSX_CALC.import_functions(formulajs, { override: true });
let directkeys = {};
directkeys['GAMMA.INV'] = formulajs.GAMMA.INV;
// import that directkeys formula
XLSX_CALC.import_functions(directkeys);

const workbook = XLSX.readFile('backend/excel/calc.xlsm');

I'm trying this right now. I'll try import_raw_functions if it doesn't work

[anolan23]

Try to put a key "GAMA.INV" like: let directkeys = {}; directkeys["GAMA.INV"] = formulas.GAMA.INV; // import that directkeys formula Em dom, 29 de mai de 2022 13:55, anolan23 @.***> escreveu: …

Using your suggestion I waited 45 minutes to receive response from that endpoint before I stopped it in Postman. No error the whole time, but it just hangs. How long can the recalculate take? import_raw_functions didn't solve it either.

[anolan23]

link to stackoverflow question

[anolan23]

var mymodule = function(workbook) {
    var formulas = find_all_cells_with_formulas(workbook, exec_formula);
    for (var i = formulas.length - 1; i >= 0; i--) {
        exec_formula(formulas[i]);
        console.log(i)
    }
};

@fabiooshiro by adding a console log, I figured out that the excel file had 1.25 million formulas that needed to be executed. After 2 hours it finally finished recalculating. Nice.

However, let's say I reduce that calculation time to only a minute by removing a lot of formulas. This is still a synchronous calculation intensive process. Wouldn't this be blocking the single thread of the node.js server? Meaning other requests to server will have to wait for this to finish. I guess I'm trying to evaluate when the usage of this npm package will be realistic because you can't have blocking of the thread.

[fabiooshiro]

You need to spawn a worker or may use a serverless solution

[Kyle1297]

We have some more missing functions:

• ANCHORARRAY
  • LET
  • _xlws.FILTER
  • XMATCH
  • _xlws.SORT

Example error: Error: "Sheet - Hidden"!D83: Function _xlws.FILTER not found

[Kyle1297]

Happy to assist with adding the above functions. Have no idea how to start though, including where we even find equivalent excel logic for these functions.

Need this for a work project, so it is rather urgent. Ready to help ASAP