Table of Contents

TExcelFile.Replace Method

Overloads

TExcelFile.Replace(TCellValue, TCellValue, TXlsCellRange, Boolean, Boolean, Boolean)

Replaces the instances of oldValue by newValue in the active sheet.

Syntax

Unit: FlexCel.Core

function TExcelFile.Replace(const oldValue: TCellValue; const newValue: TCellValue; const Range: TXlsCellRange; const CaseInsensitive: Boolean; const SearchInFormulas: Boolean; const WholeCellContents: Boolean): Integer; overload;

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.

Returns

The number of replacements done.

Examples

To replace all cells on a sheet that contain "hello" with "hi":

  xls.Replace('hello', 'hi', TXlsCellRange.Null, true, true, false);

See also

TExcelFile.Replace(TCellValue, TCellValue, TXlsCellRange, Boolean, Boolean, Boolean, TProc<TReplaceAction>)

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.Core

function TExcelFile.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; virtual; abstract;

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