We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Aspose replace formula in entire worksheet

Hi

Please could you help me replace an expression in a formula in the entire sheet. I've gone through all the forums and havent found a solution to replace in the entire sheet.

Currently the available Find method returns only a single cell rather than a range or collection which makes it difficult to replace in the entire sheet.

Please could you advise the right method to use in such a scenario.

Thanks & Regards,

Swapna Deshpande.

Hi,


Well, I am afraid, there is not any single method that could replace some contents in all your formulas, there is a method i.e. Workbook.Replace but it would replace the values not the formulas after finding them out. Well, you may easily use some loop and find out your each desired formula and then update the formula accordingly, see the sample code below for your reference, you may refer to it and update the code segment and write/embed your own accordingly for your requirements:
e.g
Sample code:

Workbook wb = new Workbook(@“e:\test2\Book1.xlsx”);


FindOptions findOptions = new FindOptions();
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = wb.Worksheets[0].Cells.MaxDataRow;
ca.EndColumn = wb.Worksheets[0].Cells.MaxDataColumn;
findOptions.SetRange(ca);
findOptions.LookAtType = LookAtType.StartWith;
findOptions.LookInType = LookInType.OnlyFormulas;

Aspose.Cells.Cell foundcell;
Aspose.Cells.Cell prevcell;

wb.CalculateFormula();

foundcell = null;
prevcell = null;
int i = 2;

do
{
foundcell = wb.Worksheets[0].Cells.Find("=SUM(", prevcell, findOptions);

if (foundcell == null)
{

break;

}

else
{
foundcell.Formula = foundcell.Replace("$", string.Empty);
// i++;


}

prevcell = foundcell;

} while (foundcell != null);


wb.Save(@“e:\test2\out1.xlsx”, SaveFormat.Xlsx);


Hope, it helps a bit.

Thank you.