SpreadJS 14
Features / Cells / Cell Format / Accounting Format
In This Topic
    Accounting Format
    In This Topic

    SpreadJS supports accounting format on cells to avoid mistakes while reading currency values. This feature is supported in the same way as in Excel.

    The accounting format facilitates enhanced readability by allowing users to align the currency symbols and decimal points for numbers in a column. Negative numbers are displayed in parentheses and zeros are displayed as dashes.

    The following table lists the accounting formatting characters and the formatting characters that are provided for using the accounting format:

    Character Description
    _ You can use the underscore character "_" to create a space that is the width of a character in a number format. For example, _) causes positive numbers to line up with negative numbers that are enclosed in parentheses.
    * Use the asterisk "*" in the number format to repeat the next character so that it fills the column width. For example, type 0*- to include enough dashes after a number to fill the cell. Type *0 before any format to include leading zeros.
    , The comma displays the thousands separator in a number. Spread separates thousands by commas if the format contains a comma that is enclosed by number signs "#" or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number $12200.0 is displayed.
    % Use the percent sign "%" in the number format to display numbers as a percentage of 100. For example, to display .08 as 8%.
    ? This digit placeholder follows the same rules as 0 (zero); however, Spread adds a space for insignificant zeros on either side of the decimal point.

    The following list describes some of the things that you need to take care of while using accounting format:

    Using Code

    This example sets the accounting format for cells using the setValue method.

    JavaScript
    Copy Code
    var sheet = spread.getActiveSheet();
    sheet.setValue(1, 0, 'Standard Accounting Format :  _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)');
    sheet.setValue(2, 0, 'Value');
    sheet.setValue(3, 0, 12);
    sheet.setValue(4, 0, -12);
    sheet.setValue(5, 0, 0);
    sheet.setValue(6, 0, 'Text');
    sheet..setValue(2, 2, 'Formatted Result');
    sheet.getRange(3, 2, 4, 1).formatter('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)');
    sheet.setValue(3, 2, 12);
    sheet.setValue(4, 2, -12);
    sheet.setValue(5, 2, 0);
    sheet.setValue(6, 2, 'Text');
    

    This example uses the setFormatter method.

    JavaScript
    Copy Code
    var sheet = spread.getActiveSheet();
    sheet.setValue(1, 2, 12.34);
    sheet.setFormatter(1, 2, '$* #.##');