× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.