4

I have a piece of code that is currently writes to a .xls workbook (HSSFWorkbook) with no issue. However when I try to use the same code to write to a .xlsx workbook (XSSFWorkbook) the archive becomes corrupted and cannot be opened in excel.

The following code is what I am using to access the workbook, edit the workbook, and then save back to the workbook. I originally assumed that the code that I was using to edit the workbook was the issue, but after commenting it out the issue still persists.

IWorkbook workbook;
using (var file = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
    if (Path.GetExtension(fileName).Contains("xlsx"))
    {
        workbook = new XSSFWorkbook(file);
    }
    else
    {
        workbook = new HSSFWorkbook(file);
    }
}

//Code that edits workbook which is currently commented out

using (var file = new FileStream(path, FileMode.Open, FileAccess.ReadWrite))
{
    workbook.Write(file);
}

I have tried running this code against a blank .xlsx workbook and the file becomes corrupt, and is no longer able to be opened.

I am using the latest stable version of NPOI from nuget: NPOI 2.1.3.1

James C. Taylor IV
  • 599
  • 10
  • 24

1 Answers1

7

After trying everything that was mentioned on NPOI's Codeplex to no avail, I tried messing with the FileStream properties and was able to get the blank .xslx to save. I am using the following code to write back to the file:

using (var file = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
    workbook.Write(file);
}

This has fixed the primary issue of saving a back to a XssfWorkbook.

James C. Taylor IV
  • 599
  • 10
  • 24
  • 4
    For future readers: note that saving an empty workbook (right after `new XSSFWorkbook();` also corrupts the `.xlsx` file. To avoid this, create an empty sheet in the workbook (`workbook.CreateSheet()`) before saving the workbook. – bklaric Aug 18 '16 at 11:59
  • 1
    Good find, I was also WTF because `FileMode.Open` is working with `.xls` files but not `.xlsx` files. – Sacha K Nov 10 '16 at 14:14
  • I was getting corrupt files because I wasn't using the Filestream object in a Using block (instead I was calling Close() on it). I'm guessing it didn't flush the file properly. – Daz Mar 26 '19 at 16:33