CODEWITHSUNDEEP
c#.netexcelautomationoffice-interop
thilanka1989
thilanka1989

Reputation: 77

Excel Interop iterate through rows and conditionally delete entire row missing some rows

I am building an excel automation programme and I need to remove specific rows conditionally. For an example I have 224 rows of specific publisher. Within that 224 rows 160 rows are invalid and I need to delete that 160. But when I iterate through rows and conditionally remove that 160 rows only 138 is deleted. 22 rows always remains in the sheet. I cannot find what the reason is? The code is written below.

            List<string> removeList = listofRemovalOfSpecificPublishers;

            string path = "C:\\Library Automation\\Source\\publishers.xlsx";
            var workBookData = GetWorkBookData(path, "Booking Publishers Info");
            mWSheet1 = workBookData.mWorkSheet1;
            Microsoft.Office.Interop.Excel.Range xlRange;

            xlRange = mWSheet1.UsedRange;

            for (int i = 1; i <= xlRange.Rows.Count; i++)
            {
                string publisher = Convert.ToString(xlRange.Cells[i, 3].Value); //publisher column
                string bookState = Convert.ToString(xlRange.Cells[i, 8].Value); //status column
                if (carrier != "Publisher" && Convert.ToString(xlRange.Cells[i, 1].Value) != "Status")
                {

                    if (removeList.Contains(publisher) && bookState == "INVALID")
                    {
                        xlRange.Cells[i, 1].EntireRow.Delete(null);

                    }
                }

            }

            workBookData.mWorkbook.RefreshAll();
            workBookData.mWorkbook.Save();
            workBookData.mWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);

I need to know is there something I have missed.Thank you for your help and time.

Upvotes: 0

Views: 438

Answers (1)

Shirayuki
Shirayuki

Reputation: 189

Pretty sure Tim Williams has answered your question, but I will add a little clarification on the matter.

When you are running a for-loop through the list of rows from top to bottom, say, you are at row 10, index is at 10 and it fits your criteria to be deleted, it will delete row 10, then jump to row at the index = index + 1.

And that's when the problem arises. When you delete the row at 10, it pushes all the rows below it up by one row, so row at the index of 11, is actually row 12, and the old row at the index of 11, is now at the index of 10.

So, yeah, whenever you are working with deletion, whether rows or columns, always go bottom up.

Upvotes: 1

Related Questions