This bug is in POI 4.1.0 It works OK on POI 4.0.1 I am using XSSFSheet addIgnoredErrors(CellReference cell, IgnoredErrorType... ignoredErrorTypes) like this: XSSFSheet sheet; Cell cell; sheet.addIgnoredErrors(new CellReference(cell), IgnoredErrorType.NUMBER_STORED_AS_TEXT); When the XSSFWorkbook is saved to "file.xlsx" Excel reports an error when opening it: "Excel found unreadable content in file.xlsx" Inside the Excel file at sheet1.xml I see this: <ignoredErrors> <ignoredError sqref="SheetName!A1" numberStoredAsText="true" /> </ignoredErrors> In POI 4.0.1 the same thing is written as: <ignoredErrors> <ignoredError sqref="A1" numberStoredAsText="true" /> </ignoredErrors> So it seems that 4.1.0 is prefixing the Sheet name to to sqref and Excel barfs.
In 4.0.1 the CellReference constructor is this: public CellReference(Cell cell) { this(cell.getRowIndex(), cell.getColumnIndex(), false, false); } This means that _sheetName is null. And so the Sheet name is not prefixed to the cell. In 4.1.0 the CellReference constructor is this: public CellReference(Cell cell) { this(cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex(), false, false); } This means that _sheetName is not null. And so the Sheet name is prefixed to the cell leading to the problem.
Workaround: CellReference has this constructor which ensures that _sheetName is null: public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) { this(null, pRow, pCol, pAbsRow, pAbsCol); } So call that constructor like this: XSSFSheet sheet; Cell cell; sheet.addIgnoredErrors(new CellReference(cell.getRowIndex(), cell.getColumnIndex(), false, false), IgnoredErrorType.NUMBER_STORED_AS_TEXT);
Created attachment 36629 [details] Test snippet that demonstrates the bug
This is likely caused by r1850210 which fixed bug #62828, seems Excel is inconsistent in which style of cell-reference is supported where, so we might need to use special formatting when writing this part of the XLSX file.
Applied a fix via r1861817 which does not include the sheetName in references when setting ignored errors for cells.
> Fixed Nice. Thanks.