|
Wow, great explanation, Scott, as usual!
There is also the cheater's (i.e. 'Dennis') approach for standard
formatting.
In Excel, choose the format that you want, from the Numbers format
(assuming.. well, you know).
Now click OK.
Now make sure the same cell is selected, and go back to Numbers formatting.
Choose Custom. Here you will see the format as it was selected previously.
This shows you the string you would pass to POI to get that
particular/peculiar formatting. Works for dates, numbers, ...
Having said that, it is *far* better to go with Scott's explanation and
understanding. The approach above is basically a hack.
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"A statesman is a politician who's been dead ten or fifteen years."
-- Harry S. Truman
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
