You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Fallows, Andrew" <AF...@drs-c3.com> on 2007/06/21 15:24:18 UTC
Formatting cells in HSSF
Hi all,
I've just started using HSSF, and I can tell that it's going to be very
helpful to me - I work with Excel files frequently, and automating tasks
through Java will be much easier if I can access the data directly from
.xls files, as opposed to manually collecting and formatting data before
processing it (which is still faster than doing the whole thing
manually, but less...faster). So I appreciate that this product exists.
However, I've run into a stumbling block that I haven't found the answer
for in the How-to, the Quick Guide, or the Javadocs. I'm hoping this is
the right groups to be in, and that you can help me out.
In the file I'm currently working with, one of the columns contains a
series of alpha-numeric codes, most of which are all numerals. And the
trouble I'm having is that when I print these codes out, those which are
all numerals get treated as large numbers and printed in scientific
notation (i.e. 9.9152516E7). I need to look at them as just a series of
digits, since they are not, in the traditional sense, numbers.
I have tried using HSSFCellStyle with the following code:
HSSFCellStyle style = wb.createCellStyle();
style.setDataFormat(wb.createDataFormat().getFormat("@"));
and calling curCell.setCellStyle(style); when I iterate over the codes,
just before I print them.
And I've used "0", "#,##0", and "General" in place of "@", but none of
those have worked. Is there a DataFormat that will do what I need it to
do, or are then other steps necessary?
Many thanks in advance,
Andrew Fallows
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Formatting cells in HSSF
Posted by "Fallows, Andrew" <AF...@drs-c3.com>.
> From: Nick Burch [mailto:nick@torchbox.com]
> Sent: Friday, June 22, 2007 9:15 AM
> Can't you just call getNumericCellValue() on the cell, and format it
> however you want?
Heh, I feel silly. I was already using getNumericalCellValue(), but all
I needed to do was cast its result as an int. I hadn't realized that
scientific notation was something Java automatically does when you print
a double.
Thanks all.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Formatting cells in HSSF
Posted by Nick Burch <ni...@torchbox.com>.
On Fri, 22 Jun 2007, Fallows, Andrew wrote:
> Is there a way to force HSSF to treat cells as strings even if they
> contain numbers only? As I said, I tried using
> style.setDataFormat(wb.createDataFormat().getFormat("@"));, but that did
> not work. And according to the Javadoc for HSSFCell,
> getRichStringCellValue() throws an exception for numeric cells.
Can't you just call getNumericCellValue() on the cell, and format it
however you want?
(If you stored the number in excel as a number (i.e. no ' prefix), it will
be stored in the excel file as a floating point number, no matter how it's
displayed on the screen.)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Formatting cells in HSSF
Posted by "Fallows, Andrew" <AF...@drs-c3.com>.
> Actually I was suggesting storing these values as strings in the first
> place
> (when entering them in the spreadsheet the first time.)
>
> What wasn't clear from your original post was whether you were
creating
> data
> to be read by Excel, or whether you were processing data created by
Excel.
That's a good thing to consider for entering new data in the future.
However, this was processing a series of existing files, each containing
hundreds to thousands of entries, so I didn't want to be forced to
change the formatting.
> Keep in mind though that if you use numeric values, eventually you
*will*
> be
> bitten by the precision bug, if the number has enough digits. The
only
> way
> to be 100% sure that precision is being kept is by using integer
values
> (which I don't think Excel can even do... correct me if I'm wrong) or
by
> using strings. Plus you'll generally lose leading zeroes unless you
use a
> format like "000000000000" to work around that. I always found it
much
> easier to use actual string values when the values were IDs rather
than
> numeric values where maths was required.
In this case I was able to get accuracy by simply getting the numeric
values of the cells and casting them as (int). None of the codes started
with 0 and none of them had .'s in them. But I'll be sure to keep that
in mind if I need to process other types of data in the future.
Thanks again,
Andrew
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formatting cells in HSSF
Posted by Daniel Noll <da...@nuix.com>.
On Friday 22 June 2007 22:18:35 Fallows, Andrew wrote:
> I'm not entirely sure what you mean. If you mean modifying the data in
> the file so that it's treated as a string, that's not ideal, because I'd
> like to avoid requiring modifications to the xls files themselves -
> every added step per file is one less benefit from automation. I'll do
> that if it comes to it, but I'd rather find a way in the program.
Actually I was suggesting storing these values as strings in the first place
(when entering them in the spreadsheet the first time.)
What wasn't clear from your original post was whether you were creating data
to be read by Excel, or whether you were processing data created by Excel.
If you're creating data on your end to be read by Excel, you just need to set
the value in as a string instead of using a numeric value. If you're
creating it in Excel, then your options are to prefix the value with a ' to
make it a string value, or manually format it however you want.
Keep in mind though that if you use numeric values, eventually you *will* be
bitten by the precision bug, if the number has enough digits. The only way
to be 100% sure that precision is being kept is by using integer values
(which I don't think Excel can even do... correct me if I'm wrong) or by
using strings. Plus you'll generally lose leading zeroes unless you use a
format like "000000000000" to work around that. I always found it much
easier to use actual string values when the values were IDs rather than
numeric values where maths was required.
Daniel
--
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Formatting cells in HSSF
Posted by "Fallows, Andrew" <AF...@drs-c3.com>.
> From: Daniel Noll [mailto:daniel@nuix.com]
> Sent: Thursday, June 21, 2007 7:41 PM
>
> Have you considered using a string value in those cells instead of a
> numeric
> value? It might save you a lot of trouble.
>
> Daniel
I'm not entirely sure what you mean. If you mean modifying the data in
the file so that it's treated as a string, that's not ideal, because I'd
like to avoid requiring modifications to the xls files themselves -
every added step per file is one less benefit from automation. I'll do
that if it comes to it, but I'd rather find a way in the program.
Is there a way to force HSSF to treat cells as strings even if they
contain numbers only? As I said, I tried using
style.setDataFormat(wb.createDataFormat().getFormat("@"));, but that did
not work. And according to the Javadoc for HSSFCell,
getRichStringCellValue() throws an exception for numeric cells.
Do you know of anything?
-Andrew
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formatting cells in HSSF
Posted by Daniel Noll <da...@nuix.com>.
On Thursday 21 June 2007 23:24:18 Fallows, Andrew wrote:
> In the file I'm currently working with, one of the columns contains a
> series of alpha-numeric codes, most of which are all numerals. And the
> trouble I'm having is that when I print these codes out, those which are
> all numerals get treated as large numbers and printed in scientific
> notation (i.e. 9.9152516E7). I need to look at them as just a series of
> digits, since they are not, in the traditional sense, numbers.
Have you considered using a string value in those cells instead of a numeric
value? It might save you a lot of trouble.
Daniel
--
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Formatting cells in HSSF
Posted by "Fallows, Andrew" <AF...@drs-c3.com>.
> From: Karr, David [mailto:david.karr@wamu.net]
> If you're able to control your input, you can prefix the values in
those
> columns with a single quote, to ensure they're treated as a string.
If
> you can't control this, perhaps someone else can figure this out.
>
I am able to control my input, but I'd hoped to be able to control this
through Java, since the purpose of this program is to automate, and I'd
rather not do that part manually unless I have to.
If it comes to that - do you know how, in Excel, to append text to each
of a large group of cells simultaneously? I have over 1000 elements per
file in my input, and I don't intend to do it manually, one by one.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Formatting cells in HSSF
Posted by "Karr, David" <da...@wamu.net>.
If you're able to control your input, you can prefix the values in those
columns with a single quote, to ensure they're treated as a string. If
you can't control this, perhaps someone else can figure this out.
> -----Original Message-----
> From: Fallows, Andrew [mailto:AFallows@drs-c3.com]
> Sent: Thursday, June 21, 2007 6:24 AM
> To: user@poi.apache.org
> Subject: Formatting cells in HSSF
>
> Hi all,
>
> I've just started using HSSF, and I can tell that it's going
> to be very helpful to me - I work with Excel files
> frequently, and automating tasks through Java will be much
> easier if I can access the data directly from .xls files, as
> opposed to manually collecting and formatting data before
> processing it (which is still faster than doing the whole
> thing manually, but less...faster). So I appreciate that this
> product exists.
>
> However, I've run into a stumbling block that I haven't found
> the answer for in the How-to, the Quick Guide, or the
> Javadocs. I'm hoping this is the right groups to be in, and
> that you can help me out.
>
> In the file I'm currently working with, one of the columns
> contains a series of alpha-numeric codes, most of which are
> all numerals. And the trouble I'm having is that when I print
> these codes out, those which are all numerals get treated as
> large numbers and printed in scientific notation (i.e.
> 9.9152516E7). I need to look at them as just a series of
> digits, since they are not, in the traditional sense, numbers.
>
> I have tried using HSSFCellStyle with the following code:
>
> HSSFCellStyle style = wb.createCellStyle();
> style.setDataFormat(wb.createDataFormat().getFormat("@"));
>
> and calling curCell.setCellStyle(style); when I iterate over
> the codes, just before I print them.
>
> And I've used "0", "#,##0", and "General" in place of "@",
> but none of those have worked. Is there a DataFormat that
> will do what I need it to do, or are then other steps necessary?
>
> Many thanks in advance,
> Andrew Fallows
>
> ---------------------------------------------------------------------
> 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