You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Kalpesh Modi <ka...@plateau.com> on 2010/07/12 18:55:22 UTC
Getting Data type
Hi,
I have created an Excel template with few columns:
Name of the columns: User, First Name, Last Name, DOB
The DOB column is formatted as Date when creating the workbook template.
When I read the cell type for the DOB field, i.e. the actual column
header DOB, it returns as String, even though its type is set to be
Date.
Any ideas, how I can get the cell type as Date.
Thanks and regards,
-Kalpesh
.
The information contained in this e-mail message is intended only for the personal
and confidential use of the recipient(s) named above. This message is privileged
and confidential. If the reader of this message is not the intended recipient or an
agent responsible for delivering it to the intended recipient, you are hereby notified
that you have received this document in error and that any review, dissemination,
distribution, or copying of this message is strictly prohibited.
RE: Getting Data type
Posted by MSB <ma...@tiscali.co.uk>.
Must admit that I agree, and for more than one reason. You may have seen that
the size of your original template file was quite large - this was down to
the fact that you had set the format on the entire column. Setting the
formats instead on just single cells within a column should mean that the
template file will shrink down in size, not dramatically I admit, but
somewhat.
All the best with your project and if you have any further questions, do not
hesitate to post a message onto the list.
Yours
Mark B
Kalpesh Modi-2 wrote:
>
> Thank you very much for your detailed explanation and solution.
>
> I think the second solution of creating a dummy row is more suitable for
> my application.
>
> Thanks and regards,
> -Kalpesh
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, July 13, 2010 9:26 AM
> To: user@poi.apache.org
> Subject: RE: Getting Data type
>
>
> I suspect that the reason the cell is being reported as type String is
> because POI is detecting a String in that cell. If you think back to my
> previous reply, I indicated that it is possible to enter one of three
> different types of data into a cell and a Date is not actually a date
> value
> as we would read it but a specially formatted number. That is why it is
> common when reading a file using POI to test the cells type and, if it
> is
> numeric, call the DateUtil.isCellDateFormatted() method to see if you
> have a
> cell holding a date value. The problem you are facing now is that
> because
> you have a String of text in that header cell - Date Of Birth - you
> cannot
> use the DateUtil method (or at least I do not think you can as I have
> never
> come across this problem myself and it would certainly be worth
> experimenting with this test as it is only two or three lines of code)
> to
> test the cells type for you.
>
> To my mind, there are two possible solutions. The first would be to read
> the
> label and see if it contains the word 'Date'. That way you would know
> what
> you were dealing with and could process the data accordingly, but this
> assumes the label may never change and it is not beyond the realms of
> possibility to imagine the user wanting DOB instead of Date Of Birth.
> The
> other option would be to modify your template a little. Just for the
> sake of
> discussion, imagine that row 1 - POI index 0 - contains the labels, then
> you
> could insert dummy data into row 2 (POI index 1). By dummy data, I mean
> values that are representative of those you would expect to see in the
> cells
> - text cells could just contain the word 'text' for example, any numeric
> cells 0 and dates something like 10/10/2009. When you open the template
> using POI, you could read this dummy row to determine what the types of
> the
> cells are and note each using a Map. The cells type could be represented
> by
> a simple word such as 'string', 'numeric' 'date' etc and the key for
> each
> would be the column index converted into a String. Then, when you
> populate
> the worksheet with 'real' data, it would be possible to use the column
> index, convert that into a String, use this as the ley into the map and
> from
> the value returned discover the cells type. Of course, you would
> overwrite
> the dummy values in the first row of cells. The further advantage this
> would
> offer is that you could set the formatting for each cell using Excel,
> read
> that using POI and then apply the style as each row is created possibly
> saving you some time and insulating the application from changes to the
> cells style.
>
> Alternatively, if you know the types in advance and do not expect them
> to
> change, you could create this map without the need to use a dummy row -
> in a
> constructor - and, finally, if you cannot know in advance, what about
> simply
> listing the types in a properties file that the application could read?
> You
> could pass the name of the properties file to the application at runtime
> making it possible to change the listing of data types on the fly.
>
> Yours
>
> Mark B
>
> PS I would be interested to discover what happens if you set the type of
> a
> column of cells to numeric and then enter a label such as 'Total' into
> the
> cell at the top of the column and then read this cell using POI; what
> type
> would POI report for this cell I wonder?
>
>
>
> Kalpesh Modi-2 wrote:
>>
>> Thanks for your reply.
>>
>> What I want to do is that I have a excel template.
>>
>> It has lot many columns with column names in the first row, and one of
>> them is labeled "Date Of Birth". The "Date Of Birth" column label is
>> itself a String but I have formatted the column type to be date. So
> any
>> value I enter in that column other than the first row, is formatted as
> a
>> date.
>>
>> User First Name DOB
>> Kal1 Kalpesh 01/01/1901
>>
>> The "DOB" column is formatted as date. When the user uploads a file, I
>> want to first check for the header data types in the first row.
>>
>> What I am expecting is that when I check the data type (ONLY THE
> HEADER
>> LABEL FIRST ROW) for User it returns String, First Name returns String
>> and DOB also returns String. Actually I am expecting DOB to be Date. I
>> am not talking about the data type of the actual data from the second
>> row onwards.
>>
>> Thanks and regards,
>> -Kalpesh
>>
>>
>> -----Original Message-----
>> From: MSB [mailto:markbrdsly@tiscali.co.uk]
>> Sent: Tuesday, July 13, 2010 2:39 AM
>> To: user@poi.apache.org
>> Subject: Re: Getting Data type
>>
>>
>> First some bad news; it is only possible to enter one of three
> different
>> types of value into a cell, a number, a String or a forumla which is
>> itslef
>> a type of String I guess. Then you apply a format to that value to
>> transform
>> the cells content into a Date value a currency value, etc. Therefore,
> it
>> is
>> likely that POI is reporting the type of the cell correctly - it is a
>> String
>> - that has then been formatted to look like a date.
>>
>> The answer to your question depends a lot on what you actually want to
>> do
>> with the value. If all you want to do is recover the contents of the
>> cell as
>> a String and formatted as they appeared on the worksheet, then try
> using
>> DataFormatter -
>>
> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.
>> html
>> - which will return the contents of the cell to you as a correctly
>> formatted
>> String. However, if you need the contents of the cell as a Date value,
>> you
>> will first need to make sure that the cell does contain a date and
> there
>> is
>> a class called DateUtil -
>>
> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html
>> - to
>> help, simply call the static isCellDateFormatted(Cell) method and pass
>> across the cell. This method will return true if the cell is formatted
>> as a
>> date. Next, you will need to convert the String value the cell
> contains
>> into
>> a Date object and this should be possible using the methods in the
> core
>> Java
>> api - DateFormat.parse(String) for example - passing the cell's
> contents
>> as
>> a String by calling either cell.GetRichStringCellValue().getString()
> or
>> getStringCellValue(). It may be worth making a second check after the
>> isCellDateFormatted() for cell type String. It could also be worth
>> checking
>> to see whether you could use the DataFormatter class's methods to
>> convert
>> the cells contents into a String and then simply pass this value to
> the
>> Date
>> class constructor also.
>>
>> Hope this helps a little.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Kalpesh Modi-2 wrote:
>>>
>>> Hi,
>>>
>>>
>>>
>>> I have created an Excel template with few columns:
>>>
>>>
>>>
>>> Name of the columns: User, First Name, Last Name, DOB
>>>
>>>
>>>
>>> The DOB column is formatted as Date when creating the workbook
>> template.
>>>
>>>
>>>
>>> When I read the cell type for the DOB field, i.e. the actual column
>>> header DOB, it returns as String, even though its type is set to be
>>> Date.
>>>
>>>
>>>
>>> Any ideas, how I can get the cell type as Date.
>>>
>>>
>>>
>>> Thanks and regards,
>>>
>>> -Kalpesh
>>>
>>>
>>>
>>>
>>> .
>>> The information contained in this e-mail message is intended only for
>> the
>>> personal
>>> and confidential use of the recipient(s) named above. This message is
>>> privileged
>>> and confidential. If the reader of this message is not the intended
>>> recipient or an
>>> agent responsible for delivering it to the intended recipient, you
> are
>>> hereby notified
>>> that you have received this document in error and that any review,
>>> dissemination,
>>> distribution, or copying of this message is strictly prohibited.
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Getting-Data-type-tp29141500p29147093.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
>>
>> .
>> The information contained in this e-mail message is intended only for
> the
>> personal
>> and confidential use of the recipient(s) named above. This message is
>> privileged
>> and confidential. If the reader of this message is not the intended
>> recipient or an
>> agent responsible for delivering it to the intended recipient, you are
>> hereby notified
>> that you have received this document in error and that any review,
>> dissemination,
>> distribution, or copying of this message is strictly prohibited.
>>
>>
>>
>> ---------------------------------------------------------------------
>> 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/Getting-Data-type-tp29141500p29150357.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
>
> .
> The information contained in this e-mail message is intended only for the
> personal
> and confidential use of the recipient(s) named above. This message is
> privileged
> and confidential. If the reader of this message is not the intended
> recipient or an
> agent responsible for delivering it to the intended recipient, you are
> hereby notified
> that you have received this document in error and that any review,
> dissemination,
> distribution, or copying of this message is strictly prohibited.
>
>
>
> ---------------------------------------------------------------------
> 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/Getting-Data-type-tp29141500p29162997.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: Getting Data type
Posted by Kalpesh Modi <ka...@plateau.com>.
Thank you very much for your detailed explanation and solution.
I think the second solution of creating a dummy row is more suitable for
my application.
Thanks and regards,
-Kalpesh
-----Original Message-----
From: MSB [mailto:markbrdsly@tiscali.co.uk]
Sent: Tuesday, July 13, 2010 9:26 AM
To: user@poi.apache.org
Subject: RE: Getting Data type
I suspect that the reason the cell is being reported as type String is
because POI is detecting a String in that cell. If you think back to my
previous reply, I indicated that it is possible to enter one of three
different types of data into a cell and a Date is not actually a date
value
as we would read it but a specially formatted number. That is why it is
common when reading a file using POI to test the cells type and, if it
is
numeric, call the DateUtil.isCellDateFormatted() method to see if you
have a
cell holding a date value. The problem you are facing now is that
because
you have a String of text in that header cell - Date Of Birth - you
cannot
use the DateUtil method (or at least I do not think you can as I have
never
come across this problem myself and it would certainly be worth
experimenting with this test as it is only two or three lines of code)
to
test the cells type for you.
To my mind, there are two possible solutions. The first would be to read
the
label and see if it contains the word 'Date'. That way you would know
what
you were dealing with and could process the data accordingly, but this
assumes the label may never change and it is not beyond the realms of
possibility to imagine the user wanting DOB instead of Date Of Birth.
The
other option would be to modify your template a little. Just for the
sake of
discussion, imagine that row 1 - POI index 0 - contains the labels, then
you
could insert dummy data into row 2 (POI index 1). By dummy data, I mean
values that are representative of those you would expect to see in the
cells
- text cells could just contain the word 'text' for example, any numeric
cells 0 and dates something like 10/10/2009. When you open the template
using POI, you could read this dummy row to determine what the types of
the
cells are and note each using a Map. The cells type could be represented
by
a simple word such as 'string', 'numeric' 'date' etc and the key for
each
would be the column index converted into a String. Then, when you
populate
the worksheet with 'real' data, it would be possible to use the column
index, convert that into a String, use this as the ley into the map and
from
the value returned discover the cells type. Of course, you would
overwrite
the dummy values in the first row of cells. The further advantage this
would
offer is that you could set the formatting for each cell using Excel,
read
that using POI and then apply the style as each row is created possibly
saving you some time and insulating the application from changes to the
cells style.
Alternatively, if you know the types in advance and do not expect them
to
change, you could create this map without the need to use a dummy row -
in a
constructor - and, finally, if you cannot know in advance, what about
simply
listing the types in a properties file that the application could read?
You
could pass the name of the properties file to the application at runtime
making it possible to change the listing of data types on the fly.
Yours
Mark B
PS I would be interested to discover what happens if you set the type of
a
column of cells to numeric and then enter a label such as 'Total' into
the
cell at the top of the column and then read this cell using POI; what
type
would POI report for this cell I wonder?
Kalpesh Modi-2 wrote:
>
> Thanks for your reply.
>
> What I want to do is that I have a excel template.
>
> It has lot many columns with column names in the first row, and one of
> them is labeled "Date Of Birth". The "Date Of Birth" column label is
> itself a String but I have formatted the column type to be date. So
any
> value I enter in that column other than the first row, is formatted as
a
> date.
>
> User First Name DOB
> Kal1 Kalpesh 01/01/1901
>
> The "DOB" column is formatted as date. When the user uploads a file, I
> want to first check for the header data types in the first row.
>
> What I am expecting is that when I check the data type (ONLY THE
HEADER
> LABEL FIRST ROW) for User it returns String, First Name returns String
> and DOB also returns String. Actually I am expecting DOB to be Date. I
> am not talking about the data type of the actual data from the second
> row onwards.
>
> Thanks and regards,
> -Kalpesh
>
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, July 13, 2010 2:39 AM
> To: user@poi.apache.org
> Subject: Re: Getting Data type
>
>
> First some bad news; it is only possible to enter one of three
different
> types of value into a cell, a number, a String or a forumla which is
> itslef
> a type of String I guess. Then you apply a format to that value to
> transform
> the cells content into a Date value a currency value, etc. Therefore,
it
> is
> likely that POI is reporting the type of the cell correctly - it is a
> String
> - that has then been formatted to look like a date.
>
> The answer to your question depends a lot on what you actually want to
> do
> with the value. If all you want to do is recover the contents of the
> cell as
> a String and formatted as they appeared on the worksheet, then try
using
> DataFormatter -
>
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.
> html
> - which will return the contents of the cell to you as a correctly
> formatted
> String. However, if you need the contents of the cell as a Date value,
> you
> will first need to make sure that the cell does contain a date and
there
> is
> a class called DateUtil -
>
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html
> - to
> help, simply call the static isCellDateFormatted(Cell) method and pass
> across the cell. This method will return true if the cell is formatted
> as a
> date. Next, you will need to convert the String value the cell
contains
> into
> a Date object and this should be possible using the methods in the
core
> Java
> api - DateFormat.parse(String) for example - passing the cell's
contents
> as
> a String by calling either cell.GetRichStringCellValue().getString()
or
> getStringCellValue(). It may be worth making a second check after the
> isCellDateFormatted() for cell type String. It could also be worth
> checking
> to see whether you could use the DataFormatter class's methods to
> convert
> the cells contents into a String and then simply pass this value to
the
> Date
> class constructor also.
>
> Hope this helps a little.
>
> Yours
>
> Mark B
>
>
> Kalpesh Modi-2 wrote:
>>
>> Hi,
>>
>>
>>
>> I have created an Excel template with few columns:
>>
>>
>>
>> Name of the columns: User, First Name, Last Name, DOB
>>
>>
>>
>> The DOB column is formatted as Date when creating the workbook
> template.
>>
>>
>>
>> When I read the cell type for the DOB field, i.e. the actual column
>> header DOB, it returns as String, even though its type is set to be
>> Date.
>>
>>
>>
>> Any ideas, how I can get the cell type as Date.
>>
>>
>>
>> Thanks and regards,
>>
>> -Kalpesh
>>
>>
>>
>>
>> .
>> The information contained in this e-mail message is intended only for
> the
>> personal
>> and confidential use of the recipient(s) named above. This message is
>> privileged
>> and confidential. If the reader of this message is not the intended
>> recipient or an
>> agent responsible for delivering it to the intended recipient, you
are
>> hereby notified
>> that you have received this document in error and that any review,
>> dissemination,
>> distribution, or copying of this message is strictly prohibited.
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/Getting-Data-type-tp29141500p29147093.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
>
> .
> The information contained in this e-mail message is intended only for
the
> personal
> and confidential use of the recipient(s) named above. This message is
> privileged
> and confidential. If the reader of this message is not the intended
> recipient or an
> agent responsible for delivering it to the intended recipient, you are
> hereby notified
> that you have received this document in error and that any review,
> dissemination,
> distribution, or copying of this message is strictly prohibited.
>
>
>
> ---------------------------------------------------------------------
> 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/Getting-Data-type-tp29141500p29150357.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
.
The information contained in this e-mail message is intended only for the personal
and confidential use of the recipient(s) named above. This message is privileged
and confidential. If the reader of this message is not the intended recipient or an
agent responsible for delivering it to the intended recipient, you are hereby notified
that you have received this document in error and that any review, dissemination,
distribution, or copying of this message is strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Getting Data type
Posted by MSB <ma...@tiscali.co.uk>.
I suspect that the reason the cell is being reported as type String is
because POI is detecting a String in that cell. If you think back to my
previous reply, I indicated that it is possible to enter one of three
different types of data into a cell and a Date is not actually a date value
as we would read it but a specially formatted number. That is why it is
common when reading a file using POI to test the cells type and, if it is
numeric, call the DateUtil.isCellDateFormatted() method to see if you have a
cell holding a date value. The problem you are facing now is that because
you have a String of text in that header cell - Date Of Birth - you cannot
use the DateUtil method (or at least I do not think you can as I have never
come across this problem myself and it would certainly be worth
experimenting with this test as it is only two or three lines of code) to
test the cells type for you.
To my mind, there are two possible solutions. The first would be to read the
label and see if it contains the word 'Date'. That way you would know what
you were dealing with and could process the data accordingly, but this
assumes the label may never change and it is not beyond the realms of
possibility to imagine the user wanting DOB instead of Date Of Birth. The
other option would be to modify your template a little. Just for the sake of
discussion, imagine that row 1 - POI index 0 - contains the labels, then you
could insert dummy data into row 2 (POI index 1). By dummy data, I mean
values that are representative of those you would expect to see in the cells
- text cells could just contain the word 'text' for example, any numeric
cells 0 and dates something like 10/10/2009. When you open the template
using POI, you could read this dummy row to determine what the types of the
cells are and note each using a Map. The cells type could be represented by
a simple word such as 'string', 'numeric' 'date' etc and the key for each
would be the column index converted into a String. Then, when you populate
the worksheet with 'real' data, it would be possible to use the column
index, convert that into a String, use this as the ley into the map and from
the value returned discover the cells type. Of course, you would overwrite
the dummy values in the first row of cells. The further advantage this would
offer is that you could set the formatting for each cell using Excel, read
that using POI and then apply the style as each row is created possibly
saving you some time and insulating the application from changes to the
cells style.
Alternatively, if you know the types in advance and do not expect them to
change, you could create this map without the need to use a dummy row - in a
constructor - and, finally, if you cannot know in advance, what about simply
listing the types in a properties file that the application could read? You
could pass the name of the properties file to the application at runtime
making it possible to change the listing of data types on the fly.
Yours
Mark B
PS I would be interested to discover what happens if you set the type of a
column of cells to numeric and then enter a label such as 'Total' into the
cell at the top of the column and then read this cell using POI; what type
would POI report for this cell I wonder?
Kalpesh Modi-2 wrote:
>
> Thanks for your reply.
>
> What I want to do is that I have a excel template.
>
> It has lot many columns with column names in the first row, and one of
> them is labeled "Date Of Birth". The "Date Of Birth" column label is
> itself a String but I have formatted the column type to be date. So any
> value I enter in that column other than the first row, is formatted as a
> date.
>
> User First Name DOB
> Kal1 Kalpesh 01/01/1901
>
> The "DOB" column is formatted as date. When the user uploads a file, I
> want to first check for the header data types in the first row.
>
> What I am expecting is that when I check the data type (ONLY THE HEADER
> LABEL FIRST ROW) for User it returns String, First Name returns String
> and DOB also returns String. Actually I am expecting DOB to be Date. I
> am not talking about the data type of the actual data from the second
> row onwards.
>
> Thanks and regards,
> -Kalpesh
>
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, July 13, 2010 2:39 AM
> To: user@poi.apache.org
> Subject: Re: Getting Data type
>
>
> First some bad news; it is only possible to enter one of three different
> types of value into a cell, a number, a String or a forumla which is
> itslef
> a type of String I guess. Then you apply a format to that value to
> transform
> the cells content into a Date value a currency value, etc. Therefore, it
> is
> likely that POI is reporting the type of the cell correctly - it is a
> String
> - that has then been formatted to look like a date.
>
> The answer to your question depends a lot on what you actually want to
> do
> with the value. If all you want to do is recover the contents of the
> cell as
> a String and formatted as they appeared on the worksheet, then try using
> DataFormatter -
> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.
> html
> - which will return the contents of the cell to you as a correctly
> formatted
> String. However, if you need the contents of the cell as a Date value,
> you
> will first need to make sure that the cell does contain a date and there
> is
> a class called DateUtil -
> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html
> - to
> help, simply call the static isCellDateFormatted(Cell) method and pass
> across the cell. This method will return true if the cell is formatted
> as a
> date. Next, you will need to convert the String value the cell contains
> into
> a Date object and this should be possible using the methods in the core
> Java
> api - DateFormat.parse(String) for example - passing the cell's contents
> as
> a String by calling either cell.GetRichStringCellValue().getString() or
> getStringCellValue(). It may be worth making a second check after the
> isCellDateFormatted() for cell type String. It could also be worth
> checking
> to see whether you could use the DataFormatter class's methods to
> convert
> the cells contents into a String and then simply pass this value to the
> Date
> class constructor also.
>
> Hope this helps a little.
>
> Yours
>
> Mark B
>
>
> Kalpesh Modi-2 wrote:
>>
>> Hi,
>>
>>
>>
>> I have created an Excel template with few columns:
>>
>>
>>
>> Name of the columns: User, First Name, Last Name, DOB
>>
>>
>>
>> The DOB column is formatted as Date when creating the workbook
> template.
>>
>>
>>
>> When I read the cell type for the DOB field, i.e. the actual column
>> header DOB, it returns as String, even though its type is set to be
>> Date.
>>
>>
>>
>> Any ideas, how I can get the cell type as Date.
>>
>>
>>
>> Thanks and regards,
>>
>> -Kalpesh
>>
>>
>>
>>
>> .
>> The information contained in this e-mail message is intended only for
> the
>> personal
>> and confidential use of the recipient(s) named above. This message is
>> privileged
>> and confidential. If the reader of this message is not the intended
>> recipient or an
>> agent responsible for delivering it to the intended recipient, you are
>> hereby notified
>> that you have received this document in error and that any review,
>> dissemination,
>> distribution, or copying of this message is strictly prohibited.
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/Getting-Data-type-tp29141500p29147093.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
>
> .
> The information contained in this e-mail message is intended only for the
> personal
> and confidential use of the recipient(s) named above. This message is
> privileged
> and confidential. If the reader of this message is not the intended
> recipient or an
> agent responsible for delivering it to the intended recipient, you are
> hereby notified
> that you have received this document in error and that any review,
> dissemination,
> distribution, or copying of this message is strictly prohibited.
>
>
>
> ---------------------------------------------------------------------
> 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/Getting-Data-type-tp29141500p29150357.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: Getting Data type
Posted by Kalpesh Modi <ka...@plateau.com>.
Thanks for your reply.
What I want to do is that I have a excel template.
It has lot many columns with column names in the first row, and one of
them is labeled "Date Of Birth". The "Date Of Birth" column label is
itself a String but I have formatted the column type to be date. So any
value I enter in that column other than the first row, is formatted as a
date.
User First Name DOB
Kal1 Kalpesh 01/01/1901
The "DOB" column is formatted as date. When the user uploads a file, I
want to first check for the header data types in the first row.
What I am expecting is that when I check the data type (ONLY THE HEADER
LABEL FIRST ROW) for User it returns String, First Name returns String
and DOB also returns String. Actually I am expecting DOB to be Date. I
am not talking about the data type of the actual data from the second
row onwards.
Thanks and regards,
-Kalpesh
-----Original Message-----
From: MSB [mailto:markbrdsly@tiscali.co.uk]
Sent: Tuesday, July 13, 2010 2:39 AM
To: user@poi.apache.org
Subject: Re: Getting Data type
First some bad news; it is only possible to enter one of three different
types of value into a cell, a number, a String or a forumla which is
itslef
a type of String I guess. Then you apply a format to that value to
transform
the cells content into a Date value a currency value, etc. Therefore, it
is
likely that POI is reporting the type of the cell correctly - it is a
String
- that has then been formatted to look like a date.
The answer to your question depends a lot on what you actually want to
do
with the value. If all you want to do is recover the contents of the
cell as
a String and formatted as they appeared on the worksheet, then try using
DataFormatter -
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.
html
- which will return the contents of the cell to you as a correctly
formatted
String. However, if you need the contents of the cell as a Date value,
you
will first need to make sure that the cell does contain a date and there
is
a class called DateUtil -
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html
- to
help, simply call the static isCellDateFormatted(Cell) method and pass
across the cell. This method will return true if the cell is formatted
as a
date. Next, you will need to convert the String value the cell contains
into
a Date object and this should be possible using the methods in the core
Java
api - DateFormat.parse(String) for example - passing the cell's contents
as
a String by calling either cell.GetRichStringCellValue().getString() or
getStringCellValue(). It may be worth making a second check after the
isCellDateFormatted() for cell type String. It could also be worth
checking
to see whether you could use the DataFormatter class's methods to
convert
the cells contents into a String and then simply pass this value to the
Date
class constructor also.
Hope this helps a little.
Yours
Mark B
Kalpesh Modi-2 wrote:
>
> Hi,
>
>
>
> I have created an Excel template with few columns:
>
>
>
> Name of the columns: User, First Name, Last Name, DOB
>
>
>
> The DOB column is formatted as Date when creating the workbook
template.
>
>
>
> When I read the cell type for the DOB field, i.e. the actual column
> header DOB, it returns as String, even though its type is set to be
> Date.
>
>
>
> Any ideas, how I can get the cell type as Date.
>
>
>
> Thanks and regards,
>
> -Kalpesh
>
>
>
>
> .
> The information contained in this e-mail message is intended only for
the
> personal
> and confidential use of the recipient(s) named above. This message is
> privileged
> and confidential. If the reader of this message is not the intended
> recipient or an
> agent responsible for delivering it to the intended recipient, you are
> hereby notified
> that you have received this document in error and that any review,
> dissemination,
> distribution, or copying of this message is strictly prohibited.
>
>
--
View this message in context:
http://old.nabble.com/Getting-Data-type-tp29141500p29147093.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
.
The information contained in this e-mail message is intended only for the personal
and confidential use of the recipient(s) named above. This message is privileged
and confidential. If the reader of this message is not the intended recipient or an
agent responsible for delivering it to the intended recipient, you are hereby notified
that you have received this document in error and that any review, dissemination,
distribution, or copying of this message is strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Getting Data type
Posted by MSB <ma...@tiscali.co.uk>.
First some bad news; it is only possible to enter one of three different
types of value into a cell, a number, a String or a forumla which is itslef
a type of String I guess. Then you apply a format to that value to transform
the cells content into a Date value a currency value, etc. Therefore, it is
likely that POI is reporting the type of the cell correctly - it is a String
- that has then been formatted to look like a date.
The answer to your question depends a lot on what you actually want to do
with the value. If all you want to do is recover the contents of the cell as
a String and formatted as they appeared on the worksheet, then try using
DataFormatter -
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html
- which will return the contents of the cell to you as a correctly formatted
String. However, if you need the contents of the cell as a Date value, you
will first need to make sure that the cell does contain a date and there is
a class called DateUtil -
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html - to
help, simply call the static isCellDateFormatted(Cell) method and pass
across the cell. This method will return true if the cell is formatted as a
date. Next, you will need to convert the String value the cell contains into
a Date object and this should be possible using the methods in the core Java
api - DateFormat.parse(String) for example - passing the cell's contents as
a String by calling either cell.GetRichStringCellValue().getString() or
getStringCellValue(). It may be worth making a second check after the
isCellDateFormatted() for cell type String. It could also be worth checking
to see whether you could use the DataFormatter class's methods to convert
the cells contents into a String and then simply pass this value to the Date
class constructor also.
Hope this helps a little.
Yours
Mark B
Kalpesh Modi-2 wrote:
>
> Hi,
>
>
>
> I have created an Excel template with few columns:
>
>
>
> Name of the columns: User, First Name, Last Name, DOB
>
>
>
> The DOB column is formatted as Date when creating the workbook template.
>
>
>
> When I read the cell type for the DOB field, i.e. the actual column
> header DOB, it returns as String, even though its type is set to be
> Date.
>
>
>
> Any ideas, how I can get the cell type as Date.
>
>
>
> Thanks and regards,
>
> -Kalpesh
>
>
>
>
> .
> The information contained in this e-mail message is intended only for the
> personal
> and confidential use of the recipient(s) named above. This message is
> privileged
> and confidential. If the reader of this message is not the intended
> recipient or an
> agent responsible for delivering it to the intended recipient, you are
> hereby notified
> that you have received this document in error and that any review,
> dissemination,
> distribution, or copying of this message is strictly prohibited.
>
>
--
View this message in context: http://old.nabble.com/Getting-Data-type-tp29141500p29147093.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