Step-by-step guide for inputting a sophisticated formula in exceljs using JavaScript

Currently, I am working on a project that involves exporting data into multiple xlsx sheets. One of the sheets requires me to add a formula in cells that meet certain conditions before adding data from the first sheet. Here is an example:

Ref !== null ? worksheet2.getCell('D2').value ={ formula: `IF('dataSheet'!${Ref.address}<>"";'dataSheet'!${Ref.address};"")`, result:Ref.value} : worksheet2.getCell('D2').value = '';

In this case, Ref refers to a cell in the first sheet that contains the "ref" label.

The issue with this formula (or any "IF" formula) is that when I open the Excel document, I receive this error message: 

We found a problem with some content in test.xlsx. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

Upon clicking Yes, the document gets recovered but all the IF formulas are lost. VLOOKUP formulas also do not work properly.

Simple formulas like A1+A2 function correctly, but nothing beyond that seems to work. I have tried using common formulas like SUM or AVERAGE, with no success. I even experimented with adding or removing an "=" sign before the formula, but it did not help. An example of a simple formula that failed to work is formula: '=IF(A1=0;10;0)'.

Furthermore, I attempted to use another package, XLSX, with IF and SUM formulas, but encountered the same issue.

Lastly, since I work for a French company, all the formulas are written in French (e.g., IF is SI). I initially thought there might be a language discrepancy between ExcelJS and Excel options, but even after switching to English, the error message persisted.

javascripttypescriptxlsxexceljs

Answer №1

Using this approach will yield positive results.

worksheet.getCell('D2').value = { formula: 'IF(datasheet!A1<>"",datasheet!B1,"")'};

For more formula examples, refer to this site

The JavaScript file demo.js will generate the Excel file test.xls

const Excel = require('exceljs');

const workbook = new Excel.Workbook();

const data_sheet = workbook.addWorksheet("datasheet")
const formula_sheet = workbook.addWorksheet("formula")


// setting data for the data_sheet
data_sheet.getCell('A1').value = 'ref';

data_sheet.getCell('B1').value = 1;

// setting formula data
formula_sheet.getCell('A1').value = 2;
formula_sheet.getCell('A2').value = 3;
formula_sheet.getCell('A3').value = { formula: 'SUM(A1,A2)'};

formula_sheet.getCell('D2').value = { formula: 'IF(datasheet!A1<>"",datasheet!B1,"")'};

const fileName = './test.xlsx';
workbook.xlsx
    .writeFile(fileName)
    .then(() => {
        console.log('file created');
    })
    .catch(err => {
        console.log(err.message);
    });

In the datasheet,

https://i.sstatic.net/vsqgj.png

Within the formula sheet

https://i.sstatic.net/S10cI.png

The formula in cell D2 will be

=IF(datasheet!A1<>"",datasheet!B1,"")

Resulting in the display of

1

since datasheet's A1 is ref and B1 is 1.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Tips for fixing TypeScript compiler error TS2339: Issue with accessing 'errorValue' property in Angular 5 project

Within a component, I have developed a function to manage errors returned from a Rest Service and determine the corresponding error message to display to the user. This method accepts an error object (custom data structure from the service), navigates to e ...

angulartypescripttslint

Developing an interactive input field using JavaScript's Document Object Model (DOM)

I am struggling with creating an editable text field using JavaScript. I have come across a function that allows for editing, but I am unsure if it is possible to change the title from High School to Middle School, which is generated by the function create ...

javascriptdom

Struggling to populate a nested array in a dropdown using AngularJS

I am currently working on populating JSON data into a dropdown list, specifically focusing on main category, sub category and sub sub category. Successfully managed to populate the main category and sub category, but facing challenges with populating subsu ...

javascriptangularjs

Creating a JSON Array from a PHP Array with json_encode()

I have used the built-in json_encode() function to encode an Array that I created. I am in need of formatting it into an Array of Arrays as shown below: { "status": "success", "data": [ { "Info": "A", "hasil": "AA" }, { " ...

javascriptphpjson

Issue: 'The server cannot be started because the path is not defined' error message appears

Hey everyone, I'm currently working on implementing a forgot/reset password feature for my React Native app using this tutorial. However, when attempting to start the server, I encountered an error related to the 'path'. ReferenceError: pat ...

javascriptexpress

saving the hash key in a separate array

Currently, I have a collection of key-value pairs that need to be stored in another array. However, I am facing difficulties with the logic as I am unable to keep track of which keys-values have already been assigned while iterating over the list of object ...

javascriptarrayskey-value

How can the AngularJS model be updated while using long polling with Ajax?

How can I update the model using ajax long polling method? To start, I will load the default list: For example: - id1 - id2 - id3 Next, I set up an ajax long polling process in the background that runs every 5 seconds. When the ajax call receives an upd ...

javascriptajaxnode.jsangularjs

I am looking to optimize my WordPress posts to load in increments as the user scrolls down the page, similar to how Facebook does

I am looking to implement a feature on my WordPress post where the content loads a few at a time as the user scrolls, similar to Facebook. Specifically, I would like my webpage to automatically load 10 posts and then continue loading 10 more as the user re ...

phpjavascriptjquerywordpress

The child component is experiencing issues with receiving props from the father component, even though it is functioning

After successfully passing data from the father component to the child and displaying it in the view, everything seemed to be working fine at first. However, upon checking the console, I noticed that there was an issue occurring, even though the code appea ...

javascriptvue.jsvuejs2

Examining the disparity between two dates through mocha/chai testing

I am currently utilizing "chai": "^4.2.0" and "mocha": "^6.1.4",. Upon using assert.equal() to compare two dates, I encounter a discrepancy where the comparison returns false despite the dates appearing the same. Here is an example of the situation: http ...

javascriptmocha.jschai

Is it possible for a lambda in TypeScript to have access to the class scope but return undefined

In my TypeScript code, I have a class used as a Pipe in Angular 2 to render markdown. It compiles successfully but encounters a runtime exception on a specific line: var Remarkable = require('remarkable'); @Pipe({ name: 'markdown' ...

javascripttypescriptangularscopethis

NodeJS - Bluebird: implementing a loop with a waiting mechanism for completion

Struggling with a seemingly simple task in my NodeJS and MongoDB project. I've included Bluebird for promises, but need some help. In my code, I have a basic for-loop that generates random names and pushes them into an array: const PlayerGenerator = ...

javascriptnode.jsmongodbmongoosebluebird

Having trouble with the nav-item dropdown functionality on Laravel Vue?

Currently utilizing Laravel and Vue.js along with AdminLTE. Initially it was functioning properly, but now... head <!-- Font Awesome Icons --> <link rel="stylesheet" href="plugins/fontawesome-free/css/all.min.css"> <!-- Theme style --> ...

javascriptphplaravelvue.jsadminlte

Creating a hierarchical menu structure by splitting strings in an array using an algorithm

I have an array of strings in Javascript that look like this: var array = [{ string: 'path1/path2/path3' }, { string: 'path1/path4/path5' }, { string: 'path1/path2/path6' }, { string: 'path10/path7' }, { s ...

javascriptarraysjsonstringalgorithm

Only retrieve one result from v-for loop depending on the key value

My v-for loop is set up to display a single result based on the chosen :key value from the counter data property. Everything is working well, but I am encountering problems when I try to add <transitions> for a smoother update of the value. The issue ...

javascriptvue.jsv-for

Guide on how to restrict specific days and dates in MUI React datepicker using a JSON array

I am working on a Laravel application that generates a JSON response containing dates and days of the week that need to be disabled in the datepicker. For instance, here is an example of my datesOff constant when I log it: ['2022-05-08', '2 ...

javascriptreactjsmaterial-uimaterialize

Display the chosen date from the datepicker in the input field

Utilizing a JQuery DatePicker to be constantly displayed, I have assigned it to a div. Despite wanting it to display the selected date in the input box like in this example, it just isn't functioning as desired for me. Is there a way to have the selec ...

javascriptjqueryhtmldatepicker

A ReferenceError was thrown because angular is not defined within the angular-moment.js script

After spending an hour trying to figure out what went wrong, I still can't solve this problem. I've searched on stackoverflow for answers, but nothing seems to be helpful. The issue arises when trying to integrate Moment js into my project. Che ...

javascriptangularjsionic-frameworkmomentjs

Angular/JS encountered a premature end of program unexpectedly

I am taking my first steps in the world of web development with Angular (and JavaScript in general). I have been trying to rewrite some basic and common examples using Angular. One thing I attempted was to display a simple message using data binding. This ...

javascriptangularjs

Retrieve the data stored in a selection of checkbox fields on a form

I have a table of checkboxes: <div> <h1 class="text-center">Select activities</h1> <div class="row"> <div class="col"></div> <div class="col-md-8 col-lg-8"> <h3>Link activ ...

javascriptjquerytwitter-bootstrapcheckbox