findAndReplace method

int findAndReplace(Pattern source, String target, { int first = -1, int startingRow = -1, int endingRow = -1, int startingColumn = -1, int endingColumn = -1, })

Returns the count of replaced source with target

source is Pattern which allows you to pass your custom RegExp or a simple String providing more control over it.

optional argument first is used to replace the number of first earlier occurrences

If first is set to 3 then it will replace only first 3 occurrences of the source with target.

  excel.findAndReplace('MySheetName', 'sad', 'happy', first: 3);

  or

  var mySheet = excel['mySheetName'];
  mySheet.findAndReplace('sad', 'happy', first: 3);

In the above example it will replace all the occurences of sad with happy in the cells

Other options are used to narrow down the starting and ending ranges of cells.

Implementation

int findAndReplace(Pattern source, String target,
    {int first = -1,
    int startingRow = -1,
    int endingRow = -1,
    int startingColumn = -1,
    int endingColumn = -1}) {
  int replaceCount = 0,
      _startingRow = 0,
      _endingRow = -1,
      _startingColumn = 0,
      _endingColumn = -1;

  if (startingRow != -1 && endingRow != -1) {
    if (startingRow > endingRow) {
      _endingRow = startingRow;
      _startingRow = endingRow;
    } else {
      _endingRow = endingRow;
      _startingRow = startingRow;
    }
  }

  if (startingColumn != -1 && endingColumn != -1) {
    if (startingColumn > endingColumn) {
      _endingColumn = startingColumn;
      _startingColumn = endingColumn;
    } else {
      _endingColumn = endingColumn;
      _startingColumn = startingColumn;
    }
  }

  int rowsLength = maxRows, columnLength = maxColumns;

  for (int i = _startingRow; i < rowsLength; i++) {
    if (_endingRow != -1 && i > _endingRow) {
      break;
    }
    for (int j = _startingColumn; j < columnLength; j++) {
      if (_endingColumn != -1 && j > _endingColumn) {
        break;
      }
      final sourceData = _sheetData[i]?[j]?.value;
      if (sourceData is! TextCellValue) {
        continue;
      }
      final result = sourceData.value.replaceAllMapped(source, (match) {
        if (first == -1 || first != replaceCount) {
          ++replaceCount;
          return match.input.replaceRange(match.start, match.end, target);
        }
        return match.input;
      });
      _sheetData[i]![j]!.value = TextCellValue(result);
    }
  }

  return replaceCount;
}