Click here to Skip to main content
15,072,051 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

C#
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);

}
Posted
Updated 4-Sep-21 3:19am
v7

1 solution

if I understand your question correctly, the inner loop should start from the next row where the outer loop is. This way you won't find the same row as a duplicate.

For example when i=123 then j should start from row 124 and so forth.

Perhaps something like
C#
for (int j = i+1; j <= lastUsedRow; j++)
   
Comments
Manidas Payyoli 4-Sep-21 2:22am
   
I have tried that but my policy number is coming differently.ID 01,02,03 have same policy number.getting new policynumber based on SSN(id-01 will give)that new policy number i have to update 02,03 ids not 01 id that will keep the old existing id
Manidas Payyoli 4-Sep-21 2:29am
   
and its skipping my first row also
Manidas Payyoli 4-Sep-21 2:53am
   
I need to loop through all SSN colom, if id=01 get new policy number,update policy number with old policy number of 02,03 means 02 and 03, 01 are same policy number.only update 02 and 03 with new policynumber 01 keep as it is.Again go bak to mail loop take next SSN check if id=01 if not take next SSN and continue as it is
Wendelius 4-Sep-21 3:02am
   
Can you provide an example data? Use the "Improve question" button to modify it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900