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++)
{
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")
{
xlWorksheet.Cells[j, 2] = NewPolicynumber;
}
}
}
xlWorkbook.SaveAs(savepath);
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}