poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 54868] Write numbers to text cells in Excel generates warnings that number is stored as text
Date Mon, 22 Apr 2013 16:07:44 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868

--- Comment #4 from Mark B <markbrdsly@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


Mime
View raw message