Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Updating Formula Result #24

Open
morgante opened this issue Jul 25, 2015 · 8 comments

Comments

Projects
None yet
6 participants
@morgante
Copy link

commented Jul 25, 2015

It's unclear from the documentation how I would go about doing this.

I want to read in an existing XLS file, update some values, and then get the new computed results of formulae.

For example, if I have this spreadsheet:

A1=3
A2=4
A3=7
A4=sum(A1:A3)

I'd like to update A3 to equal 8 and for A4's value to then yield 15.

@guyonroche

This comment has been minimized.

Copy link
Collaborator

commented Jul 26, 2015

worksheet.getCell('A3').value = 8;

// assuming A4 is a formula value with formula and result fields
worksheet.getCell('A4').value.result = 15;

@boloeng

This comment has been minimized.

Copy link

commented Dec 4, 2015

@guyonroche what I understand is that formulas are not computed by the library but retrieved from the source file?

@guyonroche

This comment has been minimized.

Copy link
Collaborator

commented Jan 27, 2016

Yes - the library is not a replacement for Excel, it won't calculate the results for you - at least not yet anyway.

@spadkins

This comment has been minimized.

Copy link

commented Jan 27, 2017

I understand that the ExcelJS software does not recompute formulas. (That is Excel's job.)
However, when I modify values in cells that are referred to in formulas and export it to an Excel file, I would expect Excel to recompute the values that are formulas when it opens up the spreadsheet for the first time.
However it does not do this.
If I tell Excel to recalculate the entire sheet, nothing happens.
If I edit the formula in the cell and hit ENTER, the value for that one cell is recalculated correctly.
Does anyone know if there is anything I can do in ExcelJS to tell Excel to recalculate the formula cells when it first opens the spreadsheet?

@spadkins

This comment has been minimized.

Copy link

commented Jan 30, 2017

I discovered that Shift-F9 ("recalculate worksheet" in Excel) does not sufficiently recalculate the spreadsheet but that Ctrl-Shift-Alt-F9 (in Excel) does recalculate the spreadsheet successfully.

However, since I don't want to have to do a recalculation in Excel, I found this work-around.
I actually have calculated the results of the formulas in Node.js, so I tried the following.

// value is the value I want to set to the cell
// cell is the cell object from the worksheet
if (cell.value && typeof(cell.value) === "object" && cell.value.formula) {
cell.value.result = value; // I would think this would work but it doesn't
}
else {
cell.value = value;
}

However, when I try to set the result of a formula cell like that (above), it doesn't seem to work.
The result does not stick.
If I print out the cell.value.result before and after, it is unchanged. (!?!)
There seems to be some object magic happening on the value of a formula cell that I'm not understanding.

So I did the following instead, and it works perfectly.
The new result shows up in Excel for the formula cell when the file is opened up.

// value is the value I want to set to the cell
// cell is the cell object from the worksheet
if (cell.value && typeof(cell.value) === "object" && cell.value.formula) {
cell.value = {
formula: cell.value.formula, // copy in the old formula from the old cell.value
result: value // put the newly computed value in as the result
};
}
else {
cell.value = value;
}

IMPORTANT: This method does not cause Excel to recalculate the values of the formulas.
For that, you still have to Enable Editing of the downloaded spreadsheet and press Ctrl-Shift-Alt-F9.
This method merely allows that if you have computed the result of the formula correctly in Node.js, then you can set it so that Excel will display it when the file is first opened.

@ifocusdata

This comment has been minimized.

Copy link

commented Mar 29, 2018

Going back to the original post by @morgante, I would like to use node (exceljs) to update a value and then read the new computed value from a cell containing a formula that references the updated value. The example by @spadkins above seems to bi-pass the work that Excel should be doing for us. To clarify, I don't want to ever "open" the excel file, but rather use it in the background as a calculation engine. Is this possible using the exceljs module?

@WinstonMarvel

This comment has been minimized.

Copy link

commented May 14, 2019

If I print out the cell.value.result before and after, it is unchanged. (!?!)

I experienced the same issue here and did the same. Had to change the whole object. Any idea why this is happening?

I did console log of the property descriptors of the object. Both cell.value.formula as well as cell.value.result are enumerable, configurable as well as writable. But I still was not able to override the cell value result.

@spadkins

This comment has been minimized.

Copy link

commented May 15, 2019

With regard to the question asked by @ifocusdata, having Excel running in the background as a calculation engine is NOT possible using the exceljs module. The exceljs module is not Excel, and it is completely oblivious to whether Excel is installed on the machine or not. It is merely a Javascript/Node module that reads and writes an Excel-formatted file.

At the time I described the work-around (described in a previous comment), if you wanted to change the value of a cell, and if that cell should cause other cells to be updated, you had to compute those new values yourself and use my work-around.

The documentation (https://www.npmjs.com/package/exceljs#formula-value) seems to indicate that my work around is in fact the way that the module is to be used. Also, "Note that ExcelJS cannot process the formula to generate a result, it must be supplied." So for @WinstonMarvel, all I can say is that the documentation I just cited says to do it the way I eventually found to do it.

Having said that, it would be a nice (but difficult) enhancement to write a workbook.recalculate() method or a worksheet.recalculate() method that would emulate what Microsoft Excel does in recalculating values. This would have to parse every formula and implement every Excel formula function, and it might be able to get most of the calculations right. (There would still be lots of complex issues it would probably never get right like references to cells in other spreadsheets, etc.)

Alternatively, it would still be nice to know how to modify the file so that when it is opened in real Excel, Excel would automatically do the recomputations. This is the problem I was stuck on that caused me do my own (limited) recomputations.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.