NPOI writing to XLS but not XLSX

Question

Having a difficult time with this one...

Everything worked 100% with XLS (I'm using NPOI on my ASP.NET app):

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

private MemoryStream ms = new MemoryStream();
private IWorkbook workbook;
private ISheet worksheet;
private byte[] buffer;

using (FileStream file = new FileStream(@"C:\template.xls", FileMode.Open, FileAccess.Read))
{
    workbook = new HSSFWorkbook(file);
}

worksheet = workbook.GetSheetAt(0);
worksheet.GetRow(11).GetCell(11).SetCellValue("hello"); // etc etc etc

workbook.Write(ms);
buffer = ms.ToArray();

System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();
response.AddHeader("Content-Type", "application/vnd.ms-excel");
response.AddHeader("Content-Disposition", String.Format("attachment; filename=template.xls; size={0}", buffer.Length.ToString()));
response.BinaryWrite(buffer);
response.End();

Requirements have changed and the new Excel template uses features from XLSX, so it's now template.xlsx. I've been using NPOI 2.0 all along, and I've seen that it supports XLSX (using XSSF instead of HSSF). I changed the code like this (only showing the differences here - everything else is the same):

using NPOI.XSSF.UserModel;

using (FileStream file = new FileStream(@"C:\template.xlsx", FileMode.Open, FileAccess.Read))
{
    workbook = new XSSFWorkbook(file);
}

response.AddHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.AddHeader("Content-Disposition", String.Format("attachment; filename=template.xlsx; size={0}", buffer.Length.ToString()));

The exception gets thrown at the line workbook.Write(ms);:

System.NotImplementedException: The method or operation is not implemented.
   at NPOI.XSSF.UserModel.XSSFChartSheet.Write(Stream out1)
   at NPOI.XSSF.UserModel.XSSFSheet.Commit()
   at NPOI.POIXMLDocumentPart.OnSave(List`1 alreadySaved)
   at NPOI.POIXMLDocumentPart.OnSave(List`1 alreadySaved)
   at NPOI.POIXMLDocument.Write(Stream stream)

What could I be doing incorrectly?

edit: I've tried NPOI 2.1.1 Beta - same issue

Answer 1

I ran some tests with your sample code and a bunch of different XLSX files. Based on the results, and the exception you got, the issue is not with your code, but with the NPOI library and the XLSX template file you are working with.

Basically, in Excel, you can display a chart in two ways:

1. Embedded in a worksheet, such that the worksheet can display other information.
2. All by itself in a separate sheet. This is known as a Chart Sheet.

It seems that your XLSX template contains a chart sheet, and the error occurs while trying to write out the chart sheet (XSSFChartSheet.Write). Currently, NPOI has no support for writing out XLSX chart sheets, it just throws an exception instead.

This leaves you with three options:

1. Remove all chart sheets from your XLSX file.
2. Convert all chart sheets in your template to embedded charts. Note that in my testing, NPOI support for embedded charts was a bit iffy. While it did not throw an exception while writing them out, the resultant charts would sometimes have data labels added where there were none, or cause Excel to recover the document by removing the chart itself. Your mileage may vary.
3. Drop NPOI and use some other library, for e.g., EPPlus which seems to have support for charts in XLSX files, based on this answer.