I would like to find and replace a group of text in Excel using C#, moreover I want this replace to happen to text in the first row only.
I have used Google and found a few paid resource like Aspose API,Spire.Xls,etc, but I am looking for an open source resource or any other efficient way to achieve this. Please suggest.
Try this:
Public static void ReplaceTextInExcelFile(string filename, string replace, string replacement)
{
object m = Type.Missing;
// open excel.
Application app = new ApplicationClass();
// open the workbook.
Workbook wb = app.Workbooks.Open(
filename,
m, false, m, m, m, m, m, m, m, m, m, m, m, m);
// get the active worksheet. (Replace this if you need to.)
Worksheet ws = (Worksheet)wb.ActiveSheet;
// get the used range.
Range r = (Range)ws.UsedRange;
// call the replace method to replace instances.
bool success = (bool)r.Replace(
replace,
replacement,
XlLookAt.xlWhole,
XlSearchOrder.xlByRows,
true, m, m, m);
// save and close.
wb.Save();
app.Quit();
app = null;
}
Here is a code that worked for me
Excel.Range range = exWS.UsedRange;
range.Replace2("look_for", "replace", Excel.XlLookAt.xlWhole);
