Skip to main content
D
Dave_Miller
Known Participant
June 14, 2011
Question

Spreadsheet dataformat options not usable

  • June 14, 2011
  • 1 reply
  • 756 views

Based upon the documentation for SpreadsheetFormatCell a complex dataformat option is not usable.

My spreadsheet does not know what to do with it. The built-in formats are not usable as is and the examples do not use any of the complex formats.

I wanted to use this format "_($*#,##0__($*(#,##0_($* \-\__(@_)", however, the hash signs throw an error, so I tried to double them up as

_($*##,####0__($*(##,####0_($* \-\__(@_) and that did not format the numbers properly. Also causes you 
not to be able to view the applied format in excel.

Any help here would be greatly appreciated.

Need a space between the dollar sign and the number, if zero, a dash to appear.

    This topic has been closed for replies.

    1 reply

    -
    -__cfSearching__-
    Inspiring
    June 14, 2011

    The custom format examples in the documentation are totally mangled. If you compare them to those in Excel, some are missing the

    format separator ";" and double quotes for special characters. This example

             _($*#,##0__($*(#,##0_($* \-\__(@_)

    should be more like

             _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)

    This may not be the exact format you want. So adapt it as needed.

    <cfset sheet= SpreadsheetNew() >
    <cfset format = {dataformat = '_($* ##,####0_);_($* (##,####0);_($* "-"_);_(@_)'} >
    <cfset SpreadsheetFormatCell(sheet, format, 1, 1) > 
    <cfset SpreadsheetSetCellValue(sheet,"0", 1, 1) >

    Message was edited by: -==cfSearching==-

    D
    Dave_MillerAuthor
    Known Participant
    June 15, 2011

    This worked perfectly!

    Thanks.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices