search

mortenbra / alexandria-plsql-utils

Oracle PL/SQL Utility Library
784 stars 317 forks source link

Acceptable values for XLS_BUILDER_PKG.get_NumFmt #80

Closed PeterNosko closed 2 years ago

PeterNosko commented 2 years ago

I'm trying to format an Excel column as the Comma or Accounting format. I was able to use '#,##0.00_);(#,##0.00);"- "' (directly in Excel). The get_NumFmt does not seem to accept format names, and when I tried this custom format string, Excel did not like the .xslx file ("We found a problem with some content..."). I also looked at using OraFmt2Excel, but Oracle has no format model for showing negative numbers in parenthesis. 1- Is there a way to format a column in the Comma or Accounting format? 2- Even when I pass a valid get_NumFmt call as a parameter to set_column, it seems this must be done before the column's cells are populated. Why is it ignored when called after?

mortenbra commented 2 years ago

As the comments in the package header indicate, this is originally the work of Anton Scheffer (see https://technology.amis.nl/languages/oracle-plsql/create-an-excel-file-with-plsql/ ) , it may be fruitful to contact him directly regarding these questions.

PeterNosko commented 2 years ago

I did get a solution from Anton. It is: get_numFmt( '#,##0.00_);(#,##0.00);"- "' ).

FYI: There are THREE spaces after that hyphen, but even as code, two are being stripped out.