Bug 58641 - Support for IgnoredErrors in XSSF
Summary: Support for IgnoredErrors in XSSF
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.14-dev
Hardware: PC All
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
: 54868 (view as bug list)
Depends on: 56892
  Show dependency tree
Reported: 2015-11-23 21:46 UTC by peterhansson_se
Modified: 2016-10-29 08:37 UTC (History)
1 user (show)

Proposal for usermodel implementation (2.01 KB, application/x-zip-compressed)
2015-11-30 08:36 UTC, peterhansson_se

Note You need to log in before you can comment on or make changes to this bug.
Description peterhansson_se 2015-11-23 21:46:24 UTC
Apache POI (XSSF) currently lacks support for IgnoredErrors feature when writing XLSX files.

More information on this official MS link:


As can be seen this is an element on the Sheet.

On this feature I suggest to leave XLS format in the dust and *not* implement it on the usermodel. Most people do not need XLS support anymore and I guess it is fair to say to users of Apache POI that if they need this feature then they need to use XSSF. It seems previous attempts to implement this in usermodel have failed (see bug 54868). Instead I suggest simply to add it as a setter method on the XSSFSheet.

There are other tickets (bug 54868 and bug 46136) that relate to the same issue but this RFE is more general, it is not just about specifically ignoring "number-stored-as-text" warning, but indeed all those warnings supported by IgnoredErrors element.

Personally I have no interest in being able to *read* this information from a XLSX file. Being able to persist the setting is far, far more important.
Comment 1 peterhansson_se 2015-11-23 21:50:55 UTC
*** Bug 54868 has been marked as a duplicate of this bug. ***
Comment 2 Nick Burch 2015-11-23 21:51:23 UTC
There's low level record support for this for HSSF, see #46136

If we can't read it back, then it's rather tricky to unit test it if nothing else!

Bug #56892 has one proposed usermodel implementation for this, but it really needs a bit more work and to be made more generic. Being able to roughly mirror behaviour for adding/setting and reading would be a bonus too

Any thoughts on the right usermodel interface for adding this?
Comment 3 peterhansson_se 2015-11-29 12:22:54 UTC
It is kinda similar in concept to DataValidations. I think the usermodel for that can be re-used as inspiration.

For information: In OOXML there are quite a few types of IgnoredErrors:

   - evalError
   - twoDigitTextYear
   - numberStoredAsText
   - formula
   - formulaRange
   - unlockedFormula
   - emptyCellReference
   - listDataValidation
   - calculatedColumn

This is just to say that 'numberStoredAsText' is just one of many. 

I notice that the XSSF implementation for DataValidation has some utility code for dealing with 'sqref'. This type of code should be moved out of DataValidation and into a utils class or something as the same needs to be used for IgnoredErrors.
Comment 4 peterhansson_se 2015-11-30 08:36:30 UTC
Created attachment 33311 [details]
Proposal for usermodel implementation
Comment 5 peterhansson_se 2015-11-30 08:56:34 UTC
I've attached a proposal for a complete usermodel implementation. 
The proposal is inspired by DataValidation.

The proposal adds an interface named IgnoredError and corresponding new methods in Sheet:




List<? extends IgnoredError> getIgnoredErrors();

I've kept it simple. It is perfectly possible to have overlap in ignore error.

Suppose I add

  For types twoDigitTextYear,numberStoredAsText ignore in cells A1:A5.

and then in addition I add

  For type numberStoredAsText ignore in cell A1.

Now this is obviously redundant wrt to A1: I've told twice that I want 'numberStoredAsText' to be ignored for that cell. I've decided that this is perfectly acceptable for the simple reason that it is also acceptable in OOXML. This means implementers of the above shouldn't try to be clever about it and try to collapse/reduce what has been requested *unless this is specifically required by the format*.

   - formula
   - formulaRange
   - unlockedFormula
   - emptyCellReference
Comment 6 Dominik Stadler 2016-03-16 09:35:15 UTC
There has been some work on this via Bug 56892, can you take a look and see how much of your proposed changes are still missing?
Comment 7 Dominik Stadler 2016-10-29 08:37:17 UTC
there was no update here in quite some time, therefore I am setting this to WONTFIX for now. 

We have support for ignoring errors for regions for XSSF via bug 56892, support for HSSF is discussed in bug 46136. 

If you would like to propose more changes then please reopen this bug with an updated patch that applies to the current trunk.