Writing a file using NPOI

Question

I'm unable to use NPOI to save changes to an xlsx to disk, when attempting to do it from a DLL.

I have a set of data that I'm currently storing in a database, and a home-grown ORM for it. The home-grown ORM is housed in its own DLL. I want to write a utility which uses the ORM to take a subset of the data to read/write it. I'm using NPOI (v2.1.3.0) to do it.

The utility calls look like:

    private void Test_Click(object sender, RoutedEventArgs e)
    {
        var model = new ExcelDal(this.filename);
        model.Clients.Save(new Client { 
              DateOfBirth = DateTime.Now, DisplayName = "Test", Male = true
        });
    }

And I would expect that I would get an xlsx with a sheet named "Client" and a text column for "DateOfBirth", "DisplayName", and "Male". A file is indeed created, but attempting to open it fails. On the other hand, if I replace that code with this, I get exactly what's expected:

    private void Test_Click(object sender, RoutedEventArgs e)
    {
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Client");
        MainWindow.Create(sheet, 0, "DateOfBirth", "DisplayName", "Male");
        MainWindow.Create(sheet, 1, "1900/1/1", "Test", "true");

        FileMode mode = File.Exists(this.filename) ? FileMode.Truncate : FileMode.Create;

        using (FileStream fs = new FileStream(this.filename, mode, FileAccess.ReadWrite))
        {
            workbook.Write(fs);
        }
    }

    private static void Create(ISheet sheet, int rowNum, params string[] values)
    {
        IRow row = sheet.CreateRow(rowNum);
        for (int i = 0; i < values.Length; i++)
        {
            ICell cell = row.CreateCell(i);
            cell.SetCellValue(values[i]);
        }
    }

Troubleshooting steps attempted thus far:

Renamed the created file to a zip and extracted it, it is a valid zip.
Zipped up the file and renamed it to xlsx, I get a slightly different error, but it still fails to open.
Everything is .Net 4.5
Another SO question mentioned using ICreationHelper in the answer to convert string values, so I tried that also, but it didn't change anything.
A few notes on the ORM, since including all the relevant code might be overkill. I'm using pretty much the same code as the piece above which works; I built the test code to try to figure out what I was doing wrong, but it baffingly just worked.

This is what the code to set a cell value looks like (note that values have already been toString()'d by the time they get to actually be saved):

    public void SetValue(IRow row, string column, string value)
    {
        int columnIndex = this.GetColumnIndex(column);
        ICell cell = ColumnMapping.GetOrCreateCell(row, columnIndex);
        cell.SetCellValue(value);
    }

    private static ICell GetOrCreateCell(IRow row, int columnIndex)
    {
        return row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);
    }

This is what the code to save the file looks like:

    public void Save()
    {
        FileMode mode = File.Exists(this.filename) ? FileMode.Truncate : FileMode.Create;

        using (FileStream fs = new FileStream(this.filename, mode, FileAccess.ReadWrite))
        {
            this.workbook.Write(fs);
        }
    }

I cannot detect any differences. The only thing that it might be is that one is using NPOI indirectly, through the aforementioned ORM and one is using it directly.

score 0 · Accepted Answer · answered Mar 27 '15 at 17:13

I was unable to figure out a way of doing it reliably with NPOI. The files created by the utility always came out corrupted. I switched over to EPPlus for xlsx. The resulting code looks like this:

public void SetValue(int row, string column, string value)
{
    row += 2;   //EPPlus is 1-index based, and the first row is the heading
    int columnIndex = this.GetColumnIndex(column);
    this.excelSheet.SetValue(row, columnIndex, value);
}

The corresponding code to save the file looks like this:

public void Dispose()
{
    //ExcelPackage is gone once it is written out.  It must be re-created.
    this.excelPackage.SaveAs(new FileInfo(this.filename));
    this.excelPackage.Dispose();
}

So, the implementation is to add a Flush() which will dispose of the current ExcelPackage, then all of the ExcelSheet currently in memory, then re-initialize all from the file written out.

Writing a file using NPOI

1 Answers1