You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Ashika Umanga <um...@aeturnum.com> on 2007/12/27 06:58:32 UTC
Reading Cell value as a String regardless of the cell type (HSSF)
hi guys,
I'm new to POI and what i want to do is read the String value of a Cell
directly without considering about the cell type.
eg: Lets say my Excel file is something like following.
______________________
| Start Date | Index | Name |
-----------------------------
|2001/01/1 | 01 |umanga|
------------------------------
So I am getting the cells as follow.
HSSFCell cellDate=row.getCell((short)1);
HSSFCell cellIndex=row.getCell((short)2);
HSSFCell cellName=row.getCell((short)3);
so now I want to get the String value '2001/01/01' from cellDate ,not
the Date type.
and get the string value '01' from cellIndex and so on...
Actually in my application ,I am reading through cells in a loop and
want to get the String value directly..
Any idea?
thanks in advance
umanga
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Reading Cell value as a String regardless of the cell type (HSSF)
Posted by Ashika Umanga <um...@aeturnum.com>.
Thanks David,
Ok,is there any way to get the Date format of a Date Cell ?
regards
umanga
Karr, David wrote:
> Then you would have to use "setLenient(true)" and hope for the best. To
> some level, you have to be able to parse the date format.
>
>
>> -----Original Message-----
>> From: Ashika Umanga [mailto:umanga@aeturnum.com]
>> Sent: Thursday, December 27, 2007 8:15 PM
>> To: POI Users List
>> Subject: Re: Reading Cell value as a String regardless of the
>> cell type (HSSF)
>>
>> Thanks David,
>>
>> But I have a little problem with using this.Here we have to
>> define the date format for RR_DATE_FORMATK rit?
>> But in my case, this date format changes,and cant predict
>> what format will use in application.
>> I thought there is any low level API call , which directly
>> reading the cell's String value?
>>
>> thanks and regards
>> umanga
>>
>>
>>> Just do something like this:
>>>
>>> private String getCellValue(HSSFRow row, HSSFCell cell)
>>> {
>>> String result = null;
>>>
>>> if (cell != null)
>>> {
>>> int cellType = cell.getCellType();
>>> switch (cellType)
>>> {
>>> case HSSFCell.CELL_TYPE_BLANK:
>>> break;
>>> case HSSFCell.CELL_TYPE_BOOLEAN:
>>> boolean flag = cell.getBooleanCellValue();
>>> result = flag + "";
>>> break;
>>> case HSSFCell.CELL_TYPE_ERROR:
>>> byte error = cell.getErrorCellValue();
>>> result = error + "";
>>> break;
>>> case HSSFCell.CELL_TYPE_FORMULA:
>>> result = cell.getCellFormula();
>>> getFormulaEvaluator().setCurrentRow(row);
>>> HSSFFormulaEvaluator.CellValue
>>>
>> formulaCellValue =
>>
>>> getFormulaEvaluator().evaluate(cell);
>>> result = getFormulaCellValue(formulaCellValue);
>>> break;
>>> case HSSFCell.CELL_TYPE_NUMERIC:
>>> if (HSSFDateUtil.isCellDateFormatted(cell))
>>> {
>>> Date date =
>>>
>>> HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
>>> DateFormat format = new
>>> SimpleDateFormat(RR_DATE_FORMAT);
>>> result = format.format(date);
>>> }
>>> else
>>> {
>>> double value = cell.getNumericCellValue();
>>> NumberFormat format =
>>> NumberFormat.getNumberInstance();
>>> format.setMaximumIntegerDigits(99);
>>> format.setGroupingUsed(false);
>>>
>>> result = format.format(value);
>>> }
>>> break;
>>> case HSSFCell.CELL_TYPE_STRING:
>>> result = cell.getStringCellValue();
>>> break;
>>> }
>>> }
>>>
>>> return (result);
>>> }
>>>
>>>
>>>
>>>> -----Original Message-----
>>>> From: Ashika Umanga [mailto:umanga@aeturnum.com]
>>>> Sent: Wednesday, December 26, 2007 9:59 PM
>>>> To: user@poi.apache.org
>>>> Subject: Reading Cell value as a String regardless of the
>>>>
>> cell type
>>
>>>> (HSSF)
>>>>
>>>> hi guys,
>>>>
>>>> I'm new to POI and what i want to do is read the String value of a
>>>> Cell directly without considering about the cell type.
>>>> eg: Lets say my Excel file is something like following.
>>>> ______________________
>>>> | Start Date | Index | Name |
>>>> -----------------------------
>>>> |2001/01/1 | 01 |umanga|
>>>> ------------------------------
>>>>
>>>>
>>>> So I am getting the cells as follow.
>>>> HSSFCell cellDate=row.getCell((short)1); HSSFCell
>>>> cellIndex=row.getCell((short)2); HSSFCell
>>>> cellName=row.getCell((short)3);
>>>>
>>>> so now I want to get the String value '2001/01/01' from
>>>>
>> cellDate ,not
>>
>>>> the Date type.
>>>> and get the string value '01' from cellIndex and so on...
>>>>
>>>> Actually in my application ,I am reading through cells in
>>>>
>> a loop and
>>
>>>> want to get the String value directly..
>>>>
>>>> Any idea?
>>>>
>>>> thanks in advance
>>>>
>>>> umanga
>>>>
>>>>
>>>>
>>>>
>>>>
>> ---------------------------------------------------------------------
>>
>>>> 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
>>>
>>>
>>>
>>>
>>>
>>
>
> ---------------------------------------------------------------------
> 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: Reading Cell value as a String regardless of the cell type (HSSF)
Posted by "Karr, David" <da...@wamu.net>.
Then you would have to use "setLenient(true)" and hope for the best. To
some level, you have to be able to parse the date format.
> -----Original Message-----
> From: Ashika Umanga [mailto:umanga@aeturnum.com]
> Sent: Thursday, December 27, 2007 8:15 PM
> To: POI Users List
> Subject: Re: Reading Cell value as a String regardless of the
> cell type (HSSF)
>
> Thanks David,
>
> But I have a little problem with using this.Here we have to
> define the date format for RR_DATE_FORMATK rit?
> But in my case, this date format changes,and cant predict
> what format will use in application.
> I thought there is any low level API call , which directly
> reading the cell's String value?
>
> thanks and regards
> umanga
>
> > Just do something like this:
> >
> > private String getCellValue(HSSFRow row, HSSFCell cell)
> > {
> > String result = null;
> >
> > if (cell != null)
> > {
> > int cellType = cell.getCellType();
> > switch (cellType)
> > {
> > case HSSFCell.CELL_TYPE_BLANK:
> > break;
> > case HSSFCell.CELL_TYPE_BOOLEAN:
> > boolean flag = cell.getBooleanCellValue();
> > result = flag + "";
> > break;
> > case HSSFCell.CELL_TYPE_ERROR:
> > byte error = cell.getErrorCellValue();
> > result = error + "";
> > break;
> > case HSSFCell.CELL_TYPE_FORMULA:
> > result = cell.getCellFormula();
> > getFormulaEvaluator().setCurrentRow(row);
> > HSSFFormulaEvaluator.CellValue
> formulaCellValue =
> > getFormulaEvaluator().evaluate(cell);
> > result = getFormulaCellValue(formulaCellValue);
> > break;
> > case HSSFCell.CELL_TYPE_NUMERIC:
> > if (HSSFDateUtil.isCellDateFormatted(cell))
> > {
> > Date date =
> >
> > HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
> > DateFormat format = new
> > SimpleDateFormat(RR_DATE_FORMAT);
> > result = format.format(date);
> > }
> > else
> > {
> > double value = cell.getNumericCellValue();
> > NumberFormat format =
> > NumberFormat.getNumberInstance();
> > format.setMaximumIntegerDigits(99);
> > format.setGroupingUsed(false);
> >
> > result = format.format(value);
> > }
> > break;
> > case HSSFCell.CELL_TYPE_STRING:
> > result = cell.getStringCellValue();
> > break;
> > }
> > }
> >
> > return (result);
> > }
> >
> >
> >> -----Original Message-----
> >> From: Ashika Umanga [mailto:umanga@aeturnum.com]
> >> Sent: Wednesday, December 26, 2007 9:59 PM
> >> To: user@poi.apache.org
> >> Subject: Reading Cell value as a String regardless of the
> cell type
> >> (HSSF)
> >>
> >> hi guys,
> >>
> >> I'm new to POI and what i want to do is read the String value of a
> >> Cell directly without considering about the cell type.
> >> eg: Lets say my Excel file is something like following.
> >> ______________________
> >> | Start Date | Index | Name |
> >> -----------------------------
> >> |2001/01/1 | 01 |umanga|
> >> ------------------------------
> >>
> >>
> >> So I am getting the cells as follow.
> >> HSSFCell cellDate=row.getCell((short)1); HSSFCell
> >> cellIndex=row.getCell((short)2); HSSFCell
> >> cellName=row.getCell((short)3);
> >>
> >> so now I want to get the String value '2001/01/01' from
> cellDate ,not
> >> the Date type.
> >> and get the string value '01' from cellIndex and so on...
> >>
> >> Actually in my application ,I am reading through cells in
> a loop and
> >> want to get the String value directly..
> >>
> >> Any idea?
> >>
> >> thanks in advance
> >>
> >> umanga
> >>
> >>
> >>
> >>
> ---------------------------------------------------------------------
> >> 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
> >
> >
> >
> >
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Reading Cell value as a String regardless of the cell type (HSSF)
Posted by Ashika Umanga <um...@aeturnum.com>.
Thanks David,
But I have a little problem with using this.Here we have to define the
date format for RR_DATE_FORMATK rit?
But in my case, this date format changes,and cant predict what format
will use in application.
I thought there is any low level API call , which directly reading the
cell's String value?
thanks and regards
umanga
> Just do something like this:
>
> private String getCellValue(HSSFRow row, HSSFCell cell)
> {
> String result = null;
>
> if (cell != null)
> {
> int cellType = cell.getCellType();
> switch (cellType)
> {
> case HSSFCell.CELL_TYPE_BLANK:
> break;
> case HSSFCell.CELL_TYPE_BOOLEAN:
> boolean flag = cell.getBooleanCellValue();
> result = flag + "";
> break;
> case HSSFCell.CELL_TYPE_ERROR:
> byte error = cell.getErrorCellValue();
> result = error + "";
> break;
> case HSSFCell.CELL_TYPE_FORMULA:
> result = cell.getCellFormula();
> getFormulaEvaluator().setCurrentRow(row);
> HSSFFormulaEvaluator.CellValue formulaCellValue =
> getFormulaEvaluator().evaluate(cell);
> result = getFormulaCellValue(formulaCellValue);
> break;
> case HSSFCell.CELL_TYPE_NUMERIC:
> if (HSSFDateUtil.isCellDateFormatted(cell))
> {
> Date date =
>
> HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
> DateFormat format = new
> SimpleDateFormat(RR_DATE_FORMAT);
> result = format.format(date);
> }
> else
> {
> double value = cell.getNumericCellValue();
> NumberFormat format =
> NumberFormat.getNumberInstance();
> format.setMaximumIntegerDigits(99);
> format.setGroupingUsed(false);
>
> result = format.format(value);
> }
> break;
> case HSSFCell.CELL_TYPE_STRING:
> result = cell.getStringCellValue();
> break;
> }
> }
>
> return (result);
> }
>
>
>> -----Original Message-----
>> From: Ashika Umanga [mailto:umanga@aeturnum.com]
>> Sent: Wednesday, December 26, 2007 9:59 PM
>> To: user@poi.apache.org
>> Subject: Reading Cell value as a String regardless of the
>> cell type (HSSF)
>>
>> hi guys,
>>
>> I'm new to POI and what i want to do is read the String value
>> of a Cell directly without considering about the cell type.
>> eg: Lets say my Excel file is something like following.
>> ______________________
>> | Start Date | Index | Name |
>> -----------------------------
>> |2001/01/1 | 01 |umanga|
>> ------------------------------
>>
>>
>> So I am getting the cells as follow.
>> HSSFCell cellDate=row.getCell((short)1); HSSFCell
>> cellIndex=row.getCell((short)2); HSSFCell
>> cellName=row.getCell((short)3);
>>
>> so now I want to get the String value '2001/01/01' from
>> cellDate ,not the Date type.
>> and get the string value '01' from cellIndex and so on...
>>
>> Actually in my application ,I am reading through cells in a
>> loop and want to get the String value directly..
>>
>> Any idea?
>>
>> thanks in advance
>>
>> umanga
>>
>>
>>
>> ---------------------------------------------------------------------
>> 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: Reading Cell value as a String regardless of the cell type (HSSF)
Posted by "Karr, David" <da...@wamu.net>.
Just do something like this:
private String getCellValue(HSSFRow row, HSSFCell cell)
{
String result = null;
if (cell != null)
{
int cellType = cell.getCellType();
switch (cellType)
{
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
boolean flag = cell.getBooleanCellValue();
result = flag + "";
break;
case HSSFCell.CELL_TYPE_ERROR:
byte error = cell.getErrorCellValue();
result = error + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
getFormulaEvaluator().setCurrentRow(row);
HSSFFormulaEvaluator.CellValue formulaCellValue =
getFormulaEvaluator().evaluate(cell);
result = getFormulaCellValue(formulaCellValue);
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell))
{
Date date =
HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
DateFormat format = new
SimpleDateFormat(RR_DATE_FORMAT);
result = format.format(date);
}
else
{
double value = cell.getNumericCellValue();
NumberFormat format =
NumberFormat.getNumberInstance();
format.setMaximumIntegerDigits(99);
format.setGroupingUsed(false);
result = format.format(value);
}
break;
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
}
}
return (result);
}
> -----Original Message-----
> From: Ashika Umanga [mailto:umanga@aeturnum.com]
> Sent: Wednesday, December 26, 2007 9:59 PM
> To: user@poi.apache.org
> Subject: Reading Cell value as a String regardless of the
> cell type (HSSF)
>
> hi guys,
>
> I'm new to POI and what i want to do is read the String value
> of a Cell directly without considering about the cell type.
> eg: Lets say my Excel file is something like following.
> ______________________
> | Start Date | Index | Name |
> -----------------------------
> |2001/01/1 | 01 |umanga|
> ------------------------------
>
>
> So I am getting the cells as follow.
> HSSFCell cellDate=row.getCell((short)1); HSSFCell
> cellIndex=row.getCell((short)2); HSSFCell
> cellName=row.getCell((short)3);
>
> so now I want to get the String value '2001/01/01' from
> cellDate ,not the Date type.
> and get the string value '01' from cellIndex and so on...
>
> Actually in my application ,I am reading through cells in a
> loop and want to get the String value directly..
>
> Any idea?
>
> thanks in advance
>
> umanga
>
>
>
> ---------------------------------------------------------------------
> 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