Intermediate calculateFormula() breaks calculations

Aspose.Cells Product Family
1

Using the attached template and the sample code below, I get this output:

Before calculate:
Retail_Pipeline!C91 formula: =C43C56C66C81
Retail_Pipeline!C43 value: 773.27
Retail_Pipeline!C91 value: 1,046.24
After set C43 to 0 and calculateFormula()
Retail_Pipeline!C91 formula: =C43C56C66C81
Retail_Pipeline!C43 value: -
Retail_Pipeline!C91 value: 1,046.24

Note that the last line should show the value “0”. But, if you comment out the middle “calculateFormula()” call, it works!

We need to set, calculate, and read lots of values in our application, of course.

Thanks for your help!



import java.io.IOException;
import com.aspose.cells.Cell;
import com.aspose.cells.Workbook;

public class AsposeTest2 {


public static void main(String[] args) {


Workbook simWorkbook = new Workbook();
try {
simWorkbook.open("/Users/brandon/Documents/sim.xls");
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}



Cell simCellC43 = simWorkbook.getWorksheets().getSheet(“Retail_Pipeline”).getCells().getCell(“C43”);
Cell simCellC91 = simWorkbook.getWorksheets().getSheet(“Retail_Pipeline”).getCells().getCell(“C91”);

System.out.println(“Before calculate:”);
System.out.println("Retail_Pipeline!C91 formula: " + simCellC91.getFormula()); // =C43C56C66C81
System.out.println("Retail_Pipeline!C43 value: " + simCellC43.getStringValue()); // 773.27
System.out.println("Retail_Pipeline!C91 value: " + simCellC91.getStringValue()); // 1,046.24

// comment out the next line, and it works!
simWorkbook.calculateFormula();

System.out.println(“After set C43 to 0 and calculateFormula()”);
simCellC43.setValue(0);
simWorkbook.calculateFormula();


System.out.println("Retail_Pipeline!C91 formula: " + simCellC91.getFormula()); // =C43C56C66C81
System.out.println("Retail_Pipeline!C43 value: " + simCellC43.getStringValue()); // -
System.out.println("Retail_Pipeline!C91 value: " + simCellC91.getStringValue()); // ERROR: 1,046.24 – should be 0!


}
2

A quick clarifying note: after the first calculateFormula() call, no further calculateFormula() calls work.

3

Hi,

yes, we found the issue and haved fixed it already. We will attach a fix soon.

Thank you.

4

Hi,

Please try this attached fix, thank you.

5

You fixed that one cell, but you don’t seem to have fixed it generally. Now, other calculations never occur. We’re also seeing resulting calculations that are way off of the correct calculations that you would see if you were using Excel.

This is the exact same code, but looks at the cells in column D instead of column C.


import java.io.IOException;
import com.aspose.cells.Cell;
import com.aspose.cells.Workbook;

public class AsposeTest2 {


public static void main(String[] args) {


Workbook simWorkbook = new Workbook();
try {
simWorkbook.open("/Users/brandon/Documents/sim.xls");
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}



Cell simCellD43 = simWorkbook.getWorksheets().getSheet(“Retail_Pipeline”).getCells().getCell(“D43”);
Cell simCellD91 = simWorkbook.getWorksheets().getSheet(“Retail_Pipeline”).getCells().getCell(“D91”);

System.out.println(“Before calculate:”);
System.out.println("Retail_Pipeline!D91 formula: " + simCellD91.getFormula()); // =D43D56D66D81
System.out.println("Retail_Pipeline!D43 value: " + simCellD43.getStringValue()); // 266.60
System.out.println("Retail_Pipeline!D91 value: " + simCellD91.getStringValue()); // 360.70

// it now makes no difference whether you comment out the next line or not.
simWorkbook.calculateFormula();

System.out.println(“After set D43 to 0 and calculateFormula()”);
simCellD43.setValue(0);
simWorkbook.calculateFormula();


System.out.println("Retail_Pipeline!D91 formula: " + simCellD91.getFormula()); // =D43D56D66D81
System.out.println("Retail_Pipeline!D43 value: " + simCellD43.getStringValue()); // -
System.out.println("Retail_Pipeline!D91 value: " + simCellD91.getStringValue()); // ERROR: 360.70 – should be 0!


}
}

6

Here is an example of values in the spreadsheet that seem to calculate very wrongly. I don’t know if this is related to the former problem or not.


import java.io.IOException;
import com.aspose.cells.Cell;
import com.aspose.cells.Workbook;

public class AsposeTest3 {


public static void main(String[] args) {


Workbook simWorkbook = new Workbook();
try {
simWorkbook.open("/Users/brandon/Documents/sim.xls");
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}



Cell simCellD273 = simWorkbook.getWorksheets().getSheet(“AE2”).getCells().getCell(“D273”);

System.out.println(“Before calculate:”);
System.out.println("simCellD273 value: " + simCellD273.getStringValue()); // 66.58

simWorkbook.calculateFormula();


System.out.println(“After calculate:”);
System.out.println("simCellD273 value: " + simCellD273.getStringValue()); // 54.47


}
}

7

Hi,

Thanks for providing us the details,

We will figure out the issue soon.

Thank you.

8

We have traced some of the “wrong values” problems to a set of problematic formulas. We re-worked those formulas, and now Aspose seems calculate values much more closely, though still not perfectly.

We are still having the first problem (changing values of predecessor cells do not result in changes to descendent cells – the first code piece still results in the erroneous value.)

I’ve attached our revised template file.

9

Hi,

Thank you for your template and sample, and thanks a lot for your patience. Please try this fix.