You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by yehogold <ye...@yahoo.com> on 2009/08/11 20:35:39 UTC
The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC
cell with 0.
Hi.
I'm trying to test if a specific cell is empty. For TYPE_STRING cells, I
can get the cellStringValue and check it against an empty string.
getNumericValue(), returns '0' when the cell is empty, making it impossible
to differentiate between a truly empty TYPE_NUMERIC cell and one that
contains '0'.
How can a I check if a cell is truly empty?
Thank you in advance for all your help.
--
View this message in context: http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24923291.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: The difference between a blank TYPE_NUMERIC cell and a
TYPE_NUMERIC cell with 0.
Posted by MSB <ma...@tiscali.co.uk>.
Just been able to play with some code and found that if the cell is genuinely
empty then it will be reported as type BLANK. I only had a couple of cells
on the sheet, one had been explicitly set to be numeric, the other text;
initially neither had a value and they both reported as BLANK. Next I
initialised both and they reported as the correct type and finally, I
cleared them both out again and they reverted to being BLANK as far as HSSF
was concerned.
The only thing I did not try was setting a format on the cells; whether this
has any bearing upon how the code will behave, I cannot guess but will try a
little later.
Yours
Mark B
yehogold wrote:
>
> But if the TYPE_NUMERIC cell is empty, won't it still be a TYPE_NUMERIC
> cell?
>
>
> VK123 wrote:
>>
>> You can use TYPE_BLANK to check if the cell is empty.
>>
>> Regards,
>>
>> Vijayakumar Gowdaman
>>
>> Group Technology & Operations (GTO)
>> Global Markets
>> Deutsche Bank
>> off: 02075456250
>> Mob:07789773998
>>
>>
>>
>> yehogold <ye...@yahoo.com>
>> 11/08/2009 19:35
>> Please respond to
>> "POI Users List" <us...@poi.apache.org>
>>
>>
>> To
>> user@poi.apache.org
>> cc
>>
>> Subject
>> The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC cell
>> with 0.
>>
>>
>>
>>
>>
>>
>>
>> Hi.
>>
>> I'm trying to test if a specific cell is empty. For TYPE_STRING cells, I
>> can get the cellStringValue and check it against an empty string.
>> getNumericValue(), returns '0' when the cell is empty, making it
>> impossible
>> to differentiate between a truly empty TYPE_NUMERIC cell and one that
>> contains '0'.
>>
>> How can a I check if a cell is truly empty?
>>
>> Thank you in advance for all your help.
>> --
>> View this message in context:
>> http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24923291.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
>>
>>
>>
>>
>>
>> ---
>>
>> This e-mail may contain confidential and/or privileged information. If
>> you are not the intended recipient (or have received this e-mail in
>> error) please notify the sender immediately and delete this e-mail. Any
>> unauthorized copying, disclosure or distribution of the material in this
>> e-mail is strictly forbidden.
>>
>> Please refer to http://www.db.com/en/content/eu_disclosures.htm for
>> additional EU corporate and regulatory disclosures.
>>
>
>
--
View this message in context: http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24953106.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: The difference between a blank TYPE_NUMERIC cell and a
TYPE_NUMERIC cell with 0.
Posted by MSB <ma...@tiscali.co.uk>.
Tried a couple more things and still got the same results.
Furstly, I tried declaring the cell to be numeric and chose one of the
default formats from the list; when that cell had nothing in POI reported it
as a BLANK. Finally, I tried delaring the cell as type Custom and specifying
some arcane accoutning format for the cells contents; still when it was
empty, POI reported it as a type BLANK again.
Do not know if that helps at all but I think POI is doing what I would
expect.
Yours
Mark B
yehogold wrote:
>
> But if the TYPE_NUMERIC cell is empty, won't it still be a TYPE_NUMERIC
> cell?
>
>
> VK123 wrote:
>>
>> You can use TYPE_BLANK to check if the cell is empty.
>>
>> Regards,
>>
>> Vijayakumar Gowdaman
>>
>> Group Technology & Operations (GTO)
>> Global Markets
>> Deutsche Bank
>> off: 02075456250
>> Mob:07789773998
>>
>>
>>
>> yehogold <ye...@yahoo.com>
>> 11/08/2009 19:35
>> Please respond to
>> "POI Users List" <us...@poi.apache.org>
>>
>>
>> To
>> user@poi.apache.org
>> cc
>>
>> Subject
>> The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC cell
>> with 0.
>>
>>
>>
>>
>>
>>
>>
>> Hi.
>>
>> I'm trying to test if a specific cell is empty. For TYPE_STRING cells, I
>> can get the cellStringValue and check it against an empty string.
>> getNumericValue(), returns '0' when the cell is empty, making it
>> impossible
>> to differentiate between a truly empty TYPE_NUMERIC cell and one that
>> contains '0'.
>>
>> How can a I check if a cell is truly empty?
>>
>> Thank you in advance for all your help.
>> --
>> View this message in context:
>> http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24923291.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
>>
>>
>>
>>
>>
>> ---
>>
>> This e-mail may contain confidential and/or privileged information. If
>> you are not the intended recipient (or have received this e-mail in
>> error) please notify the sender immediately and delete this e-mail. Any
>> unauthorized copying, disclosure or distribution of the material in this
>> e-mail is strictly forbidden.
>>
>> Please refer to http://www.db.com/en/content/eu_disclosures.htm for
>> additional EU corporate and regulatory disclosures.
>>
>
>
--
View this message in context: http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24955293.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: The difference between a blank TYPE_NUMERIC cell and a
TYPE_NUMERIC cell with 0.
Posted by yehogold <ye...@yahoo.com>.
I changed my code to simply test if the cell was of TYPE_BLANK and modified
the type of the cell when I changed its value later on in the code to
circumvent the need for it to retain its value when blank, just to be safe.
It seems to work just fine now.
Thank you very much for your help,
yehogold
MSB wrote:
>
> ....and I should have said that it is likely that something else is
> happening with your workbooks/worksheets.
>
> Can you post just a quick snippet of code to show how you are 'processing'
> the cells you get from the worksheets please; something like;
>
> HSSFSheet sheet = workbook.getSheetAt(0);
> Iterator<Row> rowIter = sheet.rowIterator();
> while(rowIter.hasNext()) {
> Row row = rowIter.next();
> Iterator<Cell> cellIter = row.cellIterator();
> while(cellIter.hasNext()) {
> Cell cell = cellIter.next();
> ..................
> }
> }
>
>
>
> yehogold wrote:
>>
>> But if the TYPE_NUMERIC cell is empty, won't it still be a TYPE_NUMERIC
>> cell?
>>
>>
>> VK123 wrote:
>>>
>>> You can use TYPE_BLANK to check if the cell is empty.
>>>
>>> Regards,
>>>
>>> Vijayakumar Gowdaman
>>>
>>> Group Technology & Operations (GTO)
>>> Global Markets
>>> Deutsche Bank
>>> off: 02075456250
>>> Mob:07789773998
>>>
>>>
>>>
>>> yehogold <ye...@yahoo.com>
>>> 11/08/2009 19:35
>>> Please respond to
>>> "POI Users List" <us...@poi.apache.org>
>>>
>>>
>>> To
>>> user@poi.apache.org
>>> cc
>>>
>>> Subject
>>> The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC cell
>>> with 0.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Hi.
>>>
>>> I'm trying to test if a specific cell is empty. For TYPE_STRING cells,
>>> I
>>> can get the cellStringValue and check it against an empty string.
>>> getNumericValue(), returns '0' when the cell is empty, making it
>>> impossible
>>> to differentiate between a truly empty TYPE_NUMERIC cell and one that
>>> contains '0'.
>>>
>>> How can a I check if a cell is truly empty?
>>>
>>> Thank you in advance for all your help.
>>> --
>>> View this message in context:
>>> http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24923291.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
>>>
>>>
>>>
>>>
>>>
>>> ---
>>>
>>> This e-mail may contain confidential and/or privileged information. If
>>> you are not the intended recipient (or have received this e-mail in
>>> error) please notify the sender immediately and delete this e-mail. Any
>>> unauthorized copying, disclosure or distribution of the material in this
>>> e-mail is strictly forbidden.
>>>
>>> Please refer to http://www.db.com/en/content/eu_disclosures.htm for
>>> additional EU corporate and regulatory disclosures.
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24955842.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: The difference between a blank TYPE_NUMERIC cell and a
TYPE_NUMERIC cell with 0.
Posted by MSB <ma...@tiscali.co.uk>.
....and I should have said that it is likely that something else is happening
with your workbooks/worksheets.
Can you post just a quick snippet of code to show how you are 'processing'
the cells you get from the worksheets please; something like;
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIter = sheet.rowIterator();
while(rowIter.hasNext()) {
Row row = rowIter.next();
Iterator<Cell> cellIter = row.cellIterator();
while(cellIter.hasNext()) {
Cell cell = cellIter.next();
..................
}
}
yehogold wrote:
>
> But if the TYPE_NUMERIC cell is empty, won't it still be a TYPE_NUMERIC
> cell?
>
>
> VK123 wrote:
>>
>> You can use TYPE_BLANK to check if the cell is empty.
>>
>> Regards,
>>
>> Vijayakumar Gowdaman
>>
>> Group Technology & Operations (GTO)
>> Global Markets
>> Deutsche Bank
>> off: 02075456250
>> Mob:07789773998
>>
>>
>>
>> yehogold <ye...@yahoo.com>
>> 11/08/2009 19:35
>> Please respond to
>> "POI Users List" <us...@poi.apache.org>
>>
>>
>> To
>> user@poi.apache.org
>> cc
>>
>> Subject
>> The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC cell
>> with 0.
>>
>>
>>
>>
>>
>>
>>
>> Hi.
>>
>> I'm trying to test if a specific cell is empty. For TYPE_STRING cells, I
>> can get the cellStringValue and check it against an empty string.
>> getNumericValue(), returns '0' when the cell is empty, making it
>> impossible
>> to differentiate between a truly empty TYPE_NUMERIC cell and one that
>> contains '0'.
>>
>> How can a I check if a cell is truly empty?
>>
>> Thank you in advance for all your help.
>> --
>> View this message in context:
>> http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24923291.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
>>
>>
>>
>>
>>
>> ---
>>
>> This e-mail may contain confidential and/or privileged information. If
>> you are not the intended recipient (or have received this e-mail in
>> error) please notify the sender immediately and delete this e-mail. Any
>> unauthorized copying, disclosure or distribution of the material in this
>> e-mail is strictly forbidden.
>>
>> Please refer to http://www.db.com/en/content/eu_disclosures.htm for
>> additional EU corporate and regulatory disclosures.
>>
>
>
--
View this message in context: http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24955534.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: The difference between a blank TYPE_NUMERIC cell and a
TYPE_NUMERIC cell with 0.
Posted by MSB <ma...@tiscali.co.uk>.
There is an easy way to test this hypothesis of course; just perform the test
for type in a specific order and ensure that the test for the blank/empty
type precedes that for the numeric cell type; must admit though that I
cannot see that this is the answer however.
Are the numeric cells you are dealing with genuinely empty? Indeed, is it
possible to have an empty numeric cell on worksheet? Sill question maybe but
I wonder what Excel does with empty numeric cells; does it have a
placeholder value of some sort, a little like NaN? Have you used the
BiffViewer utility to look at the contents of the sheet(s) in question? This
may give you an indication of the way the API is processing 'empty' numeric
cells. If I have the chance today - and I cannot promise anything, I will
try to play with some code myself just to see what I can find out. It may be
the case that the API needs to be modified so that it includes an isEmpty()
method for example, defined at the Cell level and returning a boolean value
that indicates whether or not the cell is empty; or to suit this specific
case modify getNumericCellValue() to return a java.lang.Double then it would
be possible to return NaN if the cell was empty.
Yours
Mark B
yehogold wrote:
>
> But if the TYPE_NUMERIC cell is empty, won't it still be a TYPE_NUMERIC
> cell?
>
>
> VK123 wrote:
>>
>> You can use TYPE_BLANK to check if the cell is empty.
>>
>> Regards,
>>
>> Vijayakumar Gowdaman
>>
>> Group Technology & Operations (GTO)
>> Global Markets
>> Deutsche Bank
>> off: 02075456250
>> Mob:07789773998
>>
>>
>>
>> yehogold <ye...@yahoo.com>
>> 11/08/2009 19:35
>> Please respond to
>> "POI Users List" <us...@poi.apache.org>
>>
>>
>> To
>> user@poi.apache.org
>> cc
>>
>> Subject
>> The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC cell
>> with 0.
>>
>>
>>
>>
>>
>>
>>
>> Hi.
>>
>> I'm trying to test if a specific cell is empty. For TYPE_STRING cells, I
>> can get the cellStringValue and check it against an empty string.
>> getNumericValue(), returns '0' when the cell is empty, making it
>> impossible
>> to differentiate between a truly empty TYPE_NUMERIC cell and one that
>> contains '0'.
>>
>> How can a I check if a cell is truly empty?
>>
>> Thank you in advance for all your help.
>> --
>> View this message in context:
>> http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24923291.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
>>
>>
>>
>>
>>
>> ---
>>
>> This e-mail may contain confidential and/or privileged information. If
>> you are not the intended recipient (or have received this e-mail in
>> error) please notify the sender immediately and delete this e-mail. Any
>> unauthorized copying, disclosure or distribution of the material in this
>> e-mail is strictly forbidden.
>>
>> Please refer to http://www.db.com/en/content/eu_disclosures.htm for
>> additional EU corporate and regulatory disclosures.
>>
>
>
--
View this message in context: http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24949447.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: The difference between a blank TYPE_NUMERIC cell and a
TYPE_NUMERIC cell with 0.
Posted by yehogold <ye...@yahoo.com>.
But if the TYPE_NUMERIC cell is empty, won't it still be a TYPE_NUMERIC cell?
VK123 wrote:
>
> You can use TYPE_BLANK to check if the cell is empty.
>
> Regards,
>
> Vijayakumar Gowdaman
>
> Group Technology & Operations (GTO)
> Global Markets
> Deutsche Bank
> off: 02075456250
> Mob:07789773998
>
>
>
> yehogold <ye...@yahoo.com>
> 11/08/2009 19:35
> Please respond to
> "POI Users List" <us...@poi.apache.org>
>
>
> To
> user@poi.apache.org
> cc
>
> Subject
> The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC cell
> with 0.
>
>
>
>
>
>
>
> Hi.
>
> I'm trying to test if a specific cell is empty. For TYPE_STRING cells, I
> can get the cellStringValue and check it against an empty string.
> getNumericValue(), returns '0' when the cell is empty, making it
> impossible
> to differentiate between a truly empty TYPE_NUMERIC cell and one that
> contains '0'.
>
> How can a I check if a cell is truly empty?
>
> Thank you in advance for all your help.
> --
> View this message in context:
> http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24923291.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
>
>
>
>
>
> ---
>
> This e-mail may contain confidential and/or privileged information. If you
> are not the intended recipient (or have received this e-mail in error)
> please notify the sender immediately and delete this e-mail. Any
> unauthorized copying, disclosure or distribution of the material in this
> e-mail is strictly forbidden.
>
> Please refer to http://www.db.com/en/content/eu_disclosures.htm for
> additional EU corporate and regulatory disclosures.
>
--
View this message in context: http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24939009.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: The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC
cell with 0.
Posted by Vijayakumar Gowdaman <vi...@db.com>.
You can use TYPE_BLANK to check if the cell is empty.
Regards,
Vijayakumar Gowdaman
Group Technology & Operations (GTO)
Global Markets
Deutsche Bank
off: 02075456250
Mob:07789773998
yehogold <ye...@yahoo.com>
11/08/2009 19:35
Please respond to
"POI Users List" <us...@poi.apache.org>
To
user@poi.apache.org
cc
Subject
The difference between a blank TYPE_NUMERIC cell and a TYPE_NUMERIC cell
with 0.
Hi.
I'm trying to test if a specific cell is empty. For TYPE_STRING cells, I
can get the cellStringValue and check it against an empty string.
getNumericValue(), returns '0' when the cell is empty, making it
impossible
to differentiate between a truly empty TYPE_NUMERIC cell and one that
contains '0'.
How can a I check if a cell is truly empty?
Thank you in advance for all your help.
--
View this message in context:
http://www.nabble.com/The-difference-between-a-blank-TYPE_NUMERIC-cell-and-a-TYPE_NUMERIC-cell-with-0.-tp24923291p24923291.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
---
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Please refer to http://www.db.com/en/content/eu_disclosures.htm for additional EU corporate and regulatory disclosures.