This project has moved. For the latest updates, please go here.

Formula does not calculate within Excel after saving with ClosedXML

Dec 7, 2012 at 2:29 PM

Hi,

i open an own created Excel Template and fill tables with data with ClosedXML. Other cells outside the table contain formulas based on that data. I don't touch those formulas within the program.

When i open the excel file all formulas are not updated, although the cells are still general or number format and also the "Show Formula" shows the correct formula.

I need to edit each cell by pressing F2 and ENTER.

I tried to create show case, but here it works as excepted. So it will take a while to find this issue. Does anybody has a clue what could be the reason for this strange behaviour?

Thanks,

Peter

Coordinator
Dec 7, 2012 at 3:17 PM

See if the workbook has the calculate formulas options set to manually.

Dec 7, 2012 at 3:19 PM

Its set to "Automatic". Even if i press the "Calculate Now" button, nothing happens. I have to F2 each cell and press ENTER...

Coordinator
Dec 7, 2012 at 3:40 PM

I can reproduce the error. I'll look into it.

Dec 7, 2012 at 3:42 PM

Wow, because i can't reproduce it with a new created Excel file .. hmmm

Coordinator
Dec 7, 2012 at 3:56 PM

It happens because the library loads the value cached (from the original workbook) and then save that value back. Excel sees the (wrong) value cached and basically says "good, I don't have to calculate that formula". I'll keep digging.

Coordinator
Dec 7, 2012 at 4:08 PM

I think the solution is to not save the value cached back to the workbook. If you need to reopen the same file with ClosedXML and need the calculated value then resolving formulas would get you the value. If the formula is not implemented then we should add them as needed.

I have to look at the discussions regarding the value cached but I think this is the way to go.

Coordinator
Dec 7, 2012 at 4:34 PM

CodePlex's TFS is down (not unusual). I'll work on it later when it comes back up.

Coordinator
Dec 7, 2012 at 8:29 PM

Pick up the latest code. This will probably irk some people but I think it's better to lose the ValueCached than to open a worksheet that shows outdated values.

Dec 7, 2012 at 8:29 PM
Thanks. Maybe you can do it customizable? I don't need calculated values within my program, but obviously others do.

Personally i think that Excel application functionality should not be reimplemented by ClosedXML.
The purpose is to provides an easy way writing XSLT files, but it cannot provide all Excel functions...
If i need that i have to use Excels VSTO or something else ... just my personal opinion.

P.
Coordinator
Dec 7, 2012 at 8:33 PM
Edited Dec 7, 2012 at 8:34 PM

As a general rule I don't like options. The way I see it is that if someone needs the result of a formula that's not part of the library then the solution is to add the formula, not to break the principle of least surprise.

Dec 7, 2012 at 9:02 PM
Edited Dec 9, 2012 at 8:56 AM

I understand your point, but it can get quite complicated. I have formulas like that:

Pivottables & Name references
=GETPIVOTDATA("wMBs";$G$22)*Param.IntvlLength/1024

Nested functions with Names
=SUMPRODUCT(OFFSET(DiskOvw.Hdr.rSERV;1;0;DiskOvw.PtDOintervals;1);OFFSET(DiskOvw.Hdr.rIotime;1;0;DiskOvw.PtDOintervals;1))/SUM(OFFSET(DiskOvw.Hdr.rIotime;1;0;DiskOvw.PtDOintervals;1))

Array Formula with Table-Column reference
={INDEX(tblIOSTAT[Device];MATCH(1;SUBTOTAL(3;OFFSET(tblIOSTAT[Device];ROW(tblIOSTAT[Device])-MIN(ROW(tblIOSTAT[Device]));;1));0))}

I assume there are much more complicated ones out in the universe ..

You would need to solve all names and references and than reimplement Excel functions ... The point is, i know that i deal with the file itself and not with the application. If i work an data then i don't expect application functionality. This is somewhat comparable like a database and a application server.

Dec 7, 2012 at 9:58 PM

Formulas are resfreshed now. Thanks a lot.