Scott,
Thank you for the explanation. It will go a long way in helping me
understand how to create different cell formats.
Like I said, I just copied the code from an example I found on the
internet. I didn't really understand how it worked. I thought that it
was something that POI used to tell excel what to do. I did not know
that it just passed it through to excel. Yours is the first explanation
I've seen that explains what the format means and how it is handled.
Terry Anderson
Programming Manager
Grede LLC.
Switchboard 1.251.867.5481 ext 212
Direct Line 1.251.809.2312
Fax 251.867.0525
Cell 1.251.363.4975
------------------------------
message: 5
date: Wed, 23 Jun 2010 16:37:03 -0500
from: Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
subject: Re: Creating a new cell style in POI
Hi Terry,
The string has little to do with Java/RPG. The string is merely passed
to Excel as-is.
Excel has something called "cell formatting" that describes how it
should display the contents of a cell. You are merely passing the
string that describes that cell format to Excel, and Excel uses it to
display the contents accordingly.
Indeed, people who have never heard of POI or ever used it, type this
exact same string into Excel directly to get their cells formatted.
Whether you do/don't need the parenthesis is a matter of how you want
your cell to look. Do you want it to be displayed with parenthesis, or
don't you? I can't answer that question for you, I can only tell you
how it works.
So here's how it works:
Excel's cell formatting string is divided into sections. There can be
from 1-4 sections. The sections are separated by a semi-colon.
With only one section, you define the format of all numbers (but with
only one section, there's no impact on text) For example:
#,##0.00
This says that all numbers are displayed in X,XXX.XX format. The
leftmost 3 digits will not be printed if they are zero.
With two sections, the first means positive numbers or zero. The second
section means negative numbers. So you might do this:
#,##0.00;-#,##0.00
The only difference being that negative numbers get a - character placed
in front of them. Or perhaps you'd like negative numbers to show up in
magenta... you could do this:
#,##0.00;[MAGENTA]-#,##0.00
So now positive numbers are in black, negative numbers are in magenta
and preceded by a - character.
With 3 sections, the 3rd section is for zero values. (And zero values
are no longer covered in the first section) Example:
#,##0.00;-#,##0.00;"none"
Or maybe you'd like
#,##0.00;[RED]-#,##0.00;[GREEN]0.00
So positive numbers show up as black ("in the black") and negative
numbers in red "in the red", and zeroes are green. Why not? The point
being, the 3rd section (again.. sections are divided by semicolons) is
for zero values.
You can also have 4 sections if you like, where the 4th section
represents text -- hopefully you get the idea.
Obviously, certain characters in these formats have special meanings...
I'll try to recite them .. but this is off the top of my head, so I may
miss some, or I may get something wrong.
0 means "put a digit here", if the digit is zero, the zero is printed.
# means "put a digit here", but suppress zeroes.
? means "put a digit here", but put a blank instead of a zero.
. separates whole numbers from fractions -- a decimal point.
% Percentage. Excel will multiply the number by 100, and
add the % char to the end, so "0.50" becomes "50%"
* Repeat the next character in the format to fill the cell.
so *X would fill the remainder of the cell with XXXXXXX
_ (underscore) print a blank that's the same width as the
next character. so _X would print a blank that's the
same width as the X character. Or more commonly, _)
prints a blank the same width as the ) character.
( ) / + - $ characters don't have any special meaning... they are
simply printed as-is in the cell. This surprised the heck out
of me the first time, because they are used in just about every
cell style, so I figured they must be special... but no, it just
tells excel to print them.
@ placeholder for text (as opposed to digits)
[XX] Where XX corresponds to any of the following:
BLACK, BLUE, CYAN, GREEN, MAGENTA, RED, YELLOW
changes the color of the font in the cell.
There are also hh, mm, ss, mm, dd, yy, etc for formatting dates and
times... I won't list all of the possibilities for dates/times here.
With all of that explained, let's look at your string. You had this:
(#,##0.00_);[Red](#,##0.00)
So you have two sections... one for zero/positive numbers, and the other
one is for negative numbers.
(#,##0.00_) is for positive numbers. The ( is printed, as-is. The
#,##0.00 means that a number should be printed there (with a comma).
The leftmost 3 digits are not printed if they are zeros. The _) means
to leave a space at the end that's the same width as the ) character.
Note that this trailing ) is not printed, it's just there to specify the
width of the blank space.
Then you have [Red](#,##0.00) for negative numbers. The [Red] means to
print it in red. The () are printed as is... so negative numbers will
appear enclosed in parenthesis on the page. (a common convention in
accounting). The #,##0.00 is the number format, and works the same that
it did for positive numbers.
Your format is unusual in only one way -- the leading (. Normally you'd
expect to see this:
#,##0.00_);[Red](#,##0.00)
The _) just puts a space the same width as the ) charater. You do that
so that the positive and negative numbers line up with each other.
Since the negative numbers end with a ), you want a blank the same width
as the ) for positives, and then they'll line up nicely.
But the leading parenthesis didn't make much sense... it'd just be
printed (which is what you're complaining about)
Okay, I'm gonna quit typing... this e-mail is taking forever to write,
and it's turning into a small book. I hope it helps you. If not, try
Googling "Excel Custom Formats" or something like that -- it'll explain
it.
And remember... this is just the string formatted the way Excel wants
it. It has little to do with POI, and absolutely nothing to do with
Java or RPG. It's just being supplied to Excel so it knows how to
format the cell.
Good luck
On 6/23/2010 3:14 PM, Terry Anderson wrote:
Scott,
Are you saying that I don't need the parenthesis at all?
I can go from TempStr = new_String('(#,##0.00_);[Red](#,##0.00)'); to
TempStr = new_String('#,##0.00_;[Red]#,##0.00');?
I'll be the first to admit I know very little about java and how it is
called from RPG so I have to ask, why does this code not produce the
same results?
Numeric = HSSFWorkbook_createCellStyle(book);
DataFmt = HSSFWorkbook_createDataFormat(book);
TempStr = new_String('(#,##0.00_);[Red](#,##0.00)');
NumFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
HSSFCellStyle_setDataFormat(Numeric: NumFmt);
HSSFCellStyle_setAlignment(Numeric: ALIGN_RIGHT);
All I did was copy the code above and changed the decimal position to
what I needed. The original code was copied from an example I found
on the web sometime back. It has caused no problems as long as I don't
exceed two decimal places.
Terry Anderson
Programming Manager
Grede LLC.
Switchboard 1.251.867.5481 ext 212
Direct Line 1.251.809.2312
Fax 251.867.0525
Cell 1.251.363.4975
midrange.com
