How to save an XLS file several times (once after each modification) using NPOI

Question

I'm new to NPOI, and I have a question for which I could not find a "simple" answer / solution. (I don't want to close and open a file 5 times.)

I need to edit an XLS file, (let's say I want to modify 5 cells from value 0 to 1), but I must save the file after each modification (meaning 5 times).

I noticed that the first change is saved, but I cannot see the rest of the modifications when I open the xls file. I do notice that the size of the file is something like 5 * (size of file on first change).

My code is something like:

public void SetCell(int row, int col, CellData xlsCell)
{
    ISheet sheet = mXLSWorkBooK.GetSheet(("mySheet1"));
    sheet.SetActive(true);

    ICell cell;
    cell = sheet.CreateRow(row).CreateCell(col);
    cell.SetCellType(CellType.String);
    cell.SetCellValue(xlsCell.Text);

    mXLSWorkBooK.Write(mfile);
    mfile.Flush();
} 

and I call this method 5 times.

Answer 1

You may be misunderstanding how IWorkbook.Write(stream) works. This method does not write out incremental changes; it always writes out the entire workbook. If you are reusing an existing stream each time (which is what it looks like you are doing) then you will be appending the entire workbook contents to the end of the stream, creating a corrupted file. (This also explains why the file grows in size each time you write it.) This is not the intended usage pattern for NPOI. Instead you should use a new stream each time and write to that stream, overwriting the file. I would recommend making a helper method like this:

public static void SaveWorkbook(IWorkbook wb, string fileName)
{
    using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
    {
        wb.Write(fs);
    }
}

Then call that helper method whenever you need to save the file.

Answer 2

thanks for replying. I found my mistake!

when I used cell = sheet.CreateRow(row).CreateCell(col), for the next cell in the current row, I actually erased the row and created it from scratch with the new cell only, so the final state is a single cell in each row.