Find and Replace in Spreadsheet
Contents
[
Hide
]
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";
}