You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Filip Defoort <fi...@cirquedigital.com> on 2010/01/05 20:38:18 UTC
bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Hi,
I've just stumbled across a bug in POI 3.6:
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
doesn't work if the cell is anything other than blank or a numeric
cell - which kinda defeats the purpose of the whole thing...
It turns out that HSSFCell.java does a
getNumericCellValue()
as part of the setCellType() call (XSSF doesn't have this problem).
The workaround is to first set the cell type to blank, then to numeric, like so:
cell.setCellType(Cell.CELL_TYPE_BLANK);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
While we're on the subject, I've never understood why
getNumericCellValue() doesn't try to parse the string value in case of
a text cell ? I wouldn't expect that to cause any bad side effects ?
(in fact, excel displays a warning when it detects this situation).
Hopefully someone can put a fix in for this in 3.7...
Thanks,
- Filip
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Posted by MSB <ma...@tiscali.co.uk>.
Hello Filip,
Yes, but assuming the code found two or more suitable formats which should
it then apply? I am not arguing that the API should not be modified because
I do agree with you that the setCellType() method should not fail. Instead,
I think that it can only be expected to offer the most basic of support; the
"$2,500" dollar example should be converted to 2500 in my opinion - assuming
a change from String to numeric - and it should be the developers'
responsibility to define and apply a suitable format.
With regard to getStringCellValue() I both agree and disagree. If you take a
look through the documentation for the
org.apache.poi.ss.usermodel.DataFormatter class, you will see that it
already defines methods to do pretty much what you require
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html#formatCellValue%28org.apache.poi.ss.usermodel.Cell%29.
So, I agree that what you suggested would be usful but do not think that the
getStringCellValue() method needs to be modified owing to the DataFormatter
class; if it does not meet your exact requirement, then it should be
possible to create a specialised subclass to handle this.
Yours
Mark B
Filip Defoort wrote:
>
> On Wed, Jan 6, 2010 at 11:55 PM, MSB <ma...@tiscali.co.uk> wrote:
>>
>> Yes, that would work for your requirement Filip but I can easilly imagine
>> another using say that the String value "$2,500" should be converted to a
>> number and a format should be generated and applied automatically to the
>> cell so that they did not lose the dollar sign and comma separator - and
>> is
>> this really the function of a setCellYtpe() method? I think that we could
>> convincingly argue that if you try to convert a cell whose type is text
>> or
>> string into a numeric cell (for example) then a warning should be issued
>> that information may be lost. In my opinion, it will not be possible to
>> catch and handle every little requirement, so the conversion of the value
>> should be performed and the user ought to be warned that changes may
>> occur
>> to the appearence of the cell.
>
> It probably wouldn't be that hard to loop over the data formats and
> see which ones fit and convert. A warning would indeed be nice or an
> extra parameter to the setCellType() method.
>
> E.g. setCellType(Cell.TYPE*, boolean convert)
>
> In either case, calls to setCellType() shouldn't fail.
>
> On a somewhat related topic: I'm really surprised that calls to
> .getStringCellValue() fail if the type of a cell is not string. It'd
> seem logical to me to return a string representation of the cell's
> value (or to have another method called .getCellDisplayValue() or
> something like that so a user has a single method they know they can
> always call to get the value of a cell without needing switch
> statements all over the code to handle variations).
>
> Cheers,
> - Filip
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/bug-in-cell.setCellType%28Cell.CELL_TYPE_NUMERIC%29-%28POI-3.6%29-tp27034280p27072580.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Posted by Filip Defoort <fi...@cirquedigital.com>.
On Wed, Jan 6, 2010 at 11:55 PM, MSB <ma...@tiscali.co.uk> wrote:
>
> Yes, that would work for your requirement Filip but I can easilly imagine
> another using say that the String value "$2,500" should be converted to a
> number and a format should be generated and applied automatically to the
> cell so that they did not lose the dollar sign and comma separator - and is
> this really the function of a setCellYtpe() method? I think that we could
> convincingly argue that if you try to convert a cell whose type is text or
> string into a numeric cell (for example) then a warning should be issued
> that information may be lost. In my opinion, it will not be possible to
> catch and handle every little requirement, so the conversion of the value
> should be performed and the user ought to be warned that changes may occur
> to the appearence of the cell.
It probably wouldn't be that hard to loop over the data formats and
see which ones fit and convert. A warning would indeed be nice or an
extra parameter to the setCellType() method.
E.g. setCellType(Cell.TYPE*, boolean convert)
In either case, calls to setCellType() shouldn't fail.
On a somewhat related topic: I'm really surprised that calls to
.getStringCellValue() fail if the type of a cell is not string. It'd
seem logical to me to return a string representation of the cell's
value (or to have another method called .getCellDisplayValue() or
something like that so a user has a single method they know they can
always call to get the value of a cell without needing switch
statements all over the code to handle variations).
Cheers,
- Filip
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Posted by MSB <ma...@tiscali.co.uk>.
Yes, that would work for your requirement Filip but I can easilly imagine
another using say that the String value "$2,500" should be converted to a
number and a format should be generated and applied automatically to the
cell so that they did not lose the dollar sign and comma separator - and is
this really the function of a setCellYtpe() method? I think that we could
convincingly argue that if you try to convert a cell whose type is text or
string into a numeric cell (for example) then a warning should be issued
that information may be lost. In my opinion, it will not be possible to
catch and handle every little requirement, so the conversion of the value
should be performed and the user ought to be warned that changes may occur
to the appearence of the cell.
Yours
Mark B
Filip Defoort wrote:
>
>> In my opinion, I think that poi should be changed to option (C) -
>> always attempt conversion, with strict parsing.
>>
>
> I would agree with that - it'd be really handy to have string to
> number conversion, but it can be a really simple numeric parsing
> without dealing with $, % and the likes.
>
> Cheers,
> - Filip
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/bug-in-cell.setCellType%28Cell.CELL_TYPE_NUMERIC%29-%28POI-3.6%29-tp27034280p27056259.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Posted by Filip Defoort <fi...@cirquedigital.com>.
> In my opinion, I think that poi should be changed to option (C) -
> always attempt conversion, with strict parsing.
>
I would agree with that - it'd be really handy to have string to
number conversion, but it can be a really simple numeric parsing
without dealing with $, % and the likes.
Cheers,
- Filip
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Posted by Josh Micich <jo...@gmail.com>.
I was a little surprised to see how inconsistent the behaviour of
setCellType() is. Here is a table of what POI currently does (Row
headings are 'from data type' column headings are 'to data type'):
From\To num str bool err
num - OK OK 1
str 2 - OK 1
bool OK OK - 1
err 3 OK 4 -
Notes:
1 HSSF sets the cell to #VALUE!, XSSF has bug (exception)
2 HSSF IllegalStateException, XSSF sets cell to 0.0
3 HSSF IllegalStateException, XSSF bug (exception)
4 HSSF and XSSF set cell to FALSE
5 HSSF sets vell to #VALUE!, XSSF sets cell to #NULL!
Conversions involving blank seem to be mostly correct. Setting any
cell type to blank clears it. Setting any blank cell to another type
results in the default value for that type (however HSSF and XSSF
disagree on what the default error value is).
Before making any change I thought it would be a good idea to gather
some input from interested parties. We can probably all agree that
the behaviour should be more consistent.
Firstly we need to decide on whether/how much Cell.setCellType should
perform automatic type conversion:
(A) never automatically convert data (only valid transitions are to/from blank).
(B) only perform conversions that can never fail (any type to string,
number to boolean). POI is currently closest to this.
(C) always attempt conversion of data types.
There are a few other related details to decide:
- What action should be taken when attempting a prohibited
conversion? (e.g. throw an exception / set default value)
- When conversion requires parsing, how strict should it be? (e.g.
is "$2,500" valid number, or is "no" a valid boolean value?)
- Should an explicit convertCellType() method be added?
In my opinion, I think that poi should be changed to option (C) -
always attempt conversion, with strict parsing.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Posted by Filip Defoort <fi...@cirquedigital.com>.
Oh, and one more: unrecognized formulas (such as SUMIFS()) break POI.
It'd be great if it could just ignore those but at least keep
working...
- Filip
On Thu, Jan 7, 2010 at 10:35 AM, Filip Defoort
<fi...@cirquedigital.com> wrote:
> Hi Nick,
>
> I'll write up some test cases - I have a few bugs that I got bitten by
> recently (XSSF can't handle URL links in case protocol is not file or
> http; HSSF removeSheet destroys Named Ranges and Formulas that
> reference other sheets; HSSF setCellType doesn't work in some cases).
>
> I'll try to find some time over the next day or two to write up a
> working testcase for each.
>
> - Filip
>
> On Wed, Jan 6, 2010 at 2:37 AM, Nick Burch <ni...@alfresco.com> wrote:
>> On Tue, 5 Jan 2010, Filip Defoort wrote:
>>>
>>> I've just stumbled across a bug in POI 3.6:
>>>
>>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>>
>>> doesn't work if the cell is anything other than blank or a numeric
>>> cell - which kinda defeats the purpose of the whole thing...
>>
>> Any chance you could open a new bug on bugzilla, and upload a quick little
>> test case of the java that makes this fall over? That'll give us something
>> to test against when we come to fix it, plus then a test to make sure it
>> doesn't get broken again in the future! :)
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Posted by Filip Defoort <fi...@cirquedigital.com>.
Hi Nick,
I'll write up some test cases - I have a few bugs that I got bitten by
recently (XSSF can't handle URL links in case protocol is not file or
http; HSSF removeSheet destroys Named Ranges and Formulas that
reference other sheets; HSSF setCellType doesn't work in some cases).
I'll try to find some time over the next day or two to write up a
working testcase for each.
- Filip
On Wed, Jan 6, 2010 at 2:37 AM, Nick Burch <ni...@alfresco.com> wrote:
> On Tue, 5 Jan 2010, Filip Defoort wrote:
>>
>> I've just stumbled across a bug in POI 3.6:
>>
>> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>>
>> doesn't work if the cell is anything other than blank or a numeric
>> cell - which kinda defeats the purpose of the whole thing...
>
> Any chance you could open a new bug on bugzilla, and upload a quick little
> test case of the java that makes this fall over? That'll give us something
> to test against when we come to fix it, plus then a test to make sure it
> doesn't get broken again in the future! :)
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: bug in cell.setCellType(Cell.CELL_TYPE_NUMERIC) (POI 3.6)
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 5 Jan 2010, Filip Defoort wrote:
> I've just stumbled across a bug in POI 3.6:
>
> cell.setCellType(Cell.CELL_TYPE_NUMERIC);
>
> doesn't work if the cell is anything other than blank or a numeric
> cell - which kinda defeats the purpose of the whole thing...
Any chance you could open a new bug on bugzilla, and upload a quick little
test case of the java that makes this fall over? That'll give us something
to test against when we come to fix it, plus then a test to make sure it
doesn't get broken again in the future! :)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org