You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Marc Guillemot <mg...@yahoo.fr> on 2008/06/03 09:34:17 UTC
Retrieve formatted cell value
Hi,
is there a simple way to get the formatted value (as String) of a cell
without having to use custom code to deal with the cell format?
If no, wouldn't it make sense to add such a feature?
Cheers,
Marc.
--
Blog: http://mguillem.wordpress.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Retrieve formatted cell value
Posted by Marc Guillemot <mg...@yahoo.fr>.
Hi Andy,
sorry if my first explanation was not precise enough.
I understand that such a feature doesn't directly belong to the XLS file
format but I think that it would be useful for different users,
particularly due to the fact that converting Excel data format string to
SimpleDateFormat or DecimalFormat pattern is not trivial.
Do you think that I should open an issue for that?
Cheers,
Marc.
--
Blog: http://mguillem.wordpress.com
Andy Chien wrote:
> Hey Marc:
>
> Oh I think I understand what you need now, you need the result of a cell
> after cell style application. Like the following made-up example
>
> Cell Value: 02/03/87
> Cell Style: ddmmmyyyy
> Result: 02MAR2008
>
> Something like this?
>
> I do not know the solution to this actually, but I hope by clarifying
> the question it would help others in finding a solution for you.
>
> One thing to note though, without knowing great detail about this. It is
> possible that the result of the format application could be a
> functionality of Excel App. Since POI deals with the XLS file and not
> the core Excel App, I am a bit uncertain as to whether POI will ever
> support the function you are requesting; but of course it is not up to
> me to decide.
>
> As an alternative in the meantime if the user input format is fairly
> standard, it seems logical to use Java REGEX to extract and convert the
> cell values that you get from HSSFCell to your desired format. If you
> are lucky you might not even need to use REGEX by using the Java
> packaged formating classes (using quick googling if it's number you can use
>
> http://java.sun.com/j2se/1.4.2/docs/api/java/text/DecimalFormat.html
> http://java.sun.com/j2se/1.4.2/docs/api/java/text/NumberFormat.html
>
> If it's date you can use the
>
> http://java.sun.com/j2se/1.4.2/docs/api/java/text/DateFormat.html
>
> I have used a little bit of the above classes but not extensively)
>
>
> Hope this helps
>
> Andy
>
>
>
>
> Marc Guillemot wrote:
>> Hi Andy,
>>
>> thanks for the response... but this is not what I'm looking for ;-(
>>
>> HSSFCell.getRichStringCellValue() works only for Strings. I'm looking
>> for something more generic to work for instance with numeric and date
>> cells as well.
>>
>> getCellStyle() allows to retrieve the style but I don't see any facility
>> to retrieve the result of the style applied on the cell content. I
>> can look at the data format of the style by myself, but I imagine that
>> it is a common requirement and was hoping to find an existing way for it.
>>
>> Cheers,
>> Marc.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Retrieve formatted cell value
Posted by Andy Chien <hs...@utoronto.ca>.
Hey Marc:
Oh I think I understand what you need now, you need the result of a cell
after cell style application. Like the following made-up example
Cell Value: 02/03/87
Cell Style: ddmmmyyyy
Result: 02MAR2008
Something like this?
I do not know the solution to this actually, but I hope by clarifying
the question it would help others in finding a solution for you.
One thing to note though, without knowing great detail about this. It is
possible that the result of the format application could be a
functionality of Excel App. Since POI deals with the XLS file and not
the core Excel App, I am a bit uncertain as to whether POI will ever
support the function you are requesting; but of course it is not up to
me to decide.
As an alternative in the meantime if the user input format is fairly
standard, it seems logical to use Java REGEX to extract and convert the
cell values that you get from HSSFCell to your desired format. If you
are lucky you might not even need to use REGEX by using the Java
packaged formating classes (using quick googling if it's number you can use
http://java.sun.com/j2se/1.4.2/docs/api/java/text/DecimalFormat.html
http://java.sun.com/j2se/1.4.2/docs/api/java/text/NumberFormat.html
If it's date you can use the
http://java.sun.com/j2se/1.4.2/docs/api/java/text/DateFormat.html
I have used a little bit of the above classes but not extensively)
Hope this helps
Andy
Marc Guillemot wrote:
> Hi Andy,
>
> thanks for the response... but this is not what I'm looking for ;-(
>
> HSSFCell.getRichStringCellValue() works only for Strings. I'm looking
> for something more generic to work for instance with numeric and date
> cells as well.
>
> getCellStyle() allows to retrieve the style but I don't see any facility
> to retrieve the result of the style applied on the cell content. I
> can look at the data format of the style by myself, but I imagine that
> it is a common requirement and was hoping to find an existing way for it.
>
> Cheers,
> Marc.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Retrieve formatted cell value
Posted by Marc Guillemot <mg...@yahoo.fr>.
Hi Andy,
thanks for the response... but this is not what I'm looking for ;-(
HSSFCell.getRichStringCellValue() works only for Strings. I'm looking
for something more generic to work for instance with numeric and date
cells as well.
getCellStyle() allows to retrieve the style but I don't see any facility
to retrieve the result of the style applied on the cell content. I
can look at the data format of the style by myself, but I imagine that
it is a common requirement and was hoping to find an existing way for it.
Cheers,
Marc.
--
Blog: http://mguillem.wordpress.com
Andy Chien wrote:
> Hi Marc:
>
> Is this the class you might be looking for?
>
> http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRichTextString.html
> HSSFCell
>
> You can use HSSFCell.getRichStringCellValue() to get Excel formatted
> string.
>
> http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html
>
> Or another interpretation of what you may be thinking is a combination
> of HSSFCell.getCellNum() and HSSFCell.getCellStyle()
>
> http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html
>
> Hope this helps.
>
>
> Marc Guillemot wrote:
>> Hi,
>>
>> is there a simple way to get the formatted value (as String) of a cell
>> without having to use custom code to deal with the cell format?
>>
>> If no, wouldn't it make sense to add such a feature?
>>
>> Cheers,
>> Marc.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Retrieve formatted cell value
Posted by Andy Chien <hs...@utoronto.ca>.
Hi Marc:
Is this the class you might be looking for?
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRichTextString.html
You can use HSSFCell.getRichStringCellValue() to get Excel formatted string.
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html
Or another interpretation of what you may be thinking is a combination
of HSSFCell.getCellNum() and HSSFCell.getCellStyle()
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html
Hope this helps.
Marc Guillemot wrote:
> Hi,
>
> is there a simple way to get the formatted value (as String) of a cell
> without having to use custom code to deal with the cell format?
>
> If no, wouldn't it make sense to add such a feature?
>
> Cheers,
> Marc.
--
============================
Hsiang-An (Andy) Chien
Office of the Vice-Provost,
240-K, Planning and Budget
University of Toronto
Simcoe Hall
27 King's College Circle
Toronto ON M5S 1A1
(416) 978 5035
www.utoronto.ca/planning
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org