Best way to find and replace format in range?

Aspose.Cells Product Family
1

Hi,


We are using Aspose.Cells to migrate some Excel VBA functionality to C# and looking for the best solution to apply Excel VBA “Find and Replace” format functionality for the range of cells with Aspose.Cells component (see Excel VBA example below). We have solution to loop through all cells in range and manualy replace necessary format, but it is performance consuming.

Excel VBA code example:
Application.FindFormat.Color = oldColor
Application.ReplaceFormat.Color = newColor
cellRange.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

Thank you in advance!
2

Hi,

For finding and searching data, please see Find or Search Data

For applying style and format range, please see Copy Range Data with Style and more complete example Format Worksheet Cells in a Workbook

3

Hi,

Can i find cells with specified format, for example cells with blue background color?

4

Hi,

Yes, you can do it, you will have to check the background color of all the cells and find them out.

For example int this following code. It will change the font color by iterating all cells.

You can download the source file and the output file generated by this code.

Please see the screenshot for more illustration

C#


string filePath = @“F:\source.xlsx”;

//Load a source workbook
Workbook wb = new Workbook(filePath);


//Iterate all worksheets
for (int i = 0; i < wb.Worksheets.Count; i++)
{
Cells cells = wb.Worksheets[i].Cells;

//Set the font color of all the cells to black which has blue font color
for (IEnumerator ie = cells.GetEnumerator(); ie.MoveNext(); )
{
Cell cell = (Cell)ie.Current;

Style style = cell.GetStyle();

int rgb = style.Font.Color.ToArgb() & 0xFFFFFF;

if (rgb == (Color.Blue.ToArgb() & 0xFFFFFF))
{
style.Font.Color = Color.Black;
cell.SetStyle(style);


}//if
}//for
}//for


//Save the worbook
wb.Save(filePath + “.out.xlsx”);

Screenshot: