You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2013/04/19 16:58:30 UTC
[Bug 54868] New: Write numbers to text cells in Excel generates
warnings that number is stored as text
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
Bug ID: 54868
Summary: Write numbers to text cells in Excel generates
warnings that number is stored as text
Product: POI
Version: 3.9
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: emercoyle@hotmail.com
Classification: Unclassified
If I try to write strings containing numbers to cells using the
Cell.setCellValue(String) method, a warning is displayed on the cell saying
that a number is stored as text. If I manually mark this cell as having
type Text, the warning disappears.
How can I mimic this behaviour in the API ie by setting the type to Text, the
warning is removed?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 54868] Write numbers to text cells in Excel generates warnings
that number is stored as text
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
--- Comment #2 from Mark B <ma...@tiscali.co.uk> ---
I have found where you will need to look. There is an element within the xml
markup for the sheet that allows you to specify which cell's errors ought to be
ignored, it looks a little like this;
<ignoredErrors>
<ignoredError sqref="A2" numberStoredAsText="1"/>
</ignoredErrors>
</worksheet>
and, as you can see from this fragment, is positioned close to the bottom of
the markup for the sheet. I am going to guess that you will need to create an
ignoredError element for each cell and am further going to guess that within
the xmlbeans layer for the sheet there should be an object to support this. You
will need to dig around within this layer to identify it however and the best
tool for this is a good IDE such as NetBeans or Eclipse.
As before, if you do find out the objects to use and are able to engineer a
opatch, please contribute it to the project.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 54868] Write numbers to text cells in Excel generates warnings
that number is stored as text
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |dominik.stadler@gmx.at
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 54868] Write numbers to text cells in Excel generates warnings
that number is stored as text
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
--- Comment #3 from emercoyle@hotmail.com ---
Do you know why this warning doesn't appear when you manually set this type in
Excel?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 54868] Write numbers to text cells in Excel generates warnings
that number is stored as text
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
--- Comment #4 from Mark B <ma...@tiscali.co.uk> ---
Well, it does, sort of.
If you enter an inverted comma symbol before the digit, then Excel will assume
that you are forcing it to accept a number as text and will display that error
message. Try entering '123 into a cell and you will see the error message
displayed - at least I do when using Excel 2007.
If you enter just the number - say 123 - then Excel will silently convert this
into a number for and store it into the file. When you press enter or click on
the tick in the formula bar after entering 123 into the cell, you should see
that it is automatically right aligned by Excel. You have the support of the
user interface - Excel - when you are creating the file that way. When you are
using POI, you do not have the support of the user interface and have to take
responsibility for encoding into the file all of the instructions necessary to
allow Excel to work correctly with the file at a later date.
You do have a few options if you do not feel up to patching the api. The first,
is to explicitly set the type of the cell after assigning it a value, so;
String cellValue = "123";
cell.setCellValue(cellValue);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
but I cannot guarantee that will work.
Alternatively,you can check the values you are writing into the cells and pass
an appropriate value to the setCellValue() method. A double value can be used
for most numeric values so you could do something like this;
// Assume the value you want to werite to the cell is in a String object
// like this
String cellValue = "123.45";
// Try to create a Double value from that. If this falis, you will
// see a NumberFormatException.
Double doubleValue = null;
try {
doubleValue = Double.parseDouble(cellValue);
cell.setCellValue(doubleValue.doubleValue());
}
catch(NumberFormatException nfe) {
cell.setCellValue(cellValue);
}
Not the best way to use an Exception perhaps, but what the code tries to do is
to create an instance of the Double class from the String. If that succeeds,
then the String contained a valid number (a double in this case, there is an
Integer alternative as well) and you can simply call the setCellValue(double)
method and pass it a double. Behind the scenes, this will ensure that the
cell's type is set correctly for you and you should not see an error when the
file is opened with Excel. If a NumberFormatException is thrown, then the
String did not contain a valid number and you can simply pass it to the
setCellValue(String) method.
Of course, life will be more complicated than this - not sure whether dates
will work if you simply pas them in a String - but it is a start.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 54868] Write numbers to text cells in Excel generates warnings
that number is stored as text
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
--- Comment #5 from Nick Burch <ap...@gagravarr.org> ---
This relates to bug #46136. Based on the progress with that bug, we have most
of the underlying record support to add to the file that the warning should be
ignored, but we don't have a design for how to expose that through UserModel,
nor code to do it (nor unit tests either!)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 54868] Write numbers to text cells in Excel generates warnings
that number is stored as text
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
Mark B <ma...@tiscali.co.uk> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from Mark B <ma...@tiscali.co.uk> ---
In my opinion, this is very far from being a bug. Excel allows the user to
specify error checking rules, one of which is to flag text cells that contain
numbers as being in error. To check on this, all you need to do is;
Open Excel.
Click on the Office Button that you should see in the upper left hand corner of
the screen.
On the pop-up screen that appears, look for a button that says 'Excel Options'
and click on it.
This will open u another screen and you should look for the word 'Formulas' in
the left hand list and then select it.
The right hand portion of the screen will now change and you should see a
section entitled 'Error checking rules' which contains a host of check boxes.
One of these checkboxes will say - Numbers formatted as text or preceded by an
apostrophe'.
This is normally checked and is more than likely the reason you are seeing
errors flagged when you view the sheet.
This is why I do not regard it as a bug, it is the default behaviour for Excel
if this check box has been selected by the user. What I think you should be
asking for is the ability to set these options through the application; an
enchancement. As everyone who works on POI is a volunteer, it is impossible to
say when this request would be filled and it might be better if you looked into
doing it yourself and then contributing the patch to the project.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org