Hi there, we have purchase Aspose.cells for Java Developer OEM license. When we use it, we encounter some problem. So could you help to confirm:
Could we get the formula string that only contains the raw value and operator? eg: get "=75D7+50E7+35F7" from cell defined with formula like =SUM(D15:F15), And D15=75D7, E15=50E7, F15=35F7
Could we get “=D15+E15+F15” instead of “=SUM(D15:F15)” when we want to get the formula content?
Do you have some recommendation for us to read the formula content?
You mentioned requirements/custom needs cannot be linked as issues/problem in Aspose.Cells APIs.
1 ) and 2 ) How could you do that in MS Excel manually? I do no think any option or single command which could do it in MS Excel either that could convert all the cells (names) involved in the formula range to its corresponding values in formula bar. If you know on how to do it via MS Excel, let us know with details and sample file, we will check it. May be you could use tracing precedents and dependents feature to trace down those cells first and then get their values accordingly via Aspose.Cells APIs (e.g you may use Cell.getStringValue(), Cell.getIntValue(), Cell.getFloatValue(), etc.).
In short, you have to devise your own method and write your own code to accomplish the task via using Cell’s precedents. I have written a sample code that might extract different parts, please refer to it and you may write your own codes accordingly.
e.g. Sample code:
Workbook wb = new Workbook("in.xlsx");
wb.calculateFormula();
Cells cells = wb.getWorksheets().get(0).getCells();
Cell cell = cells.get("A1");
if(cell.isFormula())
{
//Tracing precedents of the cell A1.
ReferredAreaCollection ret = cell.getPrecedents();
//Printing all the precedents with their data
if(ret != null)
{
for(int m = 0 ; m < ret.getCount(); m++)
{
ReferredArea area = ret.get(m);
StringBuilder stringBuilder = new StringBuilder();
String cellname = CellsHelper.cellIndexToName(area.getStartRow(), area.getStartColumn());
System.out.println("Cell name: " + cellname + ", value: " +cells.get(cellname).getStringValue());
}
}
}
.........