Implementing circular references and iterations

Aspose.Cells Product Family
1

Hi support,

I use an Excel file that helps us calculate energy requirements for homeowners. Airflows in or out of the dwelling need to be solved iteratively since it involves some non-continuous functions.

The company I work at has an online environment were I upload Excel files in a certain format (i.e. meta-tab and “show”, “recalculate” columns). When I upload a simple counter works fine:

A1 = A2
A2 = A1+1

When I try something more complicated it does not work anymore, it does in Excel obviously.

Through googling I have found that Aspose should support this functionality. I have no experience with Aspose and am kind of overwhelmed. I would be helped if anyone can point me to the right resources to get me going.

Thanks in advance,
Peter

2

@petersusteen,

May be the issue is due to your code or data and not with Aspose.Cells APIs. Anyways, could you share your sample code (runnable) and sample Excel file(s) to show the issue, we will check it soon.

PS. please zip the files prior attaching.

3

Thanks for your quick reply.

I don’t have code. I just upload an Excel file and then the Aspose magic happens automatically. Someone made that for us years ago and is not around anymore.

I have attached two examples. The simple one works for us, the other one does not. It lets me upload the second one and it does not throw an an error but de calculations are wrong.Iterative Calculations.zip (21.4 KB)

4

@petersusteen,

I am afraid, to evaluate your issue precisely we need complete details. How and where Aspose.Cells works differently than MS Excel? Do you get calculated results differently via Aspose.Cells, kindly elaborate with sample and example? Also, show some screenshots to highlight where results are wrong. Rest assured, Aspose.Cells should mimic MS Excel results (mostly) so if Aspose.Cells behaves differently than MS Excel for some formulas, we need a test case to reproduce it so we could evaluate and fix it as soon as possible.

5

Ok,I will try to explain the problem better.

test_iteration.xlsx solves f(x) = x^2 - x = 0 using the bisection method (Bisection method - Wikipedia). Basically the method shifts two points (x_a and x_b) along the x-axis until f(x_a) and f(x_b) have opposite sign. The a new point closer to the solution is calculated as (x_a+x_b)/2.

Cells D13:D15 represent the two old points and a new point x_c. From D17:D36 the function values and next point x_c are calculated according to the bisection algorithm.

Cells D38:D40 contain the points x_a, x_b and x_c for the following iteration step.

Every 15 steps the excel file resets it self using the counter in cell D7.

When I enable iterative calculations and set the number of calculations to 1 I can follow the algorithm step-by-step by pressing F9 or clicking “calculate now” on the formulas tab. In Excel everything works as expected.

I then save the file and upload it to our web environment, as can be seen in the attached pictures.

Then I open the web form and see step 0. If not, you can get to step 0 by changing the value in field run until “counter” = 0.

Step 0: The web form and the Excel file agree.

Step 1: The web form and the Excel file do not agree.

x_b should be 1.5, as was determined in step 0.

Step 2:The web form and the Excel file do not agree.

Again x_b does not agree with the x_b next calculated in previous step.

What seems to happen is the circular reference is resolved differently to Excel. Excel works top to bottom and therefore take the previously calculated x_b next, and then calculates a new x_b next for the next step.

Aspose seems to somehow take the updated x_b next and uses that in the rest of the calculations.

I hope this is clear enough to reproduce the issue.

Thanks,

PeterIterative Calculations.zip (276.8 KB)

6

@petersusteen,

Thanks for providing more details and screenshots.

I did test it in a simple scenario to reproduce the issue. I used the following sample code (to generate individual PDF file for each calculation) and noticed certain differences. I found some formula calculation results are different when implementing circular references and iterations, I compared the results for formulas of the output PDFs with manual calculations when performing in MS Excel (MS Excel 2007).
e.g.
Sample code:

Workbook workBook = new Workbook("e:\\test2\\test_iteration.xlsx"); 

            for (int i = 0; i < 15; i++)
            {
                workBook.CalculateFormula();
                workBook.Save("e:\\test2\\outcalc_results" + i.ToString()+ ".pdf");
            }

By the way, when opening your file into MS Excel 2013 and 2016, an error message is prompted: “There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly…”, but MS Excel 2007 does not give such an error.

I have logged a ticket with an id “CELLSNET-47829” for your issue. We will investigate and look into it soon.

Once we have an update on it, we will let you know.

7

Hi, is there any progress update on the issue? I’m assuming that the functionality I am looking for is something that Aspose would want to support, right?

Thanks

8

@petersusteen,

Yes, we are working over your issue and hopefully it will be fixed soon. We will notify you once we fix it or have some updates on it.

9

@petersusteen,

We are working on this issue and try to make the calculation of such kind of circular references work in the same way with MS Excel. It is a complicated task and we need more time to investigate further and try new solutions. We will give feedback when we get clear ETA or possible fix.

Thanks for being patient!

10

Thanks for notifying me.

Any idea if it’s mather of days, weeks or months?

11

@petersusteen,
We are afraid that ETA cannot be provided right now however we will write back here once any update is ready for sharing.

12

@petersusteen,
We are afraid at least we need some weeks to finish this task.

13

@petersusteen,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

14

Please try the latest fix 21.2.4.

Aspose.Cells21.2.4 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.2.4 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.2.4 For .NetStandard20.Zip (5.5 MB)

15

Thanks! I will try and report back.

16

@petersusteen,

Please take your time to evaluate/test the new fix. Hopefully your issue will be sorted out by the new fix.

17

Hi,

We make use of the Java version of Aspose. Is it possible to make the fix available in Java?

18

@petersusteen
Please try the latest fix v21.2.5 of Java version:
aspose-cells-21.2.5-java.zip (7.2 MB)

19

The issues you have found earlier (filed as CELLSNET-47829) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao

20

Hi,

We have got the fix installed (v21.2.5). It seems that this invalidated our license for prior versions of Aspose. I guess something has gone wrong.

Do you have any idea how to resolve this problem?