TXlsFile.Replace Method

Replaces the instances of oldValue by newValue in the active sheet, and allows to specify the cell format and value for every replaced cell.

Syntax

Unit: FlexCel.XlsAdapter

function TXlsFile.Replace(const oldValue: TCellValue; const newValue: TCellValue; const Range: TXlsCellRange; const CaseInsensitive: Boolean; const SearchInFormulas: Boolean; const WholeCellContents: Boolean; const ReplaceAction: TProc<TReplaceAction>): Integer; overload; override;

Parameters

<->	Parameter	Type	Description
const	oldValue	TCellValue	Value we want to replace.
const	newValue	TCellValue	Value we want to use to replace oldValue.
const	Range	TXlsCellRange	Range to Search. Null means the whole worksheet.
const	CaseInsensitive	Boolean	If true, string searches will not be case sensitive, "a" = "A"
const	SearchInFormulas	Boolean	If true, the search will cover formulas too.
const	WholeCellContents	Boolean	If true, only whole cells will be replaced.
const	ReplaceAction	TProc<TReplaceAction>	Action to be performed in every replacement.

Returns

The number of replacements done.

Examples

To replace all cells on a sheet that contain 1999-01-01 with 2003-01-01, formatting the cells in column 3 as blue:

  xls.Replace(EncodeDate(1999, 1, 1), EncodeDate(2003, 1, 1), TXlsCellRange.Null,
    true, true, true,
    procedure (x: TReplaceAction)
    var
      fm: TFlxFormat;
    begin
      if x.Col = 3 then
      begin
        fm := xls.GetFormat(x.XF);
        fm.FillPattern.FgColor := Colors.Red;
        fm.FillPattern.Pattern := TFlxPatternStyle.Solid;
        x.XF := xls.AddFormat(fm);
      end;

    end);

See also

• TXlsFile