[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

Accounting Format

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:

  • If word wrap is allowed and the cell contains multiple lines, then the repeat characters do not take effect.

  • Repeat characters are ignored if the cell supports text overflow.

  • The repeat characters are not used with automatic fit.

  • The repeat character does not become blank with shrink to fit.

  • The blank area does not display a text decoration.

  • The supported cell types for accounting format includes: Text cell type, Button cell type, ComboBox cell type and Header Cell cell type.

The following code sample shows how to set the accounting format for cells using the setValue method.

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');

Alternately you can use the setFormatter method as shown in the code sample below:

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