Edit existing Excel file C# npoi

Question

I want to with a console application C# open an existing excel file and add content to it. NPOI 2.1.1.0

My first approach was simply to add a value to last cell figure I solved that it will solve my other problem.
This will read the file correctly with the new content but it will not save it.

Output:

"Cannot access a closed file.".

HSSFWorkbook hssfwb;
using (FileStream file = new FileStream(@"c:\testfile.xls", FileMode.Open, FileAccess.ReadWrite))
{
    hssfwb = new HSSFWorkbook(file);
    ISheet sheet = hssfwb.GetSheetAt(0);
    IRow row = sheet.GetRow(0);

    sheet.CreateRow(row.LastCellNum);
    ICell cell = row.CreateCell(row.LastCellNum);
    cell.SetCellValue("test");

    for (int i = 0; i < row.LastCellNum; i++)
    {
        Console.WriteLine(row.GetCell(i));
    }
    hssfwb.Write(file);
    file.Close();
}

I did try add the following but it resulted in a blank file that wont open.

MemoryStream mstream = new MemoryStream();
hssfwb.Write(mstream);

byte[] bytes = new byte[mstream.Length];
mstream.Read(bytes, 0, (int)mstream.Length);
file.Write(bytes, 0, bytes.Length);
file.Close();
mstream.Close();

I also tried putting the hssfwb alone within the using scope, changed the filemode to Append/openorcreate/truncate, added if write check without any result.

However one solution would be to read the file, convert it into an datatable, create a new excel file and populate it with the data.

What am I doing wrong? Can anyone shed some light on this? I'v looked over the Npoi Example package in vain.

Answer 1

Well, it looks like HSSFWorkbook constructor closes filestream after reading from it.

Simpliest and straightforward solution - open file for reading, create HSSFWorkbook, do what you want in that workbook, and then open file again for writing and write to it.

It is safe to use hssfwb outside of using scope because HSSFWorkbook itself does not holds reference to file from which it was read (as long as I see it in NPOI sources).

So your code could look like:

HSSFWorkbook hssfwb;
using (FileStream file = new FileStream(@"c:\temp\testfile.xls", FileMode.Open, FileAccess.Read))
{
    hssfwb = new HSSFWorkbook(file);
    file.Close();
}

ISheet sheet = hssfwb.GetSheetAt(0);
IRow row = sheet.GetRow(0);

sheet.CreateRow(row.LastCellNum);
ICell cell = row.CreateCell(row.LastCellNum);
cell.SetCellValue("test");

for (int i = 0; i < row.LastCellNum; i++)
{
    Console.WriteLine(row.GetCell(i));
}

using (FileStream file = new FileStream(@"c:\temp\testfile.xls", FileMode.Open, FileAccess.Write))
{
    hssfwb.Write(file);
    file.Close();
}