NPOI ArgumentOutOfRangeException on WorkbookFactory.Create

Question

I have been using NPOI to read Excel files, and I now need to write out files. I am trying to use the WorkbookFactory, which doesn't show up in a lot of examples online (doesn't appear in the NPOI examples on CodePlex either). Here is the code:

this.FileStream = new FileStream(
    this.FilePath,
    FileMode.OpenOrCreate,
    FileAccess.ReadWrite);
this.Workbook = WorkbookFactory.Create(
    this.FileStream);

When it gets to the second statement, I get an ArgumentOutOfRangeException with the following message: "Non-negative number required.\r\nParameter name: value".

Next few lines in the call stack:

at System.IO.FileStream.set_Position(Int64 value)
at NPOI.Util.PushbackStream.set_Position(Int64 value)
at NPOI.POIXMLDocument.HasOOXMLHeader(Stream inp)
at NPOI.SS.UserModel.WorkbookFactory.Create(Stream inputStream)

Oh sorry I didn't read your post very carefully, the stack and the message clearly points to the Position property being set to a negative number, not that the mode was the out of range. — Quantic, Jul 05 '16 at 23:18
Well my eyes are starting to cross from looking at the source code, but I'm pretty sure you need some shell of an `.xls` file to exist for the code to work, and I assume that you are creating the file (because of `FileMode.OpenOrCreate`), so you are getting an exception because the [source code](https://github.com/tonyqus/npoi/blob/master/ooxml/POIXMLDocument.cs) "reads" 4 bytes then rewinds the stream 4 bytes, but for you it is rewinding from 0 to -4 which throws the exception. — Quantic, Jul 05 '16 at 23:45

score 2 · Accepted Answer · answered Jul 06 '16 at 06:31

The WorkbookFactory (link to POI documentation) reads existing file data from an input stream, and determines on the fly whether to create an HSSFWorkbook or an XSSFWorkbook (i.e. whether you are working with XLS or XLSX-like files).

From your code, it seems you are trying to create a new file using this class. That is not something the WorkbookFactory can help you with. To write files, use the following pattern:

var workbook = new XSSFWorkbook();
...
using (var fileData = new FileStream(@"path\filename.xlsx", FileMode.Create))
{
  workbook.Write(fileData);
}

(In other words, WorkbookFactory is a class factory, not a file factory :-))

NPOI ArgumentOutOfRangeException on WorkbookFactory.Create

1 Answers1