Free Support Forum - aspose.com

Complicated Replacee in Excel

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.

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