Wiki
Clone wikiAspose for OpenXML / Find and Replace in Spreadsheet
When we click on find button following is the code:
if (TXBX_Find.Text != "") { workbook = new Workbook(FOD_OpenFile.FileName); FindOptions Opts = new FindOptions(); Opts.LookInType = LookInType.Values; Opts.LookAtType = LookAtType.Contains; string found = ""; Cell cell = null; foreach (Worksheet sheet in workbook.Worksheets) { found += Environment.NewLine + "Sheet: " + sheet.Name + ":"; do { cell = sheet.Cells.Find(TXBX_Find.Text, cell, Opts); if (cell != null) found += cell.Name + ","; } while (cell != null); } LBL_FindResults.Text = found; }
Clicking on Replace button following code is executed:
if (TXBX_Find.Text != "" && TXBX_Replace.Text!="") { workbook = new Workbook(FOD_OpenFile.FileName); FindOptions Opts = new FindOptions(); Opts.LookInType = LookInType.Values; Opts.LookAtType = LookAtType.Contains; string found = ""; Cell cell = null; foreach (Worksheet sheet in workbook.Worksheets) { do { cell = sheet.Cells.Find(TXBX_Find.Text, cell, Opts); if (cell != null) { string celltext = cell.Value.ToString(); celltext = celltext.Replace(TXBX_Find.Text, TXBX_Replace.Text); cell.PutValue(celltext); } } while (cell != null); } LBL_FindResults.Text = "Replaced All Existing Values, Save the file now"; }
Download
Updated