alasql exported excel formula not working

Question

On my html page, I am using below code to create and download excel file. I am creating this dynamic file with lot many formullas.

let db = new alasql.Database('TEMPDB');
db.exec('create table tempexcel(A string,B string, c string)');
db.exec('insert into tempexcel("5","6","=A1+B1")');
db.exec('select * INTO XLSX("tempex.xlsx",{headers:false})  from tempexcel');

This code is working fine to generate excel. But when I open excel content for C1 cell is =A1+B1 If I will hit enter on it, it will evaluate the value. I want to evaluate this values for all the cells. can you please guide do I need to change something in excel or in alasql API?

Answer 1

For:

db.exec('create table tempexcel(A string,B string, c string)');

Checking the data types for alasql there's nothing for e.g. xlfunction so string is your best bet for column c.

So, the problem must be within alasql itself which leverages a library called xlsx to do Excel workbook creation. See here in the prepareSheet function:

for (var j = 0; j < dataLength; j++) {
    columns.forEach(function(col, idx) {
        var cell = {v: data[j][col.columnid]};
        if (typeof data[j][col.columnid] == 'number') {
            cell.t = 'n';
        } else if (typeof data[j][col.columnid] == 'string') {
            cell.t = 's';
        } else if (typeof data[j][col.columnid] == 'boolean') {
            cell.t = 'b';
        } else if (typeof data[j][col.columnid] == 'object') {
            if (data[j][col.columnid] instanceof Date) {
                cell.t = 'd';
            }
        }
        cells[alasql.utils.xlsnc(col0 + idx) + '' + i] = cell;
    });
    i++;
}

There's nothing to check if the cell should be flagged as a formula and considers just numbers, strings, booleans and dates (reasonably consistent with the data types documentation).

In the XLSX library, it's straightforward to flag a cell as a formula. So we can apply that to the alasql code e.g.

for (var j = 0; j < dataLength; j++) {
    columns.forEach(function (col, idx) {
        var isFormula = false; 
        var d = data[j][col.columnid];
        var cell;
        if (typeof d == 'string') {
            isFormula = d.substr(0, 1) == '=';
        }
        if (!isFormula) {
            cell = {v: data[j][col.columnid]};
            if (typeof data[j][col.columnid] == 'number') {
                cell.t = 'n';
            } else if (typeof data[j][col.columnid] == 'string') {
                cell.t = 's';
            } else if (typeof data[j][col.columnid] == 'boolean') {
                cell.t = 'b';
            } else if (typeof data[j][col.columnid] == 'object') {
                if (data[j][col.columnid] instanceof Date) {
                    cell.t = 'd';
                }
            }   
        } else {
            cell = {f: d.substr(1, d.length - 1)};
        }           
        cells[alasql.utils.xlsnc(col0 + idx) + '' + i] = cell;
    });
    i++;
}

If the value is a string, and begins with = then tell XLSX to output in a way that Excel knows it's a formula (and chop off the =). Otherwise, just do what alasql is already doing. That's an untested, poorly implemented hack by the way - but IMHO the answer to your question.

If you hack that into the alasql.fs.js file in node_modules then your original code will just work the way you expect.

I took the liberty of raising an issue in the alasql project about this.