Bug 58641

Summary:	Support for IgnoredErrors in XSSF
Product:	POI	Reporter:	peterhansson_se
Component:	XSSF	Assignee:	POI Developers List <dev>
Status:	RESOLVED WONTFIX
Severity:	enhancement	CC:	emercoyle
Priority:	P2
Version:	3.14-dev
Target Milestone:	---
Hardware:	PC
OS:	All
Bug Depends on:	56892
Bug Blocks:
Attachments:	Proposal for usermodel implementation

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:

https://msdn.microsoft.com/en-us/library/dd944168%28v=office.12%29.aspx

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:


setters:

addIgnoredError(IgnoredError)
removeIgnoredError(IgnoredError)


getters:

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.