Bug 46136 - Add an ignore warning for cells with Numbers as Text
Summary: Add an ignore warning for cells with Numbers as Text
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Windows XP
: P2 enhancement with 15 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 56892
Blocks:
  Show dependency tree
 
Reported: 2008-10-31 08:00 UTC by Joe Knudsen
Modified: 2016-10-29 08:33 UTC (History)
2 users (show)


Attachments
A number is stored as text and has the warning (13.50 KB, application/octet-stream)
2008-10-31 08:02 UTC, Joe Knudsen 		Details
Cell where a number is stored as text and the waring is ignored (13.50 KB, application/octet-stream)
2008-10-31 08:02 UTC, Joe Knudsen 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Joe Knudsen 2008-10-31 08:00:40 UTC 
To avoid the warning given by Excel 2002 and greater when a number is stored in a Text cell.  When stored as numbers Excel makes change like removing leading zeros.  From the Excel GUI you can ignore the warning on a per cell basis.  I will attach two files one with the warning (green corner in the cell) and one where that one cell has been set to ignore.  Here is the Biff output of the two files after a diff on the output.

$ diff biffNormal.txt biffIgnoreError.txt 
1690a1691,1702
> recordid = 0x867, size = 19
> [UNKNOWN RECORD:867]
>     .id        = 867
> [/UNKNOWN RECORD]
> 
> Offset 0x6f3 (1779)
> recordid = 0x868, size = 39
> [UNKNOWN RECORD:868]
>     .id        = 868
> [/UNKNOWN RECORD]
> 
> Offset 0x71e (1822)
Comment 1 Joe Knudsen 2008-10-31 08:02:19 UTC 
Created attachment 22814 [details]
A number is stored as text and has the warning
Comment 2 Joe Knudsen 2008-10-31 08:02:56 UTC 
Created attachment 22815 [details]
Cell where a number is stored as text and the waring is ignored
Comment 3 Nick Burch 2009-11-06 16:36:27 UTC 
0x867 = 2151 = FeatHdr = Page 278 of the binary documentation

0x868 = 2152 = Feat = Page 277 of the binary documentation

First we'll need to implement the records, then we can look at hooking them into the usermodel
Comment 4 Nick Burch 2010-01-03 13:55:14 UTC 
Most of the record support should now be in SVN

However, I'm not sure of the best way to expose it to the usermodel.

As best as I can tell, each sheet can have a number of FeatHdr+Feat record pairs. Within each FeatRecord is an array of cell ranges that it applies to, then a particular shared feature.

For example, it seems you can have:
* range = a1 -> b1, type = formula ignore, set = number as text, empty cell ref
* range = c1 -> c4 + c7 -> c8, type = formulas ignore, set = number as text
* range = a1 -> b1, type = protection, title + type + password hash

Any suggestions on where we've got something vaguely similar in the usermodel to copy the structure from greatly appreciated!
Comment 5 David North 2015-06-12 15:40:41 UTC 
See also bug #56892
Comment 6 Nick Burch 2016-02-15 15:03:14 UTC 
XSSF now has a suitable set of Usermodel methods, and there's a common enum of the error types + support in FeatFormulaErr2

This now needs someone to implement those same methods (+add them to the interface), by doing the necessary record finding/adding/etc