Documents for Excel .NET Edition | GCDocuments
Features / Worksheet / Range Operations / Find and Replace Data
In This Topic
    Find and Replace Data
    In This Topic

    In a spreadsheet with hundreds of rows and columns, it becomes difficult to look for specific chunks of data across the entire worksheet and even more cumbersome to edit this information. The find and replace feature makes it easy for users to locate information and replace it within seconds, thereby saving both time and efforts.

    GcExcel .NET enables users to locate data in a cell range, find specific information (and all its occurrences) across the worksheet and replace it with the desired information. Using this feature, you can find and replace specific values and formulas in a range as per custom requirements and preferences with the help of the following methods.

    Users can find basic information, locate cells with different formats, search data using various options, enumerate all occurences across the worksheet, match the number of bytes occupied by the data and look for specific data in different places including comments, formula and text. Further, you can replace the basic information, replace via executing the search operation in loop and also replace using several options (like match case, match whole word and match byte).

    Refer to the following example code in order to find cells in a target range starting from multiple positions and replace it with the desired information.

    C#
    Copy Code
    // This example finds the word "newyork" in multiple searchRanges & replaces it with "NewYork"
            
    // Initialize workbook
    Workbook workbook = new Workbook();
            
    // Fetch default worksheet 
    IWorksheet worksheet = workbook.Worksheets[0];
           
    object[,] data = new object[,]{
    {"Name", "City", "Birthday", "Sex", "Weight", "Height", "Age"},
    {"Bob", "newyork", new DateTime(1968, 6, 8), "male", 80, 180, 56},
    {"Betty", "newyork", new DateTime(1972, 7, 3), "female", 72, 168, 45},
    {"Gary", "NewYork", new DateTime(1964, 3, 2), "male", 71, 179, 50},
    {"Hunk", "Washington", new DateTime(1972, 8, 8), "male", 80, 171, 59},
    {"Cherry", "Washington", new DateTime(1986, 2, 2), "female", 58, 161, 34},
    {"Coco", "Virginia", new DateTime(1982, 12, 12), "female", 58, 181, 45},
    {"Lance", "Chicago", new DateTime(1962, 3, 12), "female", 49, 160, 57},
    { "Eva", "Washington", new DateTime(1993, 2, 5), "female", 71, 180, 81}};
    
    // Set data
    worksheet.Range["A1:G9"].Value = data;
    worksheet.Range["I10:P19"].Value = data;
    worksheet.Range["A21:G29"].Value = data;
    
    object what = "newyork";
    object replacement = "NewYork";
    ReplaceOptions ro = new ReplaceOptions();
    ro.MatchCase = true;
    
    // Specify multiple ranges to search in
    IRange searchRange = worksheet.Range["A1:G9, I10:P19"];
    
    // Using Replace method to replace content in a specific range
    searchRange.Replace(what, replacement, ro);
    
    // Saving workbook to xlsx
    workbook.Save(@"FindAndReplaceContentUsingReplaceOptions.xlsx", SaveFileFormat.Xlsx);

    Refer to the following example code in order to find cells with the formula "SUM" and replace it with another formula "PRODUCT" simultaneously.

    C#
    Copy Code
    // This code finds the "SUM" keyword & replaces it with "PRODUCT" keyword in the formula. 
            
    // Initialize workbook
    Workbook workbook = new Workbook();
            
    // Fetch default worksheet 
    IWorksheet worksheet = workbook.Worksheets[0];
            
    // Set formulas
    worksheet.Range["A1:H5"].Formula = "SUM(6,10)";
    
    FindOptions fo = new FindOptions();
    fo.LookIn = FindLookIn.Formulas;
            
    IRange range = null;
            
    // Specify range to search in formulas
    IRange searchRange = worksheet.Range["A1:B4"];
    do
    {
        range = searchRange.Find("SUM", range, fo);
        if (range != null)
        {
            // using Replace method to replace formula in searched range
            range.Formula = range.Formula.Replace("SUM", "PRODUCT");
        }
    } while (range != null);
    
    // Saving workbook to xlsx
    workbook.Save(@"FindAndReplaceFormulasUsingFindOptions.xlsx", SaveFileFormat.Xlsx);