CODEWITHSUNDEEP
c#asp.netoffice-interop
Learner.123
Learner.123

Reputation: 177

Unable to delete empty rows from Excel file using c#

I am trying to delete empty rows from my Excel.

I have tried seeing other examples and even tried implementing but it doesn't work.What I need to do is delete an entire row If the first cell of that row is empty.

Here is what I have tried:

ReadExcel.cs:

Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Open(fileLocation)
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;
Excel.Range usedRange = worksheet.UsedRange;
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;

        for (int i = 1; i <= rowCount; i++)
        {
      if (usedRange.Cells[1, 1] == null)
             {
                // Delete entire row if first cell is empty
                usedRange.Cells[1, 1]).EntireRow.Delete(null);
            }

            workbook.Save();
        }

The problem that I face is that it keeps on looping through the excel and does not follow the if condition even when the cell is null

Upvotes: 3

Views: 1748

Answers (2)

Learner.123
Learner.123

Reputation: 177

Finally this works:

        Excel.Application excelApp = new Excel.Application();
        Excel.Workbook workbook = excelApp.Workbooks.Open(fileLocation, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
        Excel.Range usedRange = worksheet.UsedRange;
        int rowCount = usedRange.Rows.Count;
        int colCount = usedRange.Columns.Count;

        for (int i = rowCount; i >= 1; i--)
        {
            if (string.IsNullOrEmpty((worksheet.Cells[i, 1]).Text.ToString()))
            {
                // Delete entire row if first cell is empty
                (worksheet.Cells[i, 1]).EntireRow.Delete();
            }

}
        workbook.Save();

Upvotes: 0

jdweng
jdweng

Reputation: 34433

First you code is always deleting row 1. You should be moving through all the rows. Second whenever deleting always start at the end and move towards the beginning otherwise you can miss rows. The issue is if you delete row 4 then row 5 becomes 4 and you skip row 5. See code below

        for (int i = rowCount - 1; i >= 1; i--)
        {
             if (usedRange.Cells[i, 1] == null)
             {
                // Delete entire row if first cell is empty
                usedRange.Cells[i, 1]).EntireRow.Delete(null);
             }

        }
        workbook.Save();

Upvotes: 2

Related Questions