2
Vote

NPOI.HSSF AutoFilter Issue: "File error: data may have been lost"

description

Getting "File error: data may have been lost" when opening saved Excel after setting auto-filter.
Example files attached.


Attempt to add data filter over all columns of Excel file using HSSFWorkbook
  • NPOI Version 2.2.1
  • XLS workbook generated by Crystal Reports export in an ASP.net webforms application
  • Excel version: Excel 2016
Crystal Reports XLS Export - unmodified:
Code:
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=\"{0}.xls\"", reportTitle));

using (Stream reportStream = _reportDocument.ExportToStream(ExportFormatType.Excel))
{
    reportStream.CopyTo(Response.OutputStream);
}

Response.Flush();
Response.Close();
Response.End();

Generated file "SimpleReport - unmodified.xls"
which opens in Excel without warning/error.


Crystal Reports XLS Export - auto-filter added with HSSFWorkbook:
Code:
HSSFWorkbook workbook;
using (Stream reportStream = _reportDocument.ExportToStream(ExportFormatType.Excel))
{
    workbook = new HSSFWorkbook(reportStream);
}

workbook.Workbook.Records.Add(new CountryRecord() { DefaultCountry = 1, CurrentCountry = 1 });  // need this else NPOI throws an exception

ISheet sheet = workbook.GetSheetAt(0);
CellRangeAddress cellRange = new CellRangeAddress(sheet.FirstRowNum, sheet.LastRowNum, sheet.GetRow(sheet.FirstRowNum).FirstCellNum, sheet.GetRow(sheet.LastRowNum).LastCellNum - 1);
sheet.SetAutoFilter(cellRange);

Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=\"{0}.xls\"", reportTitle));

workbook.Write(Response.OutputStream);

Response.Flush();
Response.Close();
Response.End();

Generated file "SimpleReport - filter set with HSSFWorkbook.xls".
When opening in Excel, a dialog is shown stating: "File error: data may have been lost".
The spreadsheet appears to be ok though.

file attachments

comments

Zebrette wrote Dec 9, 2016 at 10:27 PM

same issue for me, already reported under Id #13957 (and #14012?). I suggest merging the bugs