Replace method looking for the text in entire cell

Aspose.Cells Product Family
1

Replace method seems to be looking for the text in the entire cell. Cant I replace part of the text within a cell? or am I missing something?

Kiran

2

Hi Kiran,

You can try the following approach:

//Find cell text contains “abc” and replace it with "def"
Cells cells = excel.Worksheets[0].Cells;

Cell cell = null;

for(;Wink
{
cell = cells.FindStringContains(“abc”);
if(cell == null)
break;
string text = cell.StringValue;
cell.PutValue(text.Replace(“abc”, “def”));
}

3

Thanks Laurence. This worked great. I have 1 more issue though. I need to look for strings within a cell which starts with “a” & ends with “z” (there can be multiple strings within a cell.) Is there a way I could accomplish this?

If Aspose.Excel supported regular expression it would have been great.

thanks
Kiran

4

We will investigate the regular expression support.

And I think now it's also easy to meet your need. The following is the sample code:


for(int i = 0; i < cells.Count; i ++)
{
Cell cell = cellsIdea;
if(cell.Type == CellValueType.String)
{
string text = cell.StringValue;
//The you can use your regular expression to check the text
..............
}
}

5

@kiran,
Aspose.Excel is deprecated and no more active development is done for it. A new product Aspose.Cells has replaced Aspose.Excel and supports all the latest features of MS Excel. You can use Aspose.Cells to find and replace text in a variety of ways. Here is an example to use this feature:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Instantiate the workbook object
Workbook workbook = new Workbook(sourceDir + "sampleFindingDataOrFormulasUsingFindOptions.xlsx");

workbook.CalculateFormula();

// Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;

// Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();

// Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 8;
ca.StartColumn = 2;
ca.EndRow = 17;
ca.EndColumn = 13;

// Set cells area for find options
findOptions.SetRange(ca);

// Set searching properties
findOptions.SearchBackward = false;
findOptions.SeachOrderByRows = true;

// Set the lookintype, you may specify, values, formulas, comments etc.
findOptions.LookInType = LookInType.Values;

// Set the lookattype, you may specify Match entire content, endswith, starwith etc.
findOptions.LookAtType = LookAtType.EntireContent;

// Find the cell with value
Cell cell = cells.Find(341, null, findOptions);

if (cell != null)
{
    Console.WriteLine("Name of the cell containing the value: " + cell.Name);
}
else
{
    Console.WriteLine("Record not found ");

You may follow below links for more details about searching text:
Finding Cells Containing Specified String Value or Number
Find or Search Data
Replace text in a workbook using Regular Expression

The latest version of this new product can be downloaded here:
Aspose.Cells for .NET(Latest version)

Here is a runnable complete solution which contains lot of examples to test different features of Aspose.Cells.