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);
}

Answer 2

Best way for me to do it was simply not deleting the old line, and just replacing the old line.

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

And if you need to delete additional lines under the top lines, keep record to avoid overlapping data.

var removed = 0; // removed cels
if (indexToBeDeleted == condition)
    {
    var rowD = sheet.GetRow(indexToBeDeleted - removed);
    if (rowD != null)
        {
            sheet.ShiftRows(rowD.RowNum + 1, sheet.LastRowNum, -1);
            removed++;
        }
    }

Answer 3

RemoveRow method seems not working well. See filed bugs here.

You expect the row is removed and the rest rows below will "move up". Actually that is the MS Excel do for you. As to handle a data structure, NPOI doesn't move up the rest rows up, it just removes all cells in that row, even the removed row itself is not null after "remove", it just contains no cell.

Try this:

while (rowIndex <= sheet.LastRowNum)
    {
        var row = sheet.GetRow(rowIndex);
        if (row != null) sheet.RemoveRow(row);
        rowIndex++;
    }