Bug 54868 - Write numbers to text cells in Excel generates warnings that number is stored as text
Summary: Write numbers to text cells in Excel generates warnings that number is stored...
Status: RESOLVED DUPLICATE of bug 58641
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 normal with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-04-19 14:58 UTC by emercoyle
Modified: 2015-11-23 21:50 UTC (History)
2 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description emercoyle 2013-04-19 14:58:30 UTC
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?
Comment 1 Mark B 2013-04-19 16:34:35 UTC
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.
Comment 2 Mark B 2013-04-20 11:55:47 UTC
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.
Comment 3 emercoyle 2013-04-22 08:27:58 UTC
Do you know why this warning doesn't appear when you manually set this type in Excel?
Comment 4 Mark B 2013-04-22 16:07:44 UTC
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.
Comment 5 Nick Burch 2013-06-25 15:02:49 UTC
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!)
Comment 6 David North 2015-06-12 15:40:34 UTC
See also bug #56892
Comment 7 peterhansson_se 2015-11-23 21:50:55 UTC
I've added bug 58641 which is the generalized RFE of what is being requested here.

*** This bug has been marked as a duplicate of bug 58641 ***