C# NPOI Excel tool does not remove the row?

Question

I'm working on an application that is supposed to read a line from the end of the file, do something with it, and then delete that row and update the file.

I'm making use of the NPOI library, but it's not working. In order to avoid bad data (null cells) from coming in, I run the following check:

IWorkbook WB;
ISheet WS;
ICell cell;
using ( FileStream FS = new FileStream( p, FileMode.Open, FileAccess.Read ) ) {
    if ( Path.GetExtension( p ).ToLower( ).Equals( "xls" ) )
        WB = new HSSFWorkbook( FS );
    else
        WB = new XSSFWorkbook( FS );
}
WS = WB.GetSheetAt( 0 );
cell = WS.GetRow( WS.LastRowNum ).GetCell( 0 );
if ( cell == null ) {
    IRow row = WS.GetRow( WS.LastRowNum );
    WS.RemoveRow( row );
        using ( FileStream FS = new FileStream( p, FileMode.Create, FileAccess.Write ) )
    WB.Write( FS );
    DoFunc(args);
}

However; this is getting stuck in an infinite loop. I checked the code, and the LastRowNum property is not getting any smaller. Why is this not actually removing the last row from the worksheet?

Is there a better way to accomplish this?

Answer 1

You can use ShiftRows() method from ISheet. If you move all your rows up, beginning from the next row after the deleted row, it will do the trick. You can also see the discussion here: https://npoi.codeplex.com/workitem/8411

var row = sheet.GetRow(indexToBeDeleted);
if (row != null) 
{
    sheet.RemoveRow(row);
    sheet.ShiftRows(indexToBeDeleted + 1, sheet.LastRowNum, -1);
}