Method calculate doesnt work properly on a specific cell

Aspose.Cells Product Family
1
Hello, Dear colleagues.

We need your help. The method calculate doesnt work properly on a specific cell. The sample code and a workbook attached.

LOIS team.
2

Hi,


Thanks for the template file and sample code.

After an initial test, I observed the issue as you mentioned. I found the Cell.calculate() method does not work properly for a specific cell. I used the use case provided by you with your template file and found the issue.
e.g
Sample code:

Workbook workbook = new Workbook(Test.class.getResourceAsStream(“example.xls”));

Worksheet sheet1 = workbook.getWorksheets().get(“sheet1”);
Worksheet sheet2 = workbook.getWorksheets().get(“sheet2”);
Worksheet sheet3 = workbook.getWorksheets().get(“sheet3”);

sheet2.getCells().get(“B2”).calculate(new CalculationOptions());

sheet1.getCells().get(“A2”).putValue(“22”);
sheet3.getCells().get(“C2”).putValue(7);

sheet3.getCells().get(“B2”).calculate(new CalculationOptions());

//Actual - 4, but expected - 7
System.out.println(sheet3.getCells().get(“B2”).getValue()); // ---- Not Ok

workbook.calculateFormula();

//Actual - 7, expected - 7
System.out.println(sheet3.getCells().get(“B2”).getValue()); // -----Ok

File result = File.createTempFile(“result”, “.xls”);
workbook.save(result.getAbsolutePath());

System.out.println(result.getAbsolutePath());

workbook.dispose();

I have logged a ticket with an id “CELLSJAVA-41602” for your issue. We will look into it soon.

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

Thank you.


3
Hello, dear colleagues!
At the 11/27 you've accepted an issue and created a ticket for it "CELLSJAVA-41602".
Unfortunately this is a very critical problem for us. Are there any suggestions about the terms of fixing it?

Also are there any limitations for the terms of any bug fixing, that you declare in license agreement?
4

Hi,


Thanks for sharing your concerns.

I have checked the status of the issue logged earlier as “CELLSJAVA-41602”, I am afraid, it is not resolved yet. I have logged your concerns and asked the relevant developer to update on your issue or provide an eta for it. Once we have an update on it, we will let you know here. I hope, your issue would be fixed in the next few days (e.g 5-7 etc.).

Regarding workaround to fix your issue, I think you may try to use Workbook.calculateFormula() method instead for now as it works fine. The difference b/w Cell.calculate() and Workbook.caculateFormula() method is that the latter does evaluate and calculate all the formulas in the whole workbook where as Cell.calculate() only calculates the formula for the underlying cell or so.

Thank you.
5
Thank you for the advice, but in our case high load testing shows that the speed of calculation when using Workbook.calculateFormula() is twice as lower as the speed of the current technology we use (the technology is based on Apache POI, it allows us to calculate only specific cells without calculating whole workbook). We consider Aspose.Cells as the replacement of our current technology, so the method you proposed is inappropriate for our goals.

And you didnt answer the 2nd question. Are there any limitations for the terms of any bug fixing, that you declare in license agreement?
6
Hi,

bvg:
Thank you for the advice, but in our case high load testing shows that the speed of calculation when using Workbook.calculateFormula() is twice as lower as the speed of the current technology we use (the technology is based on Apache POI, it allows us to calculate only specific cells without calculating whole workbook). We consider Aspose.Cells as the replacement of our current technology, so the method you proposed is inappropriate for our goals.

Well, I am afraid, you have to spare us a little more time (5-7 days or so), so our product team could evaluate your issue precisely and to possibly figure it out.

bvg:
And you didnt answer the 2nd question. Are there any limitations for the terms of any bug fixing, that you declare in license agreement?

No, generally, there is no limitation for the terms and condition regarding bug fixing. We do understand your concerns and inconvenience you have been facing. We have extended the priority of your issue. For your information, the problems/ issues our users post in normal forums (other than Priority Support or Enterprise support clients) are resolved on first come first serve basis as we believe this is the fairest policy to all the customers. Sometimes when our product team has more tasks, issues or features to be performed/added, it might take little more time to fix some issues for some users. Nevertheless, as per our product team schedule/convenience, they will try to fix these issues soon.

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

Thank you.
7

Hi,

Thanks for using Aspose.Cells.

The Cell.calculate() method is designed mainly for the situation that
the calculation chain has been built for the workbook, or the cell’s
formula does not refer to other formulas that need to be calculated
recursively.

For performance consideration, we do not reset calculation
flags for any cell formula so not all formulas can be re-calculated when
one precedent has been changed. To calculate single cell instead of the
workbook, the correct way is using WorkbookSettings.CreateCalcChain:


Java

Workbook wb = new Workbook(“example.xls”);
wb.getSettings().setCreateCalcChain(true); //let workbook build the calculation chain for later being used for calculating single cell
wb.calculateFormula(); //build the chain by the first calling of formula calculation of the workbook
… //update cell values
sheet3.getCells().get(“B2”).calculate(new CalculationOptions()); //here use can get the correct value 7 instead of 4

8

Hi,

Does not work. An example of an attachment.
9

Hi,


Thanks for providing us new test case with template file.

I have evaluated your issue a bit. You are right, there is still an issue with Cell.calculate() method even following the suggested approach. If we use Workbook.calculateFormula() method all the time instead of Cell.calculate(), it works as expected. I have logged your concerns with your samples against your issue into our database. Our concerned developer from product team will look into it soon.

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

Thank you.
10

Hi,


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

Thank you.
11

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java v8.6.2.6 and let us know your feedback.

12

Hi,

Does not work. An example of an attachment.

Vladimir, LOIS
13

Hi,

Thanks for your feedback and using Aspose.Cells.

We were able to observe this issue and found that issue still exists in the latest version 8.6.2.6. The result should be 7 but it is giving 4 so it should be fixed. We have reopened this issue now. Once, the fix is available for you or if we have any other update relating to it, we will let you know asap.

14
Hello, dear colleagues!
This is a very critical problem for us. Are there any suggestions about the terms of fixing it?
15

Hi,


I have checked the status of your issue your issue “CELLSJAVA-41602”, it is “In Progress”. Our concerned developer from product team is currently working over your issue and hopefully your issue would be fixed soon. I have also intimated the relevant developer to provide an eta if it takes more time.

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

Thank you.
16

Hi,

Thanks for using Aspose.Cells.

It is to update you that we will provide fix for this issue in this week. Once the fix is available for you, we will let you know by posting in this thread.

17

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java v8.6.3.4 and let us know your feedback.

18

The issues you have found earlier (filed as CELLSJAVA-41602) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
19
Hi,
Unfortunately the problem is not solved. An example of an attachment.

Vladimir, LOIS
20

Hi Vladimir,

Thanks for your sample code and excel file and using Aspose.Cells.

We were able to observe this issue with the latest version 8.7.0.0 therefore we have logged this issue separately as a sub-task of the main issue for better management. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41713 - Cell.calculate() method does not return correct value