Trying to edit cell value of existing Excel file using NPOI

Question

I have written the following code to edit an Excel file using C# and NPOI library. There are no errors, but after running the code if I open the file, the value of the cell is not edited. What am I doing wrong?

namespace Project37
{
    class Class1
    {
        public static void Main()
        {
            string pathSource = @"C:\Users\mvmurthy\Downloads\VOExportTemplate.xlsx";

            FileStream fs = new FileStream(pathSource, FileMode.Open, FileAccess.ReadWrite); 
            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
            HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("ImportTemplate");
            HSSFRow dataRow = (HSSFRow)sheet.GetRow(4);

            dataRow.GetCell(1).SetCellValue("foo");

            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);
        }     
    }
}

Answer 1

The main reason that you are not seeing the changes is because you are writing the workbook to a MemoryStream instead of writing back to the file. What you should be doing is this:

1. using a FileStream in Read mode to read the spreadsheet file completely;
2. making the changes you want, then
3. using a FileStream in Write mode to write back to the file (or optionally write to a different file if you don't want to destroy the original -- this may be better for testing).

Also note it is good practice to use using statements when working with classes that implement IDisposable (all Streams do). This will ensure that the file is closed and all resources used by the stream are cleaned up properly.

There is another problem I see in your code, and that is you are apparently trying to use an HSSFWorkbook with an .xlsx file. That won't work. HSSFWorkbook (and all HSSF classes) are for .xls files. If you need to work with .xlsx files, then you should be using XSSFWorkbook and related XSSF classes instead. Note that both flavors of classes implement common interfaces like IWorkbook, ISheet, IRow, etc. to help reduce code duplication should you need to support both types of files. I recommend using them where possible. But you may find you still need to downcast occasionally to access certain features that are not covered by the interfaces.

One other thing I should mention: if a particular row x contains no cells in the original workbook, then GetRow(x) will return null. Similarly, GetCell(y) will return null if cell y is empty. If you want to be able to set the value of the cell regardless, you will need to check for nulls and use CreateRow(x) and/or CreateCell(y) as appropriate to ensure each respective entity exists.

Here is the revised code:

string pathSource = @"C:\Users\mvmurthy\Downloads\VOExportTemplate.xlsx";

IWorkbook templateWorkbook;
using (FileStream fs = new FileStream(pathSource, FileMode.Open, FileAccess.Read))
{
    templateWorkbook = new XSSFWorkbook(fs);
}

string sheetName = "ImportTemplate";
ISheet sheet = templateWorkbook.GetSheet(sheetName) ?? templateWorkbook.CreateSheet(sheetName);
IRow dataRow = sheet.GetRow(4) ?? sheet.CreateRow(4);
ICell cell = dataRow.GetCell(1) ?? dataRow.CreateCell(1);
cell.SetCellValue("foo");

using (FileStream fs = new FileStream(pathSource, FileMode.Create, FileAccess.Write))
{
    templateWorkbook.Write(fs);
}