0

I'm using NPOI to clear data of all rows from a given row index all the way to the last row using the following function:

public void DeleteToLastRow(ISheet sheet, int firstRowIndex)
{
    int lastRowIndex = sheet.LastRowNum;
    for (int rowIndex = lastRowIndex; rowIndex >= firstRowIndex; rowIndex--)
    {
        var deletedRow = sheet.GetRow(rowIndex);
        // All rows are warrantied to be not null
        sheet.RemoveRow(deletedRow);
    }
}

However, I have around 100 cells in each row, and around 80 cells out of those 100 have some complex formula. Because of that, it takes me about 8 seconds to delete 200 rows. Is there any way to increase performance in this case?

Most cells reference to 3 or 4 other cells at the start of the same row. They also reference to 1 fixed cell at the start of the sheet that I do not delete.

I'm using .Net 4.6.2, NPOI 2.3.0, my file is xlsx so I'm using XSSF.

duongntbk
  • 620
  • 4
  • 25
  • Are there many other cells referencing the rows being deleted? Also, is this an XSSF or HSSF? – justiceorjustus Jul 31 '17 at 14:34
  • @justiceorjustus Most cells reference to 3 or 4 other cells at the start of the same row. They also reference to 1 fixed cell at the start of the sheet that I do not delete. I'm using XSSF, my file is xlsx. – duongntbk Aug 01 '17 at 01:16
  • I have noticed that if there are cells referencing the rows being deleted, that will slow it down. Also, XSSF is notoriously slower for RemoveRow(). Try .xls and maybe turn the formulas into strings or remove them first... Not sure if the formulas referencing their own row make a difference, but you can try. Sorry I couldn't help much else. – justiceorjustus Aug 01 '17 at 01:22

0 Answers0