NPOI 2.1.3.1 produces corrupted XLSX files

Question

I'm trying to use NPOI 2.1.3.1 to create an Excel file from some database data. The file being created, though, is corrupted. Excel refuses to open it, and changing it to a ZIP and trying to extract it also causes errors in Windows and in WinRAR. Other threads I've read say to upgrade to 2.1.3.1 because it fixes something about XLSX creation, but that's not true at all. I've also read it has something to do with how it ZIPs the file, but there doesn't seem to be any ability to control that. I'm hoping someone can point me in the right direction with my code below:

public byte[] GetExcelFile(
    QueryModel query) {
    var orders = Mapper.Map<IList<ExcelListModel>>(Context.Orders.Where(
        o =>
            o.DateOrdered >= query.Start
            && o.DateOrdered <= query.End));
    var workbook = new XSSFWorkbook();
    var sheet = workbook.CreateSheet();

    for (int i0 = 0, l0 = orders.Count, r = 0; i0 < l0; i0++) {
        var order = orders[i0];

        for (int i1 = 0, l1 = order.Products.Count; i1 < l1; i1++, r++) {
            var row = sheet.CreateRow(r);
            var product = order.Products[i1];

            row.CreateCell(0).SetCellValue("");
            //  ...
            row.CreateCell(18).SetCellValue("");
        }

        for (int i1 = 0, l1 = order.Bonuses.Count; i1 < l1; i1++, r++) {
            var row = sheet.CreateRow(r);
            var bonus = order.Bonuses[i1];

            row.CreateCell(0).SetCellValue("");
            //  ...
            row.CreateCell(18).SetCellValue("");
        }
    }

    var memoryStream = new MemoryStream();

    workbook.Write(memoryStream);

    return memoryStream.ToArray();
}

score 2 · Accepted Answer · edited May 23 '17 at 10:28

After several hours, I don't think there was an actual issue with NPOI. I had switched over to EPPlus, and it was outputting a correct temp file, but the file that was being sent back to the browser was corrupted still. Ultimately I've concluded that it's an issue with ASP.NET MVC's FileResult implementation because when I overwrote the Response object the correct file was returned to the browser. For more information, look at this post:

ASP.NET MVC FileResult is corrupting files

Unfortunately, I already ripped out the NPOI code when I switched to EPPlus so I can't confirm with 100% accuracy that it wasn't truly bugged. That being said, based on the behavior I was getting with EPPlus as well, I don't think there was an issue with NPOI.

NPOI 2.1.3.1 produces corrupted XLSX files

1 Answers1