Formula not working

[ilijaveselica]

Formula doesn't work. It's printed in the formula field but it's not applied.

// Require library 
var xl = require('excel4node');
 
// Create a new instance of a Workbook class 
var wb = new xl.Workbook();
 
// Add Worksheets to the workbook 
var ws = wb.addWorksheet('Sheet 1');
 
// Create a reusable style 
var style = wb.createStyle({
    font: {
        color: '#FF0800',
        size: 12
    },
    numberFormat: '$#,##0.00; ($#,##0.00); -'
});

ws.cell(1,1).number(100).style(style);
ws.cell(2,1).number(95).style(style);
ws.cell(3,1).number(23).style(style);
ws.cell(4,1).number(7).style(style);
ws.cell(5,1).number(56).style(style);
ws.cell(6,1).number(4).style(style);
ws.cell(7,1).number(75).style(style);
 
// Doesn't work in Microsoft Excel, works in LibreOffice
ws.cell(1,3).formula('SUM(A1:A7)').style(style);
  
wb.write('Excel2.xlsx');

I've tried few Excel packages and all have the same issue so far.

[absarora]

ilijaveselica: I just tried your code above without the styles and it seems to be working just fine.

[calumk]

I am having the same issue - without styles

[deepsoul]

I am having the same issue - after clicking in the row the formula works - but initially #NAME? will be displayed

[deepsoul]

Hi, is there any progress in this issue?

[calumk]

For me, the problem now seems to be solved - I wonder if updating excel solves it?

[reg0]

I'm not sure if that's the same issue, but my formulas don't get evaluated if excel opens the file in "protected view" (after downloading it from the internet)

there's similar issue described here, of course it's a different software, but maybe there's some hint: https://www.syncfusion.com/forums/109560/formulas-in-excel-spreadsheet-do-not-calculate-until-enable-editing-is-clicked they suggested using something like sheet.EnableSheetCalculations(); - is there something like that available in excel4node api?

[mlanzi]

I have the same issue with the formulas, specifically with IF and SUMPRODUCT. They have in common the use of ";" inside the formula, maybe it is related to that. The formula won't be evaluated.

[natergj]

@reg0 sheet calculations are turned on by default. @ilijaveselica, does this issue only occur when downloading the excel file from the internet and opening with Excel for Windows? If so, that is a security feature of Windows Excel. I don't believe it can be overridden.

[mlanzi]

I found how to resolve my issue: the string of the formula cannot contain ";" I replaced the ";" with "," and not it works!

[biaggi]

Somehow im trying to use the formula "SUMA(F1:FX)" but i get suma(f1:fx) and libreoffice does not resolve the formula :(

Dont want to manually touch the library, but this should be fixed

[mmartinsolisdev]

@biaggi you need to put SUM instead of SUMA because excel4node works with formulas in English. Regards

[ghost]

I'm not sure if that's the same issue, but my formulas don't get evaluated if excel opens the file in "protected view" (after downloading it from the internet)

there's similar issue described here, of course it's a different software, but maybe there's some hint: https://www.syncfusion.com/forums/109560/formulas-in-excel-spreadsheet-do-not-calculate-until-enable-editing-is-clicked they suggested using something like sheet.EnableSheetCalculations(); - is there something like that available in excel4node api?

having same issue is there any solution for excel4node package