I want to edit the formula =SUM(Sheet1!A1:A10) and change its range from A1:A10 to A1:B20.
Is there any way to do this?
You can easily update the formula of the cell using Aspose.Cells API. See the sample code for your reference:
e.g.
Sample code:
Workbook workbook = new Workbook(stringFilePath);
Worksheet sheet = workbook.getWorksheets().get(0);
Cell cell = sheet.getCells().get("E4");
cell.setFormula("=SUM(Sheet1!A1:B20)");
workbook.save("f:\\files\\out1.xlsx");
Hope, this helps a bit.
Thanks for reply
But I want to edit formulaes dynamically and set it to the same cell basically want to update. And there might be multiple formulaes.
Following are the two ways to set/update formulas dynamically.
- Browse/loop through range of cells in the worksheets and update each cell formula accordingly.
- Use Find/Search options provided by Aspose.Cells API. You can search your formula in the worksheet and then update formula strings accordingly. See the sample code for your reference:
e.g.
Sample code:
Workbook workbook = new Workbook("f:\\files\\Book1.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
FindOptions opt = new FindOptions();
opt.setLookAtType(LookAtType.CONTAINS);
opt.setCaseSensitive(false);
opt.setSearchNext(true);
opt.setLookInType(LookInType.FORMULAS);
Cell cell = cells.find("your_formula_string", null, opt);
while (cell != null)
{
String cellFormula = cell.getFormula();
//......
//your code goes here.
//......
cell.setFormula(.......);
}
.........
Hope, this helps a bit.
Thanks for the code
Actually I tried the above code but its not what I am looking for.
What I am doing is looping through the cells and then reading the formula using cell.getFormula() and lets say I get a formula like this =SUM(Sheet1!A1:A10) for a particular cell. Now I want to edit the range i.e A1:A10 to A1:A14 and then set the updated formula =SUM(Sheet1!A1:A14) to the same cell.
So can I change the range in the formula dynamically and then update it?
@Amjad_Sahi
image.png (4.7 KB)
In first image I have added more rows in sheet1 and then extend the formula range in sheet2 in second image.
image.png (9.9 KB)
Is there something available like this that we can achieve through aspose. Like to extend the range in formula on adding rows in the reference cells.
There is no such feature available in Aspose.Cells API. Moreover, it is more related to UI feature of MS Excel. You may efficiently do it using the following sample code via Aspose.Cells, you got to provide your desired replacement range though.
e.g.
Sample code:
Workbook workbook = new Workbook("f:\\files\\Book1.xlsx");
Worksheet sheet = workbook.getWorksheets().get(1);
Cells cells = sheet.getCells();
Cell cell;
Cell prevcell = null;
FindOptions opt = new FindOptions();
opt.setLookAtType(LookAtType.CONTAINS);
opt.setCaseSensitive(false);
opt.setLookInType(LookInType.FORMULAS);
String searchFormulaText = "=SUM(Sheet1!A1:A9)";
do {
cell = cells.find(searchFormulaText, prevcell, opt);
if (cell == null)
break;
String cellFormula = cell.getFormula();
int beginIdx = cellFormula.indexOf('!');
int endIdx = cellFormula.indexOf(')', beginIdx);
String code = cellFormula.substring(beginIdx + 1, endIdx);
String newCode = "A1:A20";
System.out.println(cellFormula.substring(0,beginIdx+1));
cell.setFormula(cellFormula.substring(0,beginIdx +1) + newCode + ")");
prevcell = cell;
} while (cell != null);
workbook.save("f:\\files\\out1.xlsx");
Thank you for the code.
I tried above approach by editing the formula string and it works.
But will it be feasible for even complex formulaes like -
=VLOOKUP(A5,DirectEquity_Data!$A$2:$B$1048576,2,FALSE)
The above sample code is just an example from which you may get hints and write/update your own code and logic accordingly for complex formulas for your custom needs.
okay and also can we check if a formula contains a range or not?
Like =SUM(Sheet1!A1:A10) it conatins A1:A10 and =Client_Data!A3 it has no range.
I think you may try to use Trace Precedents feature of Excel for it. See the document on tracing precedents and dependents with example code for your complete reference. So, you may get referred area collection for a (formula) using cell’s precedents. Now you can evaluate referred area if it has range like “A1:A10” or single cell.
I have one more doubt.
Like pivot tables can we detect simple tables if there are multiple tables in a sheet?
If no than is there any way I can do it ?
Tables in MS Excel spreadsheet are denoted by ListObject collection in Aspose.Cells API. So, you may easily evaluate if there are Tables/ListObjects in the worksheet. You may use worksheet.getListObjects().getCount() to get the total number of tables/list objects in the worksheet.
Please note, Aspose.Cells can get/detect MS Excel’s Table feature (taken as ListObject) in the worksheet. If you have created and formatted data (organized) in rows/columns manually, this will not be taken as Excel’s Table, MS Excel will not detect it as Table either. If you still think you have Table (Excel’s feature) in the worksheet which is not detected by Aspose.Cells, please zip and attach your Excel file, we will check it soon.
I have manually created files.
So there is no other way? I tried using named range and it works but I am not suppose use that.