You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Bryan Liu <lj...@gmail.com> on 2006/09/06 12:46:03 UTC
Big trouble about the cell format when reading the excel file
Hi,
I have a big trouble about the excel cell's format.
In my project, I readed the data from the excel file. For one column, it
supposed to be String format and I alse set the format as text in the Excel.
But in some cells in this column, all the character is number(like this
"51231341"). When I read them in the java code, the poi read these data as
double, and if I transfer them in to string, it will show as this "
5.1231341E7". This is not the data I want.
Thought I have resolved this problem by reformat this kind of stupid String.
By I'd like to know if I can read the data follow the Excel's format or I
can specify the format that I want when read the data from file.
Dose the POI have this kind of feature.
Thanks
--
Regards!
Bryan.Liu
Re: Big trouble about the cell format when reading the excel file
Posted by Bryan Liu <lj...@gmail.com>.
Got it, Thank you so much!
On 9/14/06, deshmol-lists@yahoo.com <de...@yahoo.com> wrote:
>
>
> Once you can get the Format string from HSSF and
> verify it to be "@", I think you should be able to
> use:
>
> java.text.NumberFormat.getIntegerInstance().format(doubleCellValue);
>
>
>
> For further details on java.text.NumberFormat, check
> out the J2SE apidocs.
>
> I'll post some code later if i get the time...
>
> ~ amol
>
>
> --- Bryan Liu <lj...@gmail.com> wrote:
>
> > Amol
> > Thank you so much
> > But can you tell me how to apply this format to the
> > value that i received
> > from the cell
> >
> > Thanks
> > Bryan
> >
> > On 9/7/06, deshmol-lists@yahoo.com
> > <de...@yahoo.com> wrote:
> > >
> > > bryan,
> > >
> > > you will need to handle this in your application
> > by
> > > determining the dataformat of a cell and applying
> > it
> > > to the value retrieved.
> > >
> > > eg. in your case, you would get the dataformat
> > using:
> > >
> > > String format =
> > >
> >
> HSSFDataFormat.getBuiltinFormat(c.getCellStyle().getDataFormat());
> > >
> > > The value of /format/ in your case would be "@"
> > > indicating a "Text" format. You will then need to
> > > *apply* this format to the value you receive from
> > the
> > > cell (after determining the cell type etc.)
> > >
> > > However there may be more to it depending on how
> > your
> > > excel is generated. If you have inserted value
> > into a
> > > cell in that column after applying the "Text"
> > format,
> > > you will notice that excel may choose to store the
> > > value as text (I think you are given an option
> > whether
> > > you want to store it as number instead). OTOH, if
> > you
> > > insert values cells before applying the "Text"
> > format,
> > > you will notice that the values are stored as
> > numbers
> > > but formatted as text.
> > >
> > > So you either need to make assumptions about how
> > data
> > > is entered in excel or handle both cases
> > generically
> > > and apply the text format in your java code if the
> > > cell type is NUMERIC
> > >
> > > hth,
> > > ~ amol
> > >
> > >
> > >
> > >
> > > --- Bryan Liu <lj...@gmail.com> wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a big trouble about the excel cell's
> > format.
> > > > In my project, I readed the data from the excel
> > > > file. For one column, it
> > > > supposed to be String format and I alse set the
> > > > format as text in the Excel.
> > > > But in some cells in this column, all the
> > character
> > > > is number(like this
> > > > "51231341"). When I read them in the java code,
> > the
> > > > poi read these data as
> > > > double, and if I transfer them in to string, it
> > will
> > > > show as this "
> > > > 5.1231341E7". This is not the data I want.
> > > > Thought I have resolved this problem by reformat
> > > > this kind of stupid String.
> > > > By I'd like to know if I can read the data
> > follow
> > > > the Excel's format or I
> > > > can specify the format that I want when read the
> > > > data from file.
> > > >
> > > > Dose the POI have this kind of feature.
> > > > Thanks
> > > >
> > > > --
> > > >
> > > > Regards!
> > > > Bryan.Liu
> > > >
> > >
> > >
> > >
> >
> ---------------------------------------------------------------------
> > > To unsubscribe, e-mail:
> > poi-user-unsubscribe@jakarta.apache.org
> > > Mailing List:
> > http://jakarta.apache.org/site/mail2.html#poi
> > > The Apache Jakarta Poi Project:
> > http://jakarta.apache.org/poi/
> > >
> > >
> >
> >
> > --
> >
> > Regards!
> > Bryan.Liu
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
--
Regards!
Bryan.Liu
Re: Big trouble about the cell format when reading the excel file
Posted by de...@yahoo.com.
Once you can get the Format string from HSSF and
verify it to be "@", I think you should be able to
use:
java.text.NumberFormat.getIntegerInstance().format(doubleCellValue);
For further details on java.text.NumberFormat, check
out the J2SE apidocs.
I'll post some code later if i get the time...
~ amol
--- Bryan Liu <lj...@gmail.com> wrote:
> Amol
> Thank you so much
> But can you tell me how to apply this format to the
> value that i received
> from the cell
>
> Thanks
> Bryan
>
> On 9/7/06, deshmol-lists@yahoo.com
> <de...@yahoo.com> wrote:
> >
> > bryan,
> >
> > you will need to handle this in your application
> by
> > determining the dataformat of a cell and applying
> it
> > to the value retrieved.
> >
> > eg. in your case, you would get the dataformat
> using:
> >
> > String format =
> >
>
HSSFDataFormat.getBuiltinFormat(c.getCellStyle().getDataFormat());
> >
> > The value of /format/ in your case would be "@"
> > indicating a "Text" format. You will then need to
> > *apply* this format to the value you receive from
> the
> > cell (after determining the cell type etc.)
> >
> > However there may be more to it depending on how
> your
> > excel is generated. If you have inserted value
> into a
> > cell in that column after applying the "Text"
> format,
> > you will notice that excel may choose to store the
> > value as text (I think you are given an option
> whether
> > you want to store it as number instead). OTOH, if
> you
> > insert values cells before applying the "Text"
> format,
> > you will notice that the values are stored as
> numbers
> > but formatted as text.
> >
> > So you either need to make assumptions about how
> data
> > is entered in excel or handle both cases
> generically
> > and apply the text format in your java code if the
> > cell type is NUMERIC
> >
> > hth,
> > ~ amol
> >
> >
> >
> >
> > --- Bryan Liu <lj...@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > I have a big trouble about the excel cell's
> format.
> > > In my project, I readed the data from the excel
> > > file. For one column, it
> > > supposed to be String format and I alse set the
> > > format as text in the Excel.
> > > But in some cells in this column, all the
> character
> > > is number(like this
> > > "51231341"). When I read them in the java code,
> the
> > > poi read these data as
> > > double, and if I transfer them in to string, it
> will
> > > show as this "
> > > 5.1231341E7". This is not the data I want.
> > > Thought I have resolved this problem by reformat
> > > this kind of stupid String.
> > > By I'd like to know if I can read the data
> follow
> > > the Excel's format or I
> > > can specify the format that I want when read the
> > > data from file.
> > >
> > > Dose the POI have this kind of feature.
> > > Thanks
> > >
> > > --
> > >
> > > Regards!
> > > Bryan.Liu
> > >
> >
> >
> >
>
---------------------------------------------------------------------
> > To unsubscribe, e-mail:
> poi-user-unsubscribe@jakarta.apache.org
> > Mailing List:
> http://jakarta.apache.org/site/mail2.html#poi
> > The Apache Jakarta Poi Project:
> http://jakarta.apache.org/poi/
> >
> >
>
>
> --
>
> Regards!
> Bryan.Liu
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: Big trouble about the cell format when reading the excel file
Posted by Bryan Liu <lj...@gmail.com>.
Amol
Thank you so much
But can you tell me how to apply this format to the value that i received
from the cell
Thanks
Bryan
On 9/7/06, deshmol-lists@yahoo.com <de...@yahoo.com> wrote:
>
> bryan,
>
> you will need to handle this in your application by
> determining the dataformat of a cell and applying it
> to the value retrieved.
>
> eg. in your case, you would get the dataformat using:
>
> String format =
> HSSFDataFormat.getBuiltinFormat(c.getCellStyle().getDataFormat());
>
> The value of /format/ in your case would be "@"
> indicating a "Text" format. You will then need to
> *apply* this format to the value you receive from the
> cell (after determining the cell type etc.)
>
> However there may be more to it depending on how your
> excel is generated. If you have inserted value into a
> cell in that column after applying the "Text" format,
> you will notice that excel may choose to store the
> value as text (I think you are given an option whether
> you want to store it as number instead). OTOH, if you
> insert values cells before applying the "Text" format,
> you will notice that the values are stored as numbers
> but formatted as text.
>
> So you either need to make assumptions about how data
> is entered in excel or handle both cases generically
> and apply the text format in your java code if the
> cell type is NUMERIC
>
> hth,
> ~ amol
>
>
>
>
> --- Bryan Liu <lj...@gmail.com> wrote:
>
> > Hi,
> >
> > I have a big trouble about the excel cell's format.
> > In my project, I readed the data from the excel
> > file. For one column, it
> > supposed to be String format and I alse set the
> > format as text in the Excel.
> > But in some cells in this column, all the character
> > is number(like this
> > "51231341"). When I read them in the java code, the
> > poi read these data as
> > double, and if I transfer them in to string, it will
> > show as this "
> > 5.1231341E7". This is not the data I want.
> > Thought I have resolved this problem by reformat
> > this kind of stupid String.
> > By I'd like to know if I can read the data follow
> > the Excel's format or I
> > can specify the format that I want when read the
> > data from file.
> >
> > Dose the POI have this kind of feature.
> > Thanks
> >
> > --
> >
> > Regards!
> > Bryan.Liu
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
--
Regards!
Bryan.Liu
Re: Big trouble about the cell format when reading the excel file
Posted by de...@yahoo.com.
bryan,
you will need to handle this in your application by
determining the dataformat of a cell and applying it
to the value retrieved.
eg. in your case, you would get the dataformat using:
String format =
HSSFDataFormat.getBuiltinFormat(c.getCellStyle().getDataFormat());
The value of /format/ in your case would be "@"
indicating a "Text" format. You will then need to
*apply* this format to the value you receive from the
cell (after determining the cell type etc.)
However there may be more to it depending on how your
excel is generated. If you have inserted value into a
cell in that column after applying the "Text" format,
you will notice that excel may choose to store the
value as text (I think you are given an option whether
you want to store it as number instead). OTOH, if you
insert values cells before applying the "Text" format,
you will notice that the values are stored as numbers
but formatted as text.
So you either need to make assumptions about how data
is entered in excel or handle both cases generically
and apply the text format in your java code if the
cell type is NUMERIC
hth,
~ amol
--- Bryan Liu <lj...@gmail.com> wrote:
> Hi,
>
> I have a big trouble about the excel cell's format.
> In my project, I readed the data from the excel
> file. For one column, it
> supposed to be String format and I alse set the
> format as text in the Excel.
> But in some cells in this column, all the character
> is number(like this
> "51231341"). When I read them in the java code, the
> poi read these data as
> double, and if I transfer them in to string, it will
> show as this "
> 5.1231341E7". This is not the data I want.
> Thought I have resolved this problem by reformat
> this kind of stupid String.
> By I'd like to know if I can read the data follow
> the Excel's format or I
> can specify the format that I want when read the
> data from file.
>
> Dose the POI have this kind of feature.
> Thanks
>
> --
>
> Regards!
> Bryan.Liu
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/