Gembox find and update cell

Question

I am trying to update an excel file with gembox. I want gembox to search the excel file for a certain string, and update all matches. But the method FindText() only returns the first occurrence.

I tried the following, but it causes an infinite loop, for some reason the row and column won't update to the next search result.

  private void RenameSubCategory(ExcelWorksheet sheet, string subCategoryOld, string subCategoryNew)
  {
     int row, column;
     while (sheet.Cells.FindText(subCategoryOld, false, false, out row, out column)) {
        sheet.Cells[row, column].Value = subCategoryNew;
     }
  }

score 1 · Accepted Answer · answered May 08 '17 at 05:53

Is it possible that your subCategoryNew text contains subCategoryOld text?
In other words, do you perhaps have something like the following:

RenameSubCategory(sheet, "Sample", "Sample New");

This would result in infinite loop, because you'll always find "Sample" in a cell that was set to "Sample New" text.
Instead try using one of the ReplaceText methods, like the following:

sheet.Cells.ReplaceText("Sample", "Sample New");

Or try using the following:

private static void RenameSubCategory(ExcelWorksheet sheet, string subCategoryOld, string subCategoryNew)
{
    foreach (ExcelRow row in sheet.Rows)
        foreach (ExcelCell cell in row.AllocatedCells)
            if (cell.ValueType == CellValueType.String &&
                cell.StringValue.Contains(subCategoryOld))
                cell.Value = subCategoryNew;
}

I hope this helps.

Gembox find and update cell

1 Answers1