CODEWITHSUNDEEP
c#excel
HKM
HKM

Reputation: 21

Find and replace text in Excel using C#

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.

Upvotes: 2

Views: 9453

Answers (2)

Ahmed Fox
Ahmed Fox

Reputation: 11

Here is a code that worked for me

Excel.Range range = exWS.UsedRange;
range.Replace2("look_for", "replace", Excel.XlLookAt.xlWhole);

Upvotes: 1

Rajendra Yadav
Rajendra Yadav

Reputation: 145

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;
}

Upvotes: 6

Related Questions