| Summary: | XSSFSheet.addIgnoredErrors(CellReference, IgnoredErrorType) creates corrupt Excel file | ||
|---|---|---|---|
| Product: | POI | Reporter: | p.beauvoir |
| Component: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | regression | ||
| Priority: | P2 | ||
| Version: | unspecified | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
| Bug Depends on: | 62828 | ||
| Bug Blocks: | |||
| Attachments: | Test snippet that demonstrates the bug | ||
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.
|
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.