Free Support Forum - aspose.com

Find and Replace Text function in Excel spreadsheet in .NET

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



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:

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);
}

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.