How to remove empty rows and reduce total rows count in excel using NPOI with C#

Question

I am trying to remove empty rows in excel file using npoi with C#. i am facing the problem with my code can any one help on this please.

public static void removeRow(string filepath,string SheetName) 
    {
    try {
            IWorkbook workBook = WorkbookFactory.Create(new FileStream(
                          Path.GetFullPath(filepath),
                          FileMode.Open, FileAccess.Read,
                          FileShare.ReadWrite));
            ISheet workSheet = workBook.GetSheet(SheetName);
            int lastRowNum = workSheet.LastRowNum;
            for (int i = 0; i < lastRowNum; i++)
            {
                string value = ExcelUtils.getCellData(filepath, SheetName, i, 1);
                if (value.Equals(""))
                {
                    workSheet.ShiftRows(i + 1, lastRowNum, -1);
                    lastRowNum = workSheet.LastRowNum;
                    i--;
                    using (FileStream stream = new FileStream(filepath, FileMode.Create, FileAccess.ReadWrite))
                    {
                        workBook.Write(stream);
                        stream.Close();
                    }
                }
            }


        }
        catch (Exception e)
        {
            Console.WriteLine("Unable to get Data from Sheet. Exception is : " + e);
        }
    }

my problem is when an empty row found in sheet workSheet.ShiftRows() method shifts up the empty row then lastRowNum(total rows in sheet) count decreases by 1. its working for first time, but when the next time empty row found lastRownum value is increasing but not decreasing.

JohnG · Answer 1 · 2017-01-17T20:25:41.520

I am not familiar NPOI, however looking at your code some things seem odd or problematic. First is the decrementing of i if the row is empty. This seems odd as i is your for loops counter variable and lastRowNum is the ending condition which you also change in the loop. So an example pass would be: let’s say the first empty row is row 3 (when i = 2). Loop through the first two rows then we get to empty row 3. So i = 2, and lastRowNum has not changed and let’s say the last row is 10. So we enter the if (value.Equals("")) because the row is empty. I am guessing you shift row i+1 up to i where the empty row 3 was. Then reset lastRowNum which should be 9. THEN DECREMENT i????. I am confident the reason you are doing this is because the row that was removed at row position 3 is now the row that was in position 4. So you need to recheck this row 3 again.

The for loop looks a little strange… decrementing i and re-setting a new lastRowNum every time an empty row is found? Both of these variables control the for loop and changing those variables in the loop can cause unexpected results.

Since I am not that familiar with NPOI, it is difficult to recommend a better way using NPOI, however I have seen this before and it appears an easy and simple solution may work. Since the ranges and row indexes change every time a row is deleted, you have to account for these changes when deleting from the top down. I recommend you try and delete the rows from the BOTTOM UP. If you start at the bottom and work up, you have no need to change the lastRowNum variable as it will simply be a starting point. Also, when you delete rows from the bottom up you do not have to re-shift the i index in your loop. I would try this and see if it makes things easier. Hope this helps.

Actually the same method in java with poi worked perfectly in the same scenario for me so i have used the same functionality in c#. — Srikanth E, Jan 18 '17 at 09:35
I will not argue it can work. If you prefer the more difficult path then by all means go that rout. — JohnG, Jan 18 '17 at 12:25
You may want to see how it is done using the technique I describe above, it does use `interop` but the idea is the same. [Remove Empty rows and Columns From Excel Files Faster using Interop](http://stackoverflow.com/questions/40574084/remove-empty-rows-and-columns-from-excel-files-faster-using-interop/40726309#40726309) — JohnG, Jan 18 '17 at 12:37

score 0 · Answer 2 · answered Jan 18 '17 at 09:40

The same issue i have solved in Java but not in C#. Here is my java code for removing empty row.

public static void removeRow(String SheetName) throws Exception {
    try {
    FileInputStream fis = new FileInputStream(sPathTestData);
    ExcelWsheet = ExcelWbook.getSheet(SheetName);
    int lastRowNum=ExcelWsheet.getLastRowNum();
    for(int i=0; i<lastRowNum;i++)
        {
        String value =ExcelUtils.getCellData(i, 1, "Sheet1");
         if(value.equals("")){
            ExcelWsheet.shiftRows(i+1,lastRowNum, -1);
            lastRowNum=ExcelWsheet.getLastRowNum();
            i--;
         }
        }
        fis.close();
        FileOutputStream fileOut = new FileOutputStream(sPathTestData);
        ExcelWbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
      } 
catch(FileNotFoundException fe){fe.printStactTrace();}  
    }

score 0 · Answer 3 · answered Dec 02 '20 at 03:08

Nothing new here...I just use these

 private void ExcelRemoveRows(ISheet sheet, int rowNoFrom, int rowNoTo)
        {
            int nRowToRemove = rowNoTo - rowNoFrom + 1;
            for (int k = 1; k <= nRowToRemove; k++)
            {
                sheet.RemoveRow(sheet.GetRow(rowNoFrom));
                int lastRow = sheet.LastRowNum;
                sheet.ShiftRows(rowNoFrom + 1, lastRow, -1);
            }
        }

How to remove empty rows and reduce total rows count in excel using NPOI with C#

3 Answers3