deleting rows from an excel file using c#

Question

I am opening an excel file like this:

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;

string str;
int rCnt = 0;
int cCnt = 0;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

I would like to know:
How do I loop through all the rows and delete every row in which the string SomeString does not appear in column A?

I know how to loop through every value:

for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
    for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
    {
        str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
        MessageBox.Show(str);
    }
}

But I do not know how to delete the entire row

You should be able to use: 'range.EntireRow.Delete(Excel.XLDirection.xlUp)' - just set the range with the worksheet object (xlWorksheet). — Patrick Pitre
– Patrick Pitre, Commented Mar 30, 2012 at 22:57
It would look like: range = xlWorksheet.get_Range("A1", "B1"); range.EntireRow.Delete(Excel.XLDirection.xlUp); — Patrick Pitre
– Patrick Pitre, Commented Apr 2, 2012 at 18:16

Mircea Ion · Accepted Answer · 2012-03-31 00:47:55Z

15

Once you have a reference to the worksheet say

for(int i = 1; i <=100; i++)
{
  if(!worksheet.Cells[i,1].Contains("SomeString"))
  {
     ((Range)worksheet.Rows[i]).Delete(shiftDirection)
  }
}

where shiftDirection see here: Range.Delete method

You may have to cast the Cell's content to a string.

edited Mar 31, 2012 at 0:47

answered Mar 31, 2012 at 0:41

Mircea Ion

7065 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ninja · Accepted Answer · 2021-01-18 02:48:00Z

-1

I know the question has been already answered, but you can try something like below

 for (int i = dt.Rows.Count - 1; i >= 0; i--)
     {
         if (!dt.Rows[i]["column A"].Contains("SomeString "))
         {
             dt.Rows.RemoveAt(i);
         }
     }

edited Jan 18, 2021 at 2:48

answered Apr 10, 2018 at 11:40

Ninja

3485 silver badges12 bronze badges

Collectives™ on Stack Overflow

deleting rows from an excel file using c#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related