How to do inner loop in C#

See more:

C#3.0

.NET

COM

Excel-2016

Hi there, I am actuali doing some research work for my academic project. I can find solution in other excel opensource packages for this problem but still i wonder how to overcome this issue in Excel interoperability and i have to use that only.

I will simplify my issue with a scenario. consider I am creating Insurance policy app and which will store all enrollment details of a person. Now I want to update a new policy number for all having ID=01. I have others details with same policy number that also i have to update. But should Update ID!=0 so ID-01 will keep old policy number other ID's policy number(same policy number for ID-01,02,03,01-employee,02-for kids-03-for parents) will change.

in my code what I have done is,

1.I have a function named "UpdatePolicyNoWithSSN"
2.this function will take one row of excel and its new SSN number
3.i will query to database with this SSN and I will get a new POLICY NUMBER
4.Find the Old POLICY_NO of excel and Check any duplicates, if yes Replace all with NEW POLICY_NO but not replace id=01
5.Create a new excel sheet with changed POLICY_NO with all ID except ID=01.

But I Am stuck how to loop through all duplicates Policy numbers and check next record with SSN again loop duplicates. Appreciated the help!!!
Thanks in advance!

my code:

What I have tried:

for (int j = 2; j <= lastUsedRow; j++)//here the lenghth should be duplicate value count of each policy i think is that the right way??
        {

            Range ID = (Range)xlWorksheet.Cells[j, 17];
            Range OldpolicyNumb = (Range)xlWorksheet.Cells[j, 2];
            Range cell = (Range)xlWorksheet.Cells[j, 2];
            if ((string)cell.Value == OldpolicyNumb.Value && ID.Value != "01") //cast to string to avoid null reference exceptions
            {
                xlWorksheet.Cells[j, 2] = NewPolicynumber;
            }

        }


    }

    //save as ne excel
    xlWorkbook.SaveAs(savepath);

    //cleanup  
    GC.Collect();
    GC.WaitForPendingFinalizers();

    //release com objects to fully kill excel process from running in the background  
    Marshal.ReleaseComObject(xlRange);
    Marshal.ReleaseComObject(xlWorksheet);

    //close and release  
    xlWorkbook.Close();
    Marshal.ReleaseComObject(xlWorkbook);

    //quit and release  
    xlApp.Quit();
    Marshal.ReleaseComObject(xlApp);

}