Find and Replace in Formulae

After copying worksheets around I need to do a find and replace on all the formulae in a worksheet.

In interop I used to simply do

Worksheet.Cells.Replace("x", "y");

The worksheets I am working on have literally hundreds if not thousands of formulae.

Please advise.

Jason

Hi,


Thanks for your query.

Please see the following sample code with the attached template file for your needs for your reference. I have a simple template file (attached) which contains formulas in the first worksheet. I use Aspose.Cells’ Find/Search APIs to find those formulas in the worksheet which have your desired character/string, then, I replace the formulas’ string with your desired string, Finally I re-saved the Excel file. Please refer to the sample code and you may add/update the code segment accordingly for your needs.
e.g
Sample code:

LoadOptions loadOptions = new LoadOptions(LoadFormat.Auto);
Workbook rs = new Workbook(“e:\test2\Book1.xlsx”, loadOptions);
string searchValue = “X”;
string replaceValue = “Y”;

foreach (Worksheet s in rs.Worksheets)
{
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.OnlyFormulas;
opts.LookAtType = LookAtType.Contains;
Cell cell = null;
do
{
cell = s.Cells.Find(searchValue, cell, opts);

if (cell != null)
{
string value = cell.Formula;
string svalue = value.Replace(searchValue, replaceValue);
cell.Formula = svalue;
}
} while (cell != null);
}
rs.Save(“e:\test2\out1Book1.xlsx”);

Also, see the document for your further reference:

Hope, this helps a bit.

Thank you.

Thanks for your help.

I will give this a try.

I also need to do a find and replace in a chart's series formula.

I used to do it like this...

Series.Formula = Series.Formula.Replace("x", "y");

Can you help?

Jason

Hi,


Well, if you need to get the series’ values/data, you may try to browse the series of the chart using the following attributes of Series class, see the code segment below for your reference.
e.g
Sample code:

//Get the first chart in the first worksheet.
var chart = workbook.Worksheets[0].Charts[0];
//Get the first series details.
var series = chart.NSeries[0];
string sname = series.Name;
string seriesvalues - series.Values;
string seriesxvalues = series.XValues;

Hope, this helps a bit.

Thank you.