HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"); no longer works in 3.9

[littlenoodles]

I've been using

data_format = HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)");

to format dollar data in poi since version 3.2. I recently upgraded to 3.9, and
that code no longer works. But this does:

data_format = 7;

I saw a post on here about a regression in getBuiltinFormat, but I didn't
understand what it was saying, and in any case, the current 3.9 version still
seems to be the 20121203 release that I'm using. Does anybody know about a
recent change to getBuiltinFormat?

Thanks,
Rob

[Nick Burch]

Post by littlenoodles
I've been using
data_format = HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)");
to format dollar data in poi since version 3.2. I recently upgraded to 3.9, and
data_format = 7;

If you set it as the int/short value 7, and query the format string that
gets applied based on that, how does the string compare to what you were
trying to set?

Nick

[littlenoodles]

Post by Nick Burch
If you set it as the int/short value 7, and query the format string that
gets applied based on that, how does the string compare to what you were
trying to set?

"$"#,##0.00_);("$"#,##0.00)

I guess it needs the dollar signs quoted now. The regression note I found said
something like that - I just didn't understand what it was getting at. The
format also seems to have a trailing underscore in the first (non-negative?)
pattern that wasn't there before. I guess that's so positive and negative
currency amounts line up even with the trailing parenthesis on negative amounts.
Anyway, the string has changed from what it used to be. I guess as long as 7
seems to be the index for the 'standard currency' format, maybe it's better to
just use that, since the string seems to be subject to change with POI updates.

[Nick Burch]

Post by littlenoodles
I guess it needs the dollar signs quoted now. The regression note I
found said something like that - I just didn't understand what it was
getting at. The format also seems to have a trailing underscore in the
first (non-negative?) pattern that wasn't there before.

The commit that changed that was:

r1069396 | yegor | 2011-02-10 14:07:04 +0000 (Thu, 10 Feb 2011) | 1 line

fixed a regression caused by not escaped dollar sumbols, see r1061288 and
Bugzilla 49928

I think, but it's been a while since then and it wasn't my change, that
what we had in the built-ins wasn't quite what excel was normally storing

Does anyone know if any of those formats have a "name"? It'd be good if
people could reference them by such a thing, if it existed, but I have a
feeling that there may not be any such name/description for the main built
in formats...

Nick

[littlenoodles]

Post by Nick Burch

Post by littlenoodles
I guess it needs the dollar signs quoted now. The regression note I
found said something like that - I just didn't understand what it was
getting at. The format also seems to have a trailing underscore in the
first (non-negative?) pattern that wasn't there before.

r1069396 | yegor | 2011-02-10 14:07:04 +0000 (Thu, 10 Feb 2011) | 1 line
fixed a regression caused by not escaped dollar sumbols, see r1061288 and
Bugzilla 49928
I think, but it's been a while since then and it wasn't my change, that
what we had in the built-ins wasn't quite what excel was normally storing
Does anyone know if any of those formats have a "name"? It'd be good if
people could reference them by such a thing, if it existed, but I have a
feeling that there may not be any such name/description for the main built
in formats...

...

Whether the format has a name or not, as long as the 'standard currency' format
is always at index 7, that works too. Kind of weird to be coding for the long
format strings anyway. For what it's worth, I've been adding my own format
string for 'currency without pennies', since there doesn't seem to be a built-in
one for that. I suppose I could've done the same thing for this format - just
add it as a custom format and ignore the built-in one...