Calculate formula with cell dependency

Aspose.Cells Product Family
1

Hello i need help in following feature


At some step of project we should parse a book with formulas and check first parameter of function USINPUT

First parameter could be string or formula. I need to calculate this parameter in runtime, but unable to calculate formula cell dependend

Code:
public void test() throws Exception {
Workbook workbook = new Workbook(“D:\test.xlsx”);
Worksheet inSheet = workbook.getWorksheets().get(“in”);
Cells inCells = inSheet.getCells();
Cells outCells = workbook.getWorksheets().get(“out”).getCells();

FindOptions opt = new FindOptions();
opt.setLookAtType(LookAtType.CONTAINS);
opt.setCaseSensitive(false);
opt.setSearchNext(true);
opt.setLookInType(LookInType.FORMULAS);

Cell cell = inCells.find(“USINPUT”, null, opt);
while (cell != null) {

String cellFormula = cell.getFormula();

int beginIdx = cellFormula.indexOf(’(’);
int endIdx = cellFormula.indexOf(’,’, beginIdx);

String code = cellFormula.substring(beginIdx + 1, endIdx);

String value = inSheet.calculateFormula(code).toString();
outCells.get(cell.getRow(), cell.getColumn()).setValue(value);

cell = inCells.find(“USINPUT”, cell, opt);
}

workbook.save(“D:\out.xlsx”);
}

Now, sheet “out” contains a string “M.1.1” in each cell, but i need to receive a result of calculation formula “M.”&ROW()&"."&COLUMN() for each cell.

Is it possible to do this? I think there is should be some method for Cell object - Cell.calculate(String), but unable to find it.

Best regards. Alexey
2

Hi,


Thanks for providing details with sample code and template file.

I think you may try the following workaround to accomplish your task, please refer to it ( see the lines in bold):
e.g
Sample code:

…
Workbook workbook = new Workbook(“test.xlsx”);
Worksheet inSheet = workbook.getWorksheets().get(“in”);
Cells inCells = inSheet.getCells();
Cells outCells = workbook.getWorksheets().get(“out”).getCells();

FindOptions opt = new FindOptions();
opt.setLookAtType(LookAtType.CONTAINS);
opt.setCaseSensitive(false);
opt.setSearchNext(true);
opt.setLookInType(LookInType.FORMULAS);

Cell cell = inCells.find(“USINPUT”, null, opt);
while (cell != null) {

String cellFormula = cell.getFormula();

int beginIdx = cellFormula.indexOf(’(’);
int endIdx = cellFormula.indexOf(’,’, beginIdx);

String code = cellFormula.substring(beginIdx + 1, endIdx);

String value = inSheet.calculateFormula(code).toString();
outCells.get(cell.getRow(), cell.getColumn()).setFormula("=" + code);

cell = inCells.find(“USINPUT”, cell, opt);
}

workbook.calculateFormula(true);
outCells.removeFormulas();

workbook.save(“out1.xlsx”);
…


Hopefully, this helps a bit.

Thank you.

3

Hi. Thanks for your response, but provided code is not valid. In case, when formula will reference to another cell on current sheet (“in”) calculation this formula on sheet “out” will invalid.


API contains a method Worksheet.calculate(String formula)
is it possible to add like this method to a cell?

Best regards. Alexey
4

Hi,


Alright! We will check the feasibility if we could add Cell.calculate(String formula) method similar to Worksheet.calculateFormula(String formula) for your needs. Alternatively we will also check if there is any better way to cope with it. I have logged a ticket with an id “CELLSJAVA-40970” for your issue. We will look into it soon.

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

Thank you
5

Hi,

Thanks for using Aspose.Cells.

We have evaluated this issue further and we are afraid, we would not support Cell.calculate(String formula) method as it is not a
good idea.

There is a solution to implement your need, please check the
following code.

Java

public static void main(String[] args) {
// TODO
Auto-generated method stub

try
{
String myDir =
“D:/Filetemp/”;


Workbook source = new
Workbook(“d:\Filetemp\test.xlsx”);
Workbook dest = new
Workbook(“d:\Filetemp\test.xlsx”);
CustomFunction
customFunction = new
CustomFunction(dest.getWorksheets().get(“out”).getCells());

source.calculateFormula(true,customFunction);


dest.save(“d:\Filetemp\dest.xlsx”);


}catch(Exception
e)
{
System.out.println(e.getMessage());
}
}

public class
CustomFunction implements ICustomFunction
{
Cells
outCells;
public CustomFunction(Cells outCells)

{
this.outCells = outCells;
}
public Object
calculateCustomFunction(String functionName, ArrayList paramsList, ArrayList
contextObjects)
{
if
(“USINPUT”.equals(functionName))
{
Cell cell =
(Cell)contextObjects.get(2);
outCells.get(cell.getRow(),
cell.getColumn()).putValue(paramsList.get(0));
}

return null;
}

}

6

Thanks for response. Yep, i already use customfunction for this.

Best regards. Alexey
7

Hi,


Good to know that your issue is sorted out now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
8

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan