Complicated Replacee in Excel

Aspose.Cells Product Family
1

I am trying to replace text within an Excel file. The text is dynamic, so I do not have a finite list from which to search. I have several items that i need to replace within the Excel file such as:

[CARS:Total], [CARS:Blue],[CARS:Red],[CARS:Green].

These strings may be located anywhere within the file. I am doing this succeffully in Word and PowerPoint. In Aspose.Words, I am using the following:

//Create a Regular Expression that can identify the text
System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("(\\[)[CARS:](.)*?(\\])");

//Set the default value for textFound. This will allow the first loop through the While
bool textFound = true;

//Loop through the document to replace all instances of the text
while (textFound)

{

//Use the Regular Expression defined above to match existing text in the word document
System.Text.RegularExpressions.Match expressionMatch = regex.Match(word.Range.Text);
//if a data marker is foound
if (expressionMatch.Success)
{
//Replace the text using the dataMarkerEvaluator Below.
//Make sure the isForward parameter is set to false. Otherwise,
//the initial pass will replace ALL text with the initial value
word.Range.Replace(regex, textEvaluator, false);
}

//If there is no match, we have reached the end of the document and there are is more text to replace
//so set textFound to false to exit the loop
else
{
textFound = false;
}
}

Is there a similar way to do this in Excel? I cannot find any overloads that accept a Regular Expression.

2

Hi,

Thanks for considering Aspose.

Well, In Aspose.Cells, you may easily replace the text in different cells by Replace() method of the Workbook class, which scans through all the worksheets of a workbook (excel file) :

Workbook wb = new Workbook();

wb.Open(@"e:\MyBook.xls");

wb.Replace("[Cars:Total]","newvalue");

wb.Save(@"e:\NewBook.xls");

For searching only, there are several methods of Aspose.Cells.Cells class: e.g., FindString(), FindStringStartsWith(), FindStringEndsWith(), FindStringContains(), FindNumber() etc. All the methods return the Cell object.

You can refer the sample code:

Workbook wb = new Workbook();

wb.Open(@"e:\MyBook.xls");

Worksheet sheet = wb.Worksheets[0];

Cell cell = sheet.Cells.FindStringStartsWith("[CARS:",null);

// null means searching starts from the first cell of the sheet.

MessageBox.Show(cell.Name);

Regards

Amjad Sahi

Aspose Nanjing Team