2
Vote

NPOI.XSSF - SetAutoFilter results in missing/unreadable xml in XLSX

description

Getting warning: "We found a problem with some content in 'SimpleReport '. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." when opening saved Excel after setting auto-filter.
Example files attached.

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

using (Stream exportStream = _reportDocument.ExportToStream(ExportFormatType.ExcelWorkbook))
{
    exportStream.CopyTo(Response.OutputStream);
}

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

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


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

if (!string.IsNullOrWhiteSpace(filterCellRange))
{
    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.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=\"{0}.xlsx\"", reportTitle));

workbook.Write(Response.OutputStream);

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

Generated file "SimpleReport - filter added with XSSFWorkbook.xlsx".


When opening the .xlsx file, Excel shows a dialog stating "We found a problem with some content in 'SimpleReport '. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

If you click "Yes", you get a dialog stating that "Excel was able to open the file by repairing or removing the unreadable content." and detail as follows: "Removed Part: /xl/styles.xml part with XML error. (Styles) Load error. Line 1, column 0.
Repaired Records: Cell information from /xl/worksheets/Sheet1.xml part".

It also points you to a log file with the contents:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error223040_01.xml</logFileName><summary>Errors were detected in file 'D:\work\AgeUK\AREP-66 - AgeUK MI (Management Information) Suite\TEST\SimpleReport Export\XLSX Export\SimpleReport - filter added with XSSFWorkbook - Copy.xlsx'</summary><removedParts><removedPart>Removed Part: /xl/styles.xml part with XML error.  (Styles) Load error. Line 1, column 0.</removedPart></removedParts><repairedRecords><repairedRecord>Repaired Records: Cell information from /xl/worksheets/Sheet1.xml part</repairedRecord></repairedRecords></recoveryLog>

file attachments

comments

Zebrette wrote Dec 9, 2016 at 10:25 PM

same issue for me, already reported under Id #13957. I suggest merging the bugs

wrote Dec 9, 2016 at 10:25 PM