Extend Calculation chain dynamically new ranges in Java

Hi.


Can you please help me with following issue:

public static class Resolver implements ICustomFunction {

private boolean isLocked;

public Resolver(boolean isLocked) {
this.isLocked = isLocked;
}

@Override
public Object calculateCustomFunction(String s, ArrayList paramList, ArrayList arrayList2) {
System.out.println(“Calc " + s);
if (isLocked) {
return 0.0;
}
Double r = 0d;
for (Object d : paramList) {
r += (Double)d;
}
System.out.println(s + " calculated to " + r);
return r;
}
}

public void calculationChainTest() {
try {
Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
Cells cells = ws.getCells();

wb.getSettings().setCreateCalcChain(true);

Cell a1 = cells.get(“A1”);
Cell a2 = cells.get(“A2”);
Cell a3 = cells.get(“A3”);

a2.setFormula(”=MYSUM(A1,5)");
a3.setFormula("=MYSUM(A2,3)");

a1.putValue(“1”, true);
a3.calculate(true, new Resolver(true));
outCells(a1, 1, a2, 6, a3, 9);

a1.putValue(“4”, true);
a3.calculate(true, new Resolver(false));
outCells(a1, 4, a2, 9, a3, 12);

} catch (Exception e) {
e.printStackTrace();
assertNull(e);
}
}

private void outCells(Cell a1, double a1val, Cell a2, double a2val, Cell a3, double a3val) {
assertEquals(a1val, a1.getDoubleValue());
assertEquals(a2val, a2.getDoubleValue());
assertEquals(a3val, a3.getDoubleValue());
}

a2 will no recalucated automatically on recalculation a3. I just have one way to resolve this issue - read all precendents of a3 and recalc it.

Best regards. Alexey

Hi Alexey,

Thanks for using Aspose.Cells.

For this issue of recalculating formulas, it is not bug of Cell.calculate()
method. Formula calculation chain can only be created when user calculating the
whole workbook(by Workbook.calculateFormula()). So, you should call
Workbook.calculateFormula() at least once to get the chain created.

You can change your code bit like this.
a1.putValue("1", true);
wb.calculateFormula(true, new Resolver(true));
outCells(a1, 1, a2, 6, a3, 9);
a1.putValue("4", true);
a3.calculate(true, new Resolver(false)); //and here you can use wb.calculateFormula() too.
outCells(a1, 4, a2, 9, a3, 12);

Hi Shakeel.


We have a book, that will change dynamically. After some changes i should recalc values in new range. For this moment some cells in old ranges could be changed by user. Is there some way for dynamically extend Calculation chain for new ranges? (wb.calculationFormula() are no applicable way, because changes could be about thousands)

Changes like :
- copying ranges (with formulas)
- removing ranges
etc

public static class Resolver implements ICustomFunction {

private boolean isLocked;

public Resolver(boolean isLocked) {
this.isLocked = isLocked;
}

@Override
public Object calculateCustomFunction(String s, ArrayList paramList, ArrayList arrayList2) {
System.out.println(“Calc " + s);
if (isLocked) {
return 0.0;
}
Double r = 0d;
for (Object d : paramList) {
r += (Double)d;
}
System.out.println(s + " calculated to " + r);
return r;
}
}

public void calculationChainTest() {
try {
Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
Cells cells = ws.getCells();

wb.getSettings().setCreateCalcChain(true);

Cell a1 = cells.get(“A1”);
Cell a2 = cells.get(“A2”);
Cell a3 = cells.get(“A3”);

a2.setFormula(”=MYSUM(A1,5)");
a3.setFormula("=MYSUM($A$2,3)");

a1.putValue(“1”, true);
wb.calculateFormula(true, new Resolver(true));
checkCell(a1, 1);
checkCell(a2, 0);
checkCell(a3, 0);

cells.copyRow(cells, 2, 3);
Cell a4 = cells.get(“A4”);

a1.putValue(“4”, true);
a4.calculate(true, new Resolver(false));
checkCell(a1, 4);
checkCell(a2, 9);
checkCell(a4, 12);

} catch (Exception e) {
e.printStackTrace();
assertNull(e);
}
}

private void checkCell(Cell cell, double value) {
assertEquals(value, cell.getDoubleValue());
}

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

We have logged your requirements in our database for investigation. We will look into it and see if these could be fulfilled or we could suggest you any workaround. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40802.