Find and Replace Text function in Excel spreadsheet in .NET

Aspose.Cells Product Family
1

Hi Team,

I am using Aspose word and cell for generating documents.

I used Aspose Word replace function which comes with extension "IReplacingCallback" where we can write custom function. Like edit color of the text being replace. I want to replace the text in Red Color font. I achieved same in word with IReplacingCallback interface.

I need to achieve same with Aspose cell also. In excel cell the replaced cell font color should be custom color. But I don’t find any callback function in excel 'Replace' function.

Could you please help me in achieving this. Please provide some API from Aspose Cell or sample code.

Best Regards,

Nakul



2

Hi,

Please see the code below. It finds the string hello from input xlsx file then it replaces it with string hello world in blue color and arial black font.

I have attached both the input and output xlsx file and you can also see the screenshot.

Java


String path = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook();

workbook.open(path);


Worksheet worksheet = workbook.getWorksheets().getSheet(0);


FindOptions opts = new FindOptions();

opts.setLookinType(FindOptions.LOOKIN_VALUES);


Cell cell = null;


//find each cell containing hello and replace it with

//blue color hello world in arial black font

do

{

cell = worksheet.getCells().find(“hello”, cell, opts);


if(cell!=null)

{

cell.setValue(“Hello World”);


Style style = cell.getStyle();


Font fnt = new Font();

fnt.setName(“Arial Black”);

fnt.setColor(Color.BLUE);


style.setFont(fnt);

cell.setStyle(style);

}


}while(cell!=null);


workbook.save(path+ “.out.xlsx”);

Screenshot:
3

Here is simplified version for .NET


MemoryStream fileData = new MemoryStream(inputExcelData);
Workbook book = new Workbook(fileData);
foreach (Worksheet s in book.Worksheets)
{
FindOptions opts = new FindOptions();

opts.LookInType = LookInType.Values;

Cell cell = null;

do
{
cell = s.Cells.Find(“Search Data”, cell, opts);

if (cell != null)
{
cell.HtmlString = “” + “New value”+ “”;
}
} while (cell != null);
}
4

Hi,

That’s great, you were able to port the code to .NET C#.

I mistakenly provided the Java code but still it was useful for you to get an idea.